How to resolve this deadlock?

Maahi 2018-05-22 14:04:48

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.

Hugo Kornelis 2018-05-23 08:59:28
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.

Maahi 2018-05-23 19:40:16
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.

Hugo Kornelis 2018-05-24 07:09:42
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.