SQL Server – Main Causes of Deadlocks and Prevention

deadlocksql serversql-server-2008

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor.

This is the first time I've seen this error and didn't know how to track and fix it effectively. I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column.

I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.

I felt happy and came back to my project, and did some research to found out the reason for that deadlock…

Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.

I know it isn't a well defined conclusion, that is why I'm posting here…

  • Is the missing primary key the problem?
  • Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
  • How do I prevent and track deadlocks?

Best Answer

tracking deadlocks is the easier of the two:

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605)

Turn it off: DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.

Related Question