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.
By Maahi 1 asked May 22 at 02:04 PM
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:
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.
By Hugo Kornelis 271 answered May 23 at 08:59 AM