SQL: How to debug SQL deadlocks

What is a deadlock?

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, Andrew holds a lock on table A and requests a lock on table B; Lindsay holds a lock on table B and requests a lock on table A.

The lock manager(Lock Monitor)Æs thread checks for deadlocks. When a lock managerÆs deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.

Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Killing the SPID frees the resources and allows the other SPID to continue. In a well-designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction. Although deadlocks can be minimized, they cannot be completely avoided. That is why the front-end application should be designed to handle deadlocks.

So what can cause deadlocks?

1. Locks
2. Worker threads
3. Memory
4. Parallel query execution-related resources
5. Multiple Active Result Sets (MARS) resources.

How to debug deadlocks

Turn on deadlock trace

          DBCC TRACEON(1204,1222)

This will enable deadlock tracing for all existing connetions and new. You can check out KB832524 for more details. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

Use SQL Profiler to trace deadlock events and get the resource ID of the table or index under contention. The steps to do this are:

1. Start SQL profiler
2. On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3. Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
4. Click the Data columns tab, add DatabaseID, IndexID, ObjectID

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention. To translate the database id and object id into names (although DatabaseName and ObjectName are selectable columns, the data is not always presented in the trace), you can do

           SELECT database_name(DatabaseID)

           SELECT object_name(ObjectID)

Use SQL Profiler to trace batch statements or RPC statements, to trace which statement causes the deadlock. The restriction of this is that if the repro is far between and you have a busy system, the trace file will grow too large to be useful.

You can also run following query to check which process is being blocked.

            SELECT * FROM sys.sysprocesses WHERE blocked <> 0

            Get the SPID from blocked column

            DBCC inputbuffer (SPID)

            sp_who2

            sp_lock2

You can also use SP_LOCK2 to receive detailed locking view.