Reading a deadlock

Deadlock is a common phenomena in databases where users can work concurrently. Deadlock happens because of pessimistic concurrency control mechanism which is essentially use of locks. You can't prevent deadlocks but of course you need to minimize them occurring. There are several ways of capturing deadlocks in SQL Server. They are;

1. Using SQL Server Profiler

2. Using SQL Server Error Log

The objective of the blog post is to read and understand the deadlock graph which is captured in SQL Server Error log.

Below is a sample deadlock. (Wait-for graph) Most important sections are highlighted. 

Very first step is to identify the nodes involved for the deadlock. In this case it is Node1 and Node2. In Node1, under the Grant List it has the lock information being held by Node1. You can clearly see that SPID 54 which is essentially the Node1, is being held a shared lock (S) on the resource KEY (13: 1993058136: 2) [13 is the database id, 1993058136 is the object id and 2 indicates non clustered index]

Under Requested By section of Node1 has the lock type which is being looking for the same resource. As per the wait-for-graph above, it is X (Exclusive) lock from SPID 55. This X lock can not be granted here simply because of the KEY resource is being held S lock. So it goes to the wait state.

In Node2, under Grant List it has the currently held lock information. It is X lock from SPID 55 on the resource KEY (13: 1993058136:1) [Database id and object id is same as in Node1 but only the last digit differs. Here it is 1 means clustered index.]

Under Requested By section of Node2, has the information about the lock which is being locking for or waiting for. It is S lock from SPID 54. Since the KEY resource is being held X lock, S lock can not be granted so it goes to waiting state.

So two processes are being waiting for each for resources which is not going to resolved. SQL Server has the deadlock monitoring mechanism and it can identify this type of waiting's. Then it chooses one process and kill thus becomes the deadlock victim. Choosing of deadlock victim is based on the least effort which SQL Server needs to rollback the process.

The deadlock victim information can be found under Victim Resource Owner: section. In this case it is SPID 54.

Further details, you also can get exactly in which code line responsible for the lock. In Input buffer in each Node has that details. In Node1 S lock is being held by SELECT statement at line #3 in BookMarkLookupSelect stored procedure.

The most interesting factor is, you can also identify the particular record who has the S lock. Look at the hash value presented after the KEY resource. In Node1 it is, (08009d1c9ab1). This is an internal value where you can identify reach record in the table. See below SQL statement;


SELECT TOP 10 %%LOCKRES%%,* FROM Sales.SalesOrderDetail

Note: %%LOCKRES%% is an undocumented function. Use at your own risk in Production environment.