How to Read a SQL Server Deadlock Graph Without Guesswork

The first time an application team brought me a week of error 1205s, their proposed fix was a retry loop and their proposed timeline was Friday. The retry loop went in, because it had to, and the deadlocks did not care, because nobody had read the deadlock graph. That graph is not an intimidating blob of XML. It is a short story with two protagonists, a contested resource, and a victim, and SQL Server has been writing that story down for you on every deadlock since 2012. You only have to open it.

What follows is how I capture, read, and permanently kill recurring deadlocks on SQL Server 2016 through 2022 and Azure SQL Database. The reading part is a skill you pick up after a dozen graphs; the capturing part takes five minutes and costs nothing.

How do I capture deadlock graphs without trace flags?

You do not need trace flags 1204 or 1222 anymore, because the built-in system_health Extended Events session has captured every xml_deadlock_report by default since SQL Server 2012, on-prem and in Azure SQL Managed Instance. Azure SQL Database is the exception: it has no system_health session, so there you create your own database-scoped session that captures database_xml_deadlock_report, typically writing to an Azure Storage file target, and the graphs it produces are the same graphs. The old trace-flag era dumped a text rendering of the graph into the error log and felt like a configuration decision you had to argue about in change control. system_health just runs, from the moment the instance starts, whether you asked it to or not.

One caveat worth knowing before an incident: the default file target for system_health is small, a handful of rollover files of a few MB each, so on a busy or chatty instance deadlock graphs age out within hours. If deadlocks are a chronic investigation on your system, either copy the xel files off the box on a schedule or create your own lightweight session with a larger file target that captures only xml_deadlock_report. Pulling what the default session still holds is one query:

-- every deadlock graph system_health has not rotated out yet
SELECT CAST(event_data AS XML) AS deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

That exact query does not carry over to Azure SQL Database, where the built-in session and its local xel files do not exist; the graph-reading skills below do. Each returned row is one deadlock event; click the XML in SSMS and it renders as the familiar graph, or keep it as XML and parse the pieces you care about.

How do I read the victim, owner, and waiter nodes?

Start at the victim-list, then read the process-list as the protagonists and the resource-list as the thing they fought over. The victim-list names the process SQL Server killed to break the cycle, chosen by estimated rollback cost, essentially how much log the transaction had written, unless someone deliberately set DEADLOCK_PRIORITY. The process-list gives you each participant with its spid, its isolation level, and its inputbuf, which holds the last statement that session ran. That statement is usually the smoking gun.

The resource-list is where the cycle becomes visible. Every contested resource carries an owner-list, showing which process holds it and in which lock mode, and a waiter-list, showing which process wants it and in which mode. The classic shape is a clean two-node circle: process A owns key K1 in mode X and waits for key K2 in mode U, while process B owns K2 and waits for K1. Draw it on paper the first few times. After a dozen graphs you read them like headlines. And remember the economics: the deadlock monitor scans for cycles every few seconds and kills immediately when it finds one, so this graph is the entire postmortem you get. There is no surviving crime scene, only this document.

What do KEY, PAGE, and OBJECT resources tell you?

The resource type tells you which layer of the schema to fix. A keylock means the fight is over specific index keys, and the resource carries objectname and indexname attributes that name the exact index, so the fix is almost always index shape. A pagelock means sessions are fighting over whole pages at once, which usually means a scan or update is touching far more pages than it should for want of a better index; if a previous tuner left WITH (PAGELOCK) hints in the code, removing them is your first move. A ridlock is the heap-table variant of the same story.

An objectlock is a table-level lock, and the lock mode decides which story you are in. Schema modes, a Sch-M from an ALTER colliding with the Sch-S that compiling and executing sessions hold, mean schema-stability or compile-time contention between modules, triggers re-entering the same procedure, or a recompile storm. But ordinary data modes, S, X, IX, mean two sessions really are fighting over table data: lock escalation promoted thousands of row or page locks into one table lock, or someone left a TABLOCK hint in the code. Read the mode attribute in the owner and waiter lists before assigning a cause. Schema modes send me to the calling pattern, who compiles what and when; data modes send me to the update that got big enough to escalate.

Which three patterns cause most of the deadlocks I read?

The lookup deadlock, the unindexed foreign key, and the serializable upsert account for most graphs I have ever untangled. The lookup deadlock is the classic: a query seeks a nonclustered index and then does a key lookup back to the clustered row, while a concurrent writer holds the clustered row and needs to update that same nonclustered key. Reader holds shared on the nonclustered key and wants shared on the clustered row; writer holds exclusive on the clustered row and wants the nonclustered key. Clean circle, one victim. The fix is to add the looked-up columns as INCLUDE columns on the nonclustered index so the lookup disappears entirely. I watched a system go from forty deadlocks a day to zero with two INCLUDE columns.

The unindexed foreign key is quieter and meaner. Deleting or updating a parent row forces the engine to validate against the child table, and without an index on the child's foreign key columns that validation scans and locks its way across the child, colliding with every concurrent child insert. The graph shows PAGE or KEY waits on the child table and the fix is one index away. The serializable upsert is the trickiest: an IF EXISTS check followed by INSERT under HOLDLOCK or serializable isolation, or a trigger-based upsert, takes range locks, RangeS-U on the probe and RangeX-X on the key plus the gap beside it. Two sessions upserting keys that would sort next to each other deadlock on each other's gaps. The fix starts with a suitable index so the probe locks the narrowest possible gap instead of a scan-sized range, though under serializable isolation the gap lock on a key that does not exist never fully goes away, because that is the mechanism that keeps phantoms out. Beyond the index, keep the transaction as narrow as possible, and prefer insert-and-handle-the-duplicate patterns over check-then-insert where the platform allows.

Is retry logic for error 1205 enough?

Retry logic is the seatbelt, not the repair. Error 1205 means your transaction was chosen as the victim and rolled back completely; nothing was applied, and the only correct response is to replay the entire logical transaction, with capped attempts and a small jittered delay. Three attempts is plenty. If the third attempt fails, the system has a real problem and retrying harder only hides it.

DECLARE @attempt int = 0;
WHILE @attempt < 3
BEGIN
  BEGIN TRY
    BEGIN TRAN;
    -- the real statements of the transaction
    COMMIT;
    BREAK;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    IF ERROR_NUMBER() = 1205 AND @attempt < 2
    BEGIN
      SET @attempt += 1;
      -- jittered backoff: 100 ms base per attempt plus 0-199 ms random
      DECLARE @delay char(12) = CONVERT(char(12),
        DATEADD(millisecond, 100 * @attempt + ABS(CHECKSUM(NEWID())) % 200, 0), 114);
      WAITFOR DELAY @delay;
    END
    ELSE THROW; -- non-deadlock errors, and the third 1205, reach the caller
  END CATCH
END

Understand what the retry loop actually costs. The victim's work was thrown away, and it was chosen precisely because it was the cheapest to throw away. Under a retry storm your error rate can look acceptable while your p99 latency doubles, because every retried transaction ran twice. SET DEADLOCK_PRIORITY LOW on a reporting or batch session is a useful valve since it shifts victimhood onto the workload you mind least, but it is still a valve, not a fix. The reason fixing index shape beats retry loops is compounding: retries convert deadlocks into latency and extra load, and extra load makes deadlocks more likely. The loop feeds itself. If your graphs are really long blocking chains that occasionally tip into a deadlock, the wait-stats route in my notes on wait statistics triage is the complementary read, because LCK_M waits will tell you the blocking story before the deadlock monitor does.

Watching deadlocks with MonPG when SQL Server support ships

The telemetry I want for this problem is specific: deadlocks per minute parsed straight from the extended events stream, the victim statements ranked by frequency, and the split of KEY versus PAGE versus OBJECT resources so the index work is aimed instead of guessed. MonPG monitors PostgreSQL in production today, and the cross-engine version of this same class of problem is written up in deadlock diagnosis across MySQL and PostgreSQL. SQL Server support is in active development, and making deadlock-graph triage boring is high on the list of things it should do. Honest status lives on the SQL Server monitoring (coming soon) page, and until it ships the system_health query above is your best friend.