How to resolve this deadlock?
We are seeing a deadlock happening repeatedly in our environment.
Using SQL 2014.
Was able to get the xml deadlock graph and found below.
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.
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:
- 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).
- 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.
- 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.
- 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.
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.