Deadlocks, how SQL Server breaks them, and how to stop causing them

Blocking is one session waiting for a lock another holds; it ends when the holder finishes. A deadlock is a cycle of waits: session A holds a lock B needs, while waiting for a lock B holds. Neither can ever finish, so the engine has to break the cycle.

How SQL Server resolves it

A background lock monitor searches the wait-for graph for cycles, by default every 5 seconds and more often once it starts finding them. When it finds one it picks a victim, rolls back the victim's transaction, and raises error 1205 in the victim's session. The other sessions continue.

The victim is the session with the lowest DEADLOCK_PRIORITY (LOW, NORMAL, HIGH, or -10 to 10; default NORMAL). Among equal priorities, SQL Server picks the one it estimates is cheapest to roll back.

Deadlock graphs are captured by default in the system_health extended events session as xml_deadlock_report events (it keeps a limited history, so old ones roll off). That graph names each process, the statement it ran, the lock it held, and the lock it waited for. It is the first thing to read.

The sandbox runs one session at a time, so it cannot produce a real deadlock. The examples below model one instead: the first treats a snapshot of who-waits-for-whom as a table, the way you would read blocking_session_id from sys.dm_exec_requests.

1CREATE TABLE waits (waiter INT PRIMARY KEY, holder INT NOT NULL, resource NVARCHAR(40) NOT NULL);2INSERT INTO waits VALUES3  (51, 52, N'KEY: orders id=10'),4  (52, 51, N'KEY: customers id=7'),5  (60, 51, N'KEY: orders id=10'),6  (61, 62, N'OBJECT: stock');

1SELECT w.waiter, w.holder, w.resource,2       CASE WHEN EXISTS (SELECT 1 FROM waits AS x WHERE x.waiter = w.holder)3            THEN N'holder is waiting too'4            ELSE N'holder is running' END AS holder_state5FROM waits AS w6ORDER BY w.waiter;

Three common shapes

• Opposite access order. One procedure updates orders then customers; another updates customers then orders. Fix: touch tables (and rows) in one agreed order everywhere.
• Lock conversion. Two sessions read the same row under REPEATABLE READ or SERIALIZABLE, both keep their shared lock, and both then try to update it. Each waits for the other's shared lock to go. Fix: read with WITH (UPDLOCK) when you intend to update, so the second reader waits at the read instead.
• Key lookup against update. A reader seeks a nonclustered index and then looks up the clustered index; a writer updates the clustered row and then the nonclustered index entry. They take the two locks in opposite order. Fix: a covering index removes the lookup.

General remedies: keep transactions short, index so statements touch few rows, and consider RCSI, under which plain reads take no shared locks and so stop taking part in reader-writer deadlocks.

Retry, because deadlocks still happen

A deadlock victim's transaction was rolled back, so it is safe to run the whole transaction again. Retry on 1205 a small number of times with a short delay, and only for errors that a retry can fix.

The example below does not contain a deadlock. It simulates one: THROW cannot raise error numbers below 50000, so the procedure raises a custom error 51205 on its first two attempts to stand in for the real 1205, and the caller retries on either number. What it shows is the retry loop and the rollback of each failed attempt, not how SQL Server picks a victim.

1CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL);2CREATE TABLE attempts (n INT NOT NULL);3INSERT INTO accounts VALUES (1, 100), (2, 0);4INSERT INTO attempts VALUES (0);5GO6CREATE PROCEDURE dbo.transfer @amount INT7AS8BEGIN9  SET NOCOUNT ON;10  SET XACT_ABORT ON;11  BEGIN TRY12    BEGIN TRAN;13      UPDATE accounts SET balance = balance - @amount WHERE id = 1;14      UPDATE attempts SET n = n + 1;15      IF (SELECT n FROM attempts) <= 216        THROW 51205, N'Simulated: chosen as deadlock victim.', 1;17      UPDATE accounts SET balance = balance + @amount WHERE id = 2;18    COMMIT;19  END TRY20  BEGIN CATCH21    IF @@TRANCOUNT > 0 ROLLBACK;22    UPDATE attempts SET n = n + 1;   -- keep the count outside the rolled-back work23    THROW;24  END CATCH;25END;

1DECLARE @log TABLE (attempt INT, outcome NVARCHAR(40));2DECLARE @attempt INT = 1, @done BIT = 0;3 4WHILE @done = 0 AND @attempt <= 55BEGIN6  BEGIN TRY7    EXEC dbo.transfer @amount = 30;8    INSERT INTO @log VALUES (@attempt, N'committed');9    SET @done = 1;10  END TRY11  BEGIN CATCH12    INSERT INTO @log VALUES (@attempt, CONCAT(N'error ', ERROR_NUMBER()));13    IF ERROR_NUMBER() NOT IN (1205, 51205) BREAK;14    WAITFOR DELAY '00:00:00.050';15    SET @attempt += 1;16  END CATCH;17END;18 19SELECT l.attempt, l.outcome,20       (SELECT balance FROM accounts WHERE id = 1) AS balance_1,21       (SELECT balance FROM accounts WHERE id = 2) AS balance_222FROM @log AS l23ORDER BY l.attempt;

Common mistakes

• Retrying a single statement instead of the whole transaction. The victim's earlier statements were rolled back too.
• Retrying forever, or on every error. Bound the attempts; a constraint violation will not go away on the next try.
• Fixing deadlocks with NOLOCK. It hides reader-writer deadlocks by allowing dirty reads; writer-writer cycles remain.
• Guessing instead of reading the deadlock graph from system_health.

What interviewers ask

• Deadlock or blocking? Blocking resolves when the holder finishes; a deadlock is a cycle that only ends when a victim is killed.
• How does SQL Server choose the victim? Lowest DEADLOCK_PRIORITY, then the cheapest transaction to roll back.
• How would you investigate and fix a recurring deadlock? Read the graph from system_health, find the two statements and lock order, then fix access order, add a covering index or UPDLOCK, and add a 1205 retry.