Diagnosing deadlocks in SQL Server with Extended Events

A deadlock happens when two (or more) sessions each hold a lock the other one needs. Neither can proceed, so SQL Server's deadlock monitor picks a victim, rolls back its transaction and raises:

Msg 1205: Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

The good news: SQL Server already recorded everything you need to fix it.

A minimal deadlock

Two sessions update the same two rows in opposite order:

-- Session 1
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance -= 10 WHERE AccountID = 1;
-- ...pause...
UPDATE dbo.Accounts SET Balance += 10 WHERE AccountID = 2;

-- Session 2
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance -= 5 WHERE AccountID = 2;
UPDATE dbo.Accounts SET Balance += 5 WHERE AccountID = 1;

Session 1 holds an exclusive lock on row 1 and waits for row 2; session 2 holds row 2 and waits for row 1. Classic cycle.

Get the deadlock graph from system_health

The built-in system_health Extended Events session is on by default and captures xml_deadlock_report events. Query its file target:

SELECT
    xed.value('@timestamp', 'datetime2') AS occurred_at,
    xed.query('.') AS deadlock_graph
FROM (
    SELECT CAST(event_data AS xml) AS event_xml
    FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
    WHERE object_name = 'xml_deadlock_report'
) AS t
CROSS APPLY t.event_xml.nodes('/event') AS x(xed)
ORDER BY occurred_at DESC;

Click the XML in SSMS and save it as a .xdl file to see the graphical deadlock graph.

Reading the graph

A deadlock graph has three parts:

• Processes (ovals) — each session involved, including the statement it was running and its isolation level. The victim is crossed out.
• Resources (rectangles) — what was locked: a keylock, pagelock, objectlock, etc., with the index name.
• Edges — "owner" (holds a lock, with mode such as X or S) and "waiter" (requests a lock).

Ask three questions:

1. Which statements were involved? (inputbuf / frame elements)
2. Which index were they fighting over? (objectname and indexname)
3. Which lock modes conflicted? (mode / requestType)

The usual fixes

Access objects in a consistent order. If every transaction updates accounts in ascending AccountID order, the cycle above cannot form.

Keep transactions short. Don't hold locks while waiting on user input, API calls or big result sets.

Add the right index. Many deadlocks involve a scan that locks far more rows than needed. A seekable index shrinks the lock footprint.

Consider row versioning. Reader/writer deadlocks often disappear with READ_COMMITTED_SNAPSHOT, because readers stop taking shared locks:

ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Retry the victim. Deadlocks can be reduced but not always eliminated. Application code should catch error 1205 and retry the transaction with a short backoff.

Choosing the victim yourself

By default SQL Server picks the victim that is cheapest to roll back. You can influence it for low-priority work:

SET DEADLOCK_PRIORITY LOW; -- this session volunteers to be the victim

Key takeaways

1. The system_health session already captured your deadlocks — go read them.
2. Identify the statements, the index and the lock modes involved.
3. Fix with consistent access order, shorter transactions, better indexes or row versioning.
4. Always make deadlock victims retryable.