Deadlock patterns

In one line: a deadlock is two sessions taking the same locks in opposite orders, each waiting for the other. SQL Server breaks the circle by rolling one back with Msg 1205, and most real deadlocks are one of a handful of patterns.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/deadlock-patterns on GitHub.

What you’ll see

  • Msg 1205: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
  • Intermittent failures that vanish when you run the same code by hand.
  • A deadlock is also a long wait: the lock monitor checks every 5 seconds, and in the demo it took 5.7 s to find the cycle.

Why it happens

The three patterns the demo reproduces:

  • Opposite order. Session 1 updates account 1, then account 2. Session 2 updates account 2, then account 1. Each holds what the other wants.
  • One row, read then update. Under REPEATABLE READ, two sessions read the same row and keep their shared locks, then both try to update it. Each exclusive lock waits on the other’s shared lock.
  • A SELECT and an UPDATE through a key lookup. No transaction and no hints are needed. The reader locks the nonclustered index row, then the clustered row. The writer locks the clustered row, then the index row. That produced 24 deadlocks in 5 seconds in the demo.

What to do

  • Take locks in the same order everywhere: parent before child, IDs ascending, tables in the same order in every procedure. The same pair of transactions in the same order gave a 2-second wait instead of a deadlock.
  • Take the strongest lock you’ll need first. Read with WITH (UPDLOCK) when you’re about to update. The second session waits at its read and then sees the first one’s write, with no deadlock and no lost update.
  • Make the reader’s index covering. Including the looked-up column removed every key-lookup deadlock, and the reader managed 9,425 reads in the time it had managed 73. RCSI also fixes this pattern.
  • Retry on 1205. The victim did nothing wrong. In the demo, the retried transaction committed on its second attempt. Cap the attempts.

What doesn’t help

  • Assuming one row or no explicit transaction is safe. Both deadlocked in the demo.
  • Turning on a trace flag to capture graphs. The built-in system_health session already records every deadlock graph by default.

Check your server

-- Deadlock graphs already recorded by the built-in system_health session.
SELECT  DeadlockTime = x.ev.value('@timestamp', 'datetime2'),
        Graph        = x.ev.query('data/value/deadlock')
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) AS f
CROSS APPLY (SELECT CAST(f.event_data AS xml)) AS t(x_data)
CROSS APPLY t.x_data.nodes('/event') AS x(ev)
WHERE f.object_name = 'xml_deadlock_report'
ORDER BY DeadlockTime DESC;

Two indexes of the same table in one graph is the key-lookup pattern, and the fix for it is in the index, not the code.

See it happen

The deadlock-patterns demo makes each pattern happen on cue between two sessions, applies the fix, retries on 1205, and then reads back one system_health graph for every deadlock it caused (27 for 27).

Related: blocking chains · upsert race conditions · RCSI and snapshot isolation · key lookups and the tipping point

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights