Need some help in fixing the deadlock.
Will an Intent exclusive (IX) lock can cause deadlock?? We are seeing deadlocks where one spid is having IX lock and another spid is requesting for a Shared lock. Needs some advise here on how can I avoid/minimize the deadlock of current scenario.
Below is the deadlock graph. We can save it as .xml .xdl to see the Graph in ssms. also attaching stored procedure code.
By Maahi 1 asked Jun 12 at 05:30 AM
An IX lock means that the transaction has (or requests) an X lock on a more granul resource. Simplified: I update data for employee 4, I get an X lock on employee 4, and an IX lock on the table. You update employee 18, you get an X lock on employee 18, and an IX lock on the table. These IX locks are not exclusive - since we are updating different employees, we can run at the same time.
But if I am updating employee 4 and you try to do an update without WHERE clause, you would get a table lock. The IX lock prevents that. The reason for this architecture is that it's very easy to work "up" (get an X lock on employee 4 and we "know" which table this is so it's eaasy to find where the IX lock goes. The alternative would be to verify each and every row when a table-level lock is requested, which is much harder and slower.
An IX lock and an S lock can not go together. If you want an S lock on the whole table, then you will only get it if none of its rows are X-locked. The IX locks tells you that's not the case.
Now then, on to your deadlock. I see two processes. I'll call them c28 and ca8, after the last characters of their official names in the deadlock graph.
Process ca8 does a bulk insert on FieldValues. It wants to take an IX lock on that table (makes sense, since it will get X locks on the rows it insert), and it already has an IX lock on Questionnaires. My guess is that it first inserted or updated a row there and then does the bulk insert with the detailed data.
Process c28 runs the stored procedure you attached. It holds an exclusive lock on FieldValues and tries to convert an existing lock on Questionnaires to an S lock. I guess it has IS now. So it appears it is reading the table, taking shared row locks and deciding to escalate to a table-level lock.
The standard advice on how to avoid deadlocks is to always lock tables in the same order. That is not what happened in this case. Process ca8 locked Questionnaires first, then went on to FieldValues. Process c28 used the reverse order.
At this point there are two options:
I hope this helps!
By Hugo Kornelis 271 answered Jun 12 at 04:54 PM