Understanding SQL Server Deadlocks: Causes, Detection, and Prevention

1. Deadlock Principle

In operating systems, a deadlock occurs when a set of processes each hold resources that cannot be released and wait indefinitely for resources held by other processes.

The four necessary conditions are: Mutual exclusion (resources cannot be shared). Hold and wait (processes holding resources may request new ones). No pre-emption (resources cannot be forcibly taken away). Circular wait (processes form a cycle of waiting).

2. Mapping to SQL Server

In SQL Server, deadlocks arise when two or more tasks each lock resources the other needs. Locked resources may include rows (RID), index keys (KEY), pages (PAG), extents (EXT), heaps or B‑trees (HOBT), tables (TAB), files (File), application‑specific resources (APP), metadata (METADATA), allocation units (Allocation_Unit), or the whole database (DB).

Example deadlock graph is shown below:

3. Deadlock Investigation

(1) Use SQL Server system stored procedures sp_who and sp_lock to view current locks. The functions objectID(@objID) (SQL Server 2005) or object_name(@objID) (SQL Server 2000) reveal which resource is locked. dbcc ld(@blk) shows the last SQL statement for a given SPID.

CREATE TABLE #Who(spid int, ecid int, status nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_ID int);
CREATE TABLE #Lock(spid int, dpid int, objid int, indld int, [Type] nvarchar(20), Resource nvarchar(50), Mode nvarchar(10), Status nvarchar(10));
INSERT INTO #Who EXEC sp_who active;
INSERT INTO #Lock EXEC sp_lock;
DECLARE @DBName nvarchar(20) = 'NameOfDataBase';
SELECT #Who.* FROM #Who WHERE dbname=@DBName;
SELECT #Lock.* FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName;
DECLARE crsr CURSOR FOR SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;
DECLARE @blk int;
OPEN crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DBCC INPUTBUFFER(@blk);
    FETCH NEXT FROM crsr INTO @blk;
END
CLOSE crsr;
DEALLOCATE crsr;
SELECT #Who.spid, hostname, objid, [type], mode, object_name(objid) as objName FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName WHERE objid<>0;
DROP TABLE #Who;
DROP TABLE #Lock;

(2) Use SQL Server Profiler to capture the Deadlock graph event. The XML data is stored in the TextData column and can be exported as an .xdl file for analysis in SQL Server Management Studio.

4. Avoiding Deadlocks

Breaking any of the four necessary conditions prevents deadlocks. Common strategies include: (1) Access objects in a consistent order. (2) Avoid user interaction inside transactions to reduce lock hold time. (3) Keep transactions short and within a single batch. (4) Use a lower isolation level (e.g., READ COMMITTED) to shorten shared‑lock duration. (5) Enable row‑versioning isolation (snapshot) with SET ALLOW_SNAPSHOT_ISOLATION ON and SET READ_COMMITTED_SNAPSHOT ON. (6) Use bound connections to share the same transaction and locks across sessions.

5. Example Scenarios and Solutions

5.1 Simple deadlock example

--Query 1
Begin Tran
Update Lock1 Set C1=C1+1;
WaitFor Delay '00:01:00';
Select * From Lock2;
Rollback Tran;

--Query 2
Begin Tran
Update Lock2 Set C1=C1+1;
WaitFor Delay '00:01:00';
Select * From Lock1;
Rollback Tran;

During the wait, each query holds an exclusive row lock (RID) on its own table and an intent‑update lock (PAG, TAB) on the page and table, forming a circular wait.

SQL Server’s lock monitor selects the transaction with the smallest rollback cost as the victim and returns error 1205.

Resolution methods include reordering statements, using WITH (NOLOCK) (caution: dirty reads), lowering isolation level, enabling snapshot isolation, or setting SET LOCK_TIMEOUT to break the hold‑and‑wait condition.

5.2 Programmatic deadlock in C#

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
string sql1 = "Update Lock1 SET C1=C1+1";
string sql2 = "SELECT * FROM Lock1";
ExecuteNonQuery(tran, sql1); // transaction holds X lock on Lock1
ExecuteNonQuery(null, sql2); // new connection requests S lock and blocks

The second connection times out because the S lock is incompatible with the X lock held by the first transaction.

Solutions: move SELECT outside the transaction, include it in the same transaction, use WITH (NOLOCK), lower isolation level, enable snapshot isolation, or use bound sessions ( sp_getbindtoken and sp_bindsession) to share the same transaction across connections.

Appendix: Lock Compatibility Matrix

The matrix shows which lock modes are compatible; incompatible requests cause waiting or timeout.