How to resolve this deadlock?

H All,

We are seeing a deadlock happening repeatedly in our environment. Using SQL 2014. Was able to get the xml deadlock graph and found below.

link text Tried below options but didn't work out. 1. The retry logic is there for 3 times, still it fails. 2. Tried creating a non-clustered idx on a primary key column to avoid the deadlock. Still we can see deadlocks happening.

Attaching the deadlock graph.

1.png (14.4 kB)
avatar image By Maahi 1 asked May 22 at 02:04 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

If you often see the same deadlock happenning, the most likely root cause is badly written code. If this is vendor code you cannot change yourself, you'll probably need to raise a ticket with the vendor,

If the code is written within your organization (in a .Net layer or in stored procedures), then you can probably fix this yourself. You will need to find the source code for the code module that was running in each of the two involved conncetions, then analyze how it accesses the database tables.

Here is a checklist you can use to see how resiliennt your code is against deadlocks:

  1. Is the transaction isolation level appropriate? There are workloads where serializable is actually needed. But if it's not, then don't use it. Never use a too low isolation level (you'd risk compromising your data integrity), but also try to avoid uysing a too high isolation level (which causes unnecessary locking and increases the risk of deadlocks).

  2. If within a single transaction you first read some data and then later may end up modifying that same data, always use the WITH (UPDLOCK) hint. This creates a special type of lock that will not block other readers (so you don't hurt concurrency), but that will block other writers or other potential writers. Using WITH (UPDLOCK) vastly reduces the risk of deadlocks due to lock escalation.

  3. Finally (and perhaps the most important): always access tables in the same order. If one stored procedure locks the OrderHeader table first and the OrderDetail table second, then all should follow that order. If this is "impossible" (eg a foreign key might get into the way of deleting OrderDetail before deleting OrderHeader), then you can get around that by requesting an exclusive lock in a dummy query - in other words, just do a SELECT of what you intend to delete, bith the WITH (UPDLOCK) hint or in this case even with the WITH (XLOCK) hint.

  4. If you have foreign keys with the CASCADE option, then you have little to no control over the order in which affected tables are locked. CASCADE can be a fine option to reduce the amount of application code you need to write, but if it gets in the way of concurrency by causing deadlocks, you'll have to remove the logic and write the application logic yourself.

Good luck!

EDIT:

In addition to the above ...

I see in the deadlock XML that neither of the statements involved in the deadlock explicitly mentions the Commmon.Changesets table.

The statement in spid 151 attempts to insert data in ItemTags and in ItemVersionMasterTranslations (but fails because it cannot get a lock on ItemTags); this spid already has an exclusive lock on Common.Changesets which was probably granted while executing an earlier statement in the same transaction. That is not unusual.

The statement in spid 158 wants to insert data in Questionnaire.FormattingActions, and that data comes from a temporary table. That does not explain why it attempts to acquire a shared lock on Common.Changesets. The likely causes are either a foreign key (it has to check that a referenced row exists before inserting it) or a trigger. So that;s where you'll probably have to loook when investigating this.

avatar image By Hugo Kornelis 271 answered May 23 at 08:59 AM
more ▼
(comments are locked)
avatar image Maahi May 23 at 07:40 PM

Thanks a lot for taking time and providing suggestions. This code is a Entity Framework generated code and not many in the support team has .net background knowledge on it. The vendors who developed this code no more exists in the organization.

Couple of things I want to know.

  1. IF there is a trigger, how to deal with the situation. is there a way to avoid deadlocks in that scenario?

  2. Can I use snapshot isolation level here as a dirty fix or bandage kind of?? If I can go for snapshot, which is better ? either RCSI or Snapshot isolation level?

Pl suggest.

avatar image Hugo Kornelis May 24 at 07:09 AM

If there's a trigger, then you can control the order in which events happen within the trigger. And you need to be aware that the trigger fires after the modification, so the target table will be locked first, then the trigger executes.

Snapshot isolation might help in this case. One of the locks involved is an S (shared, for reading) lock. With snapshot isolation, readers and writers do not block each other so it's worth a try. RCSI isolation needs little to no code changes. Snapshot isolation does require you to change your code. So in your case where you can't easily change the code, I'd try RCSI first.

Make sure that you are aware of how Snapshot and RCSI behave, how they deal with update conflicts, and how they affect performance of your system. Especially the extra pressure on tempdb is something to be aware of.

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
x1

asked: May 22 at 02:04 PM

Seen: 34 times

Last Updated: May 24 at 07:09 AM