How to fix this deadlock? need suggestions.

Hi All,

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.

Thanks,

M

[1]: /storage/temp/2297-2.xdl

[2]: /storage/temp/2296-2.xml

avatar image By Maahi 1 asked Jun 12 at 05:30 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Hi Maahi!

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:

  1. You change the order in which tables are accessed in one of the two locations (keeping in mind all other code that accesses these tables as well!) and it works. Great!

  2. You attempt to do #1 but get an error because of a foreign key. Congratulations! You have a better-desinged databse than most. However, in this case you do have to do some extra work. You can't insert and delete in the same order when foreign keys are involved. But you can still change the lock order. For instance, if you want to lock table1, then delete from table2, and then delete from table1, you can first do a select from table1 with the XLOCK hint, then do the two deletes. (All inside a transaction of course)

I hope this helps!

avatar image By Hugo Kornelis 271 answered Jun 12 at 04:54 PM
more ▼
(comments are locked)
avatar image Maahi Jun 12 at 06:05 PM

Thanks Sir, for taking a look into the deadlock graph. Many thanks for the above suggestions.

Also, one more thought. Please correct me if I am wrong. What if, I use below statement inside my stored procedure? I know this will have some impact on concurrency but will it avoid this particular deadlock scenario? pl provide feedback if I can use it or not.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Thanks,

M

avatar image Hugo Kornelis Jun 12 at 09:24 PM

Can you use it? Yes.

Will it help? No.

The cause of the deadlock is that two transactions take locks on the same tables but in different order. You'll need to fix that, or build retry logic.

avatar image Maahi Jun 13 at 05:28 PM +

Many thanks Sir.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!

<p><br></p>

Follow this question

Topics:

x17

asked: Jun 12 at 05:30 AM

Seen: 34 times

Last Updated: 2 days ago