sql server hub / deadlocks guide

SQL Serverdeadlocks guide

A SQL Server deadlock happens when sessions wait on each other in a cycle. SQL Server breaks the cycle by rolling one transaction back with error 1205.

Use this guide when deadlock errors keep returning or the application log only shows the victim. Capture the deadlock XML, read the victim, process, and resource nodes, then connect the cycle to the application action.

Guide

Guide~8 min readUpdated 19 Apr 2026

LinkedIn X Email

01What SQL Server deadlocks are
02How to capture SQL Server deadlock XML
03How to read victim, process, and resource nodes
04What SQL Server deadlock XML fields mean
05How to map a deadlock to the application action
06Why transaction order causes deadlocks
07How indexes and execution plans affect deadlocks
08How isolation level and retry logic change deadlocks
09What not to change during a deadlock incident
10When to request a SQL Server performance review

What SQL Server deadlocks are

A deadlock happens when two or more tasks permanently block each other. Each task owns a resource the other task needs. SQL Server detects the cycle, picks a victim, rolls that transaction back, and lets the other task continue.

Deadlocks are related to blocking, but they are not the same issue. Blocking can wait and later clear. A deadlock cannot clear by itself because the sessions are waiting in a cycle.

A common example is an order workflow and an invoice workflow touching the same tables in the opposite order. Session 58 updates `SalesOrder` and then needs `Invoice`; session 72 updates `Invoice` and then needs `SalesOrder`. Neither can continue, so SQL Server rolls one back.

Concrete SQL Server deadlock examples

These are simplified scripts, not production scripts. They show the shape of the conflict: two sessions, two resources, and an order that can form a cycle. Replace the table and procedure names with the ones from your graph.

Opposite update order

Two workflows update the same two tables, but they touch them in a different order.

Session A

BEGIN TRAN;

UPDATE dbo.SalesOrder
SET Status = N'Invoicing'
WHERE SalesOrderID = 42;

-- Session B now owns the Invoice row this transaction wants.
UPDATE dbo.Invoice
SET InvoiceStatus = N'Ready'
WHERE InvoiceID = 9001;

COMMIT;

Session B

BEGIN TRAN;

UPDATE dbo.Invoice
SET InvoiceStatus = N'Posting'
WHERE InvoiceID = 9001;

-- Session A now owns the SalesOrder row this transaction wants.
UPDATE dbo.SalesOrder
SET Status = N'Posted'
WHERE SalesOrderID = 42;

COMMIT;

What to look for in XML: The XML usually shows two process nodes and two keylock or objectlock resources. Each process owns one lock and waits for the other.

Range read collides with a write

A reporting or validation transaction reads a protected range while another transaction writes into the same range.

Session A

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;

SELECT InvoiceID
FROM dbo.Invoice
WHERE CustomerID = 42
  AND InvoiceStatus = N'Unpaid';

UPDATE dbo.CustomerSummary
SET OpenInvoiceCount = OpenInvoiceCount + 1
WHERE CustomerID = 42;

COMMIT;

Session B

BEGIN TRAN;

INSERT dbo.Invoice (CustomerID, InvoiceStatus, Amount)
VALUES (42, N'Unpaid', 149.00);

UPDATE dbo.CustomerSummary
SET OpenInvoiceAmount = OpenInvoiceAmount + 149.00
WHERE CustomerID = 42;

COMMIT;

What to look for in XML: Look for keylock entries on an index range, serializable isolation, or a long reader holding locks while another session writes into the range.

Foreign key lookup deadlock

A parent row and child row are touched from different paths, and the foreign key check needs locks at the wrong time.

Session A

BEGIN TRAN;

UPDATE dbo.Customer
SET AccountStatus = N'Review'
WHERE CustomerID = 42;

UPDATE dbo.OrderLine
SET Quantity = Quantity + 1
WHERE OrderLineID = 7007;

COMMIT;

Session B

BEGIN TRAN;

UPDATE dbo.OrderLine
SET Quantity = Quantity + 2
WHERE OrderLineID = 7007;

-- The foreign key check needs to read the parent Customer row.
UPDATE dbo.OrderHeader
SET CustomerID = 42
WHERE OrderID = 5005;

COMMIT;

What to look for in XML: The resource list often names parent and child indexes. Missing or weak indexes around foreign keys can make this pattern much easier to hit.

Parallel exchange deadlock

A parallel query can deadlock inside its own execution plan, especially under memory pressure or skewed parallel work.

Session A

SELECT
    c.CustomerID,
    SUM(i.Amount) AS TotalAmount
FROM dbo.Customer AS c
JOIN dbo.Invoice AS i
    ON i.CustomerID = c.CustomerID
WHERE i.InvoiceDate >= DATEADD(day, -30, SYSUTCDATETIME())
GROUP BY c.CustomerID
OPTION (MAXDOP 8);

Session B

-- This is usually not a separate user transaction.
-- Other workers from the same parallel plan can be part of the cycle.
-- Capture the actual plan and check exchange operators, memory grant,
-- row estimates, and whether MAXDOP changed recently.

What to look for in XML: The resource list contains exchangeEvent nodes instead of ordinary keylock or pagelock nodes.

Application lock order deadlock

Application locks are useful, but they can deadlock when different code paths request lock names in different order.

Session A

BEGIN TRAN;

EXEC sys.sp_getapplock
    @Resource = N'Customer:42',
    @LockMode = N'Exclusive',
    @LockOwner = N'Transaction';

EXEC sys.sp_getapplock
    @Resource = N'Invoice:42',
    @LockMode = N'Exclusive',
    @LockOwner = N'Transaction';

COMMIT;

Session B

BEGIN TRAN;

EXEC sys.sp_getapplock
    @Resource = N'Invoice:42',
    @LockMode = N'Exclusive',
    @LockOwner = N'Transaction';

EXEC sys.sp_getapplock
    @Resource = N'Customer:42',
    @LockMode = N'Exclusive',
    @LockOwner = N'Transaction';

COMMIT;

What to look for in XML: The XML shows applicationlock resources. The fix is usually consistent app-lock ordering, not an index change.

Thing to read	Why it matters
Application error 1205	Confirms SQL Server rolled back one transaction.
victim-list	Shows which process lost the deadlock choice.
process-list	Shows sessions, statements, input buffers, isolation levels, and transaction state.
resource-list	Shows the locks or other resources that created the cycle.

How to capture SQL Server deadlock XML

In modern SQL Server, use the `xml_deadlock_report` Extended Event. The built-in `system_health` session captures it by default, so many systems already have recent deadlock XML available.

Start with `system_health`. If deadlocks are frequent or the ring buffer rolls over too quickly, use the event file target or create a dedicated Extended Events session later. Do not make SQL Profiler or SQL Trace the preferred capture method.

How to read deadlocks from system_health ring buffer

Reads xml_deadlock_report events from the system_health ring buffer.

SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_time,
    xdr.query('(data/value/deadlock)[1]') AS deadlock_xml
FROM (
    SELECT CAST(target_data AS xml) AS target_data
    FROM sys.dm_xe_session_targets AS xt
    JOIN sys.dm_xe_sessions AS xs
        ON xs.address = xt.event_session_address
    WHERE xs.name = N'system_health'
      AND xt.target_name = N'ring_buffer'
) AS rb
CROSS APPLY rb.target_data.nodes(
    'RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS XEventData(xdr)
ORDER BY deadlock_time DESC;

deadlock_time tells you when the cycle was captured. deadlock_xml contains the victim, process, and resource nodes. The ring buffer is convenient, but older events can disappear on busy systems. If this returns no rows, try the event file target or check whether the deadlock happened on another instance.

How to read deadlocks from system_health event files

Reads xml_deadlock_report events from the system_health event_file target when that target is available.

DECLARE @system_health_file nvarchar(260);

SELECT
    @system_health_file =
        CAST(target_data AS xml).value(
            '(EventFileTarget/File/@name)[1]',
            'nvarchar(260)'
        )
FROM sys.dm_xe_session_targets AS xt
JOIN sys.dm_xe_sessions AS xs
    ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
  AND xt.target_name = N'event_file';

SELECT
    xed.object_name,
    xed.timestamp_utc,
    CAST(xed.event_data AS xml).query('(event/data/value/deadlock)[1]') AS deadlock_xml
FROM sys.fn_xe_file_target_read_file(@system_health_file, NULL, NULL, NULL) AS xed
WHERE xed.object_name = N'xml_deadlock_report'
ORDER BY xed.timestamp_utc DESC;

The event file usually keeps more history than the ring buffer. If @system_health_file is null, this instance may not expose the event_file target in the expected shape. Use the returned XML, not only the timestamp, for diagnosis. For recurring production issues, consider a dedicated Extended Events session after the first pass.

How to read victim, process, and resource nodes

The deadlock graph usually has three useful areas. The victim list shows which process lost. The process list shows the sessions and statements. The resource list shows what each process owned and waited for.

For example, a short checkout update may be chosen as the victim even when the wider problem is a nightly stock-sync job holding locks too long. Read the victim, but compare it with the other process before deciding what to fix.

How to find the deadlock victim

Extracts victim process IDs from recent deadlock XML.

WITH Deadlocks AS (
    SELECT
        xdr.value('@timestamp', 'datetime2') AS deadlock_time,
        CAST(xdr.query('(data/value/deadlock)[1]') AS xml) AS deadlock_xml
    FROM (
        SELECT CAST(target_data AS xml) AS target_data
        FROM sys.dm_xe_session_targets AS xt
        JOIN sys.dm_xe_sessions AS xs
            ON xs.address = xt.event_session_address
        WHERE xs.name = N'system_health'
          AND xt.target_name = N'ring_buffer'
    ) AS rb
    CROSS APPLY rb.target_data.nodes(
        'RingBufferTarget/event[@name="xml_deadlock_report"]'
    ) AS XEventData(xdr)
)
SELECT
    d.deadlock_time,
    victim.value('@id', 'nvarchar(100)') AS victim_process_id
FROM Deadlocks AS d
CROSS APPLY d.deadlock_xml.nodes('/deadlock/victim-list/victimProcess') AS V(victim)
ORDER BY d.deadlock_time DESC;

The victim_process_id maps to process id values in the process-list. This is not the same thing as the SQL Server session_id. Use it to identify the rolled-back participant, then read all participants. A repeated victim may point to retry-sensitive application behavior.

How to extract process details

Extracts session, application, login, isolation level, wait resource, transaction count, and input buffer from process-list.

WITH Deadlocks AS (
    SELECT TOP (10)
        xdr.value('@timestamp', 'datetime2') AS deadlock_time,
        CAST(xdr.query('(data/value/deadlock)[1]') AS xml) AS deadlock_xml
    FROM (
        SELECT CAST(target_data AS xml) AS target_data
        FROM sys.dm_xe_session_targets AS xt
        JOIN sys.dm_xe_sessions AS xs
            ON xs.address = xt.event_session_address
        WHERE xs.name = N'system_health'
          AND xt.target_name = N'ring_buffer'
    ) AS rb
    CROSS APPLY rb.target_data.nodes(
        'RingBufferTarget/event[@name="xml_deadlock_report"]'
    ) AS XEventData(xdr)
    ORDER BY xdr.value('@timestamp', 'datetime2') DESC
)
SELECT
    d.deadlock_time,
    p.value('@id', 'nvarchar(100)') AS process_id,
    p.value('@spid', 'int') AS spid,
    p.value('@hostname', 'nvarchar(256)') AS host_name,
    p.value('@clientapp', 'nvarchar(256)') AS application_name,
    p.value('@loginname', 'nvarchar(256)') AS login_name,
    p.value('@isolationlevel', 'nvarchar(100)') AS isolation_level,
    DB_NAME(TRY_CONVERT(int, p.value('@currentdb', 'nvarchar(20)'))) AS database_name,
    p.value('@waitresource', 'nvarchar(256)') AS wait_resource,
    p.value('@waittime', 'bigint') AS wait_time_ms,
    p.value('@lockMode', 'nvarchar(20)') AS requested_lock_mode,
    p.value('@trancount', 'int') AS transaction_count,
    p.value('@logused', 'bigint') AS log_used,
    p.value('(inputbuf/text())[1]', 'nvarchar(max)') AS input_buffer
FROM Deadlocks AS d
CROSS APPLY d.deadlock_xml.nodes('/deadlock/process-list/process') AS P(p)
ORDER BY d.deadlock_time DESC, spid;

input_buffer often gives the best first clue about the application action. isolation_level helps decide whether read/write behavior or versioning matters. log_used can help explain victim choice when deadlock priority is equal. transaction_count greater than zero deserves transaction-scope review.

How to extract resource details

Extracts resource type, object or index name, owner process, waiter process, and lock modes from resource-list.

WITH Deadlocks AS (
    SELECT TOP (10)
        xdr.value('@timestamp', 'datetime2') AS deadlock_time,
        CAST(xdr.query('(data/value/deadlock)[1]') AS xml) AS deadlock_xml
    FROM (
        SELECT CAST(target_data AS xml) AS target_data
        FROM sys.dm_xe_session_targets AS xt
        JOIN sys.dm_xe_sessions AS xs
            ON xs.address = xt.event_session_address
        WHERE xs.name = N'system_health'
          AND xt.target_name = N'ring_buffer'
    ) AS rb
    CROSS APPLY rb.target_data.nodes(
        'RingBufferTarget/event[@name="xml_deadlock_report"]'
    ) AS XEventData(xdr)
    ORDER BY xdr.value('@timestamp', 'datetime2') DESC
)
SELECT
    d.deadlock_time,
    r.value('local-name(.)', 'nvarchar(80)') AS resource_type,
    r.value('@objectname', 'nvarchar(512)') AS object_name,
    r.value('@indexname', 'nvarchar(512)') AS index_name,
    r.value('@mode', 'nvarchar(20)') AS resource_mode,
    owner.value('@id', 'nvarchar(100)') AS owner_process_id,
    owner.value('@mode', 'nvarchar(20)') AS owner_mode,
    waiter.value('@id', 'nvarchar(100)') AS waiter_process_id,
    waiter.value('@mode', 'nvarchar(20)') AS waiter_mode,
    waiter.value('@requestType', 'nvarchar(60)') AS waiter_request_type
FROM Deadlocks AS d
CROSS APPLY d.deadlock_xml.nodes('/deadlock/resource-list/*') AS R(r)
OUTER APPLY r.nodes('owner-list/owner') AS O(owner)
OUTER APPLY r.nodes('waiter-list/waiter') AS W(waiter)
ORDER BY d.deadlock_time DESC, resource_type, object_name;

resource_type tells you whether the cycle involves key, page, object, exchange, or another resource shape. owner_mode and waiter_mode show who held what and who wanted what. object_name and index_name are not always present, but they are very useful when available. If the resource points to a hot index, check the execution plan and access order next.

What SQL Server deadlock XML fields mean

The XML is easier to read when you know which part answers which question. In a two-table update deadlock, the process nodes show the two statements, while the resource nodes show that one process owns a lock on table A and waits for table B, and the other owns table B and waits for table A.

Field names vary a little by deadlock shape and SQL Server version. Missing fields are normal. A `keylock` graph points you toward access order and indexing; an `exchangeEvent` graph points you toward parallel plan behavior; an `applicationlock` graph points you toward app-level lock ordering.

XML area	What it answers
victim-list	Which process SQL Server rolled back to break the cycle.
process-list	Which sessions, applications, statements, isolation levels, and transaction details participated.
resource-list	Which resources were owned, which were requested, and how the cycle formed.
owner-list	Which process already held the resource and in what mode.
waiter-list	Which process was waiting for the resource and in what mode.

Process field	Meaning
spid	The SQL Server session id at the time of the deadlock.
hostname	The client machine name reported by the connection.
clientapp	The application name reported by the connection string or client library.
loginname	The SQL Server login used by the session.
isolationlevel	The isolation level active for the process.
waitresource	The resource the process was waiting for.
waittime	How long the process had waited, in milliseconds.
lockMode	The lock mode the process requested.
trancount	The transaction nesting count reported for the process.
logused	Log space used by the transaction; this can affect victim choice.
inputbuf	The submitted statement or batch text available in the graph.

Resource node	What it usually points to
keylock	A lock on an index key. This is common in row-level deadlocks.
pagelock	A lock on a data or index page.
objectlock	A lock on an object such as a table.
ridlock	A lock on a heap row identifier.
exchangeEvent	A parallel-query exchange deadlock.
threadpool	A deadlock involving worker thread starvation.
metadata	A deadlock involving metadata access.
applicationlock	A deadlock involving application locks from sp_getapplock.

Owner or waiter value	How to use it
owner mode	The lock mode already held by a process.
waiter mode	The lock mode another process wanted.
requestType	Whether the waiter wanted to wait, convert, or otherwise change lock state.
objectname	The object involved, when SQL Server can name it.
indexname	The index involved, when SQL Server can name it.
process id	The XML process id. Match it back to process-list; it is not the same as spid.

How to map a deadlock to the application action

The process list tells you what SQL Server saw. The application flow explains why it happened. A stored procedure, API endpoint, report, job step, or queue worker may be the real unit that needs review.

Example: the XML might show `CheckoutService` updating `Orders` while `InventoryWorker` updates `StockReservation`. The useful next step is not only to read those statement names, but to confirm which endpoint, queue message, or job step caused each statement and whether they touch the same objects in a different order.

Application action or job step for each process.

Stored procedure, statement, or ORM-generated command involved.

Object access order across the participants.

Whether the same pattern appears repeatedly in logs or XML.

Why transaction order causes deadlocks

The classic deadlock shape is inconsistent order: one path touches object A then B, while another touches B then A. Long transactions make that pattern easier to hit because locks are held longer.

The fix often lives in stored procedure design, application flow, batch size, or the order in which objects are touched. SQL Server settings rarely fix an inconsistent business transaction by themselves.

Different code paths touching shared tables in different order.

Transactions opened earlier than the real critical section.

Large batches that could safely commit in smaller units.

Application work, remote calls, or user interaction while locks are held.

How indexes and execution plans affect deadlocks

Poor access paths can widen the deadlock window. A statement that scans too much data, uses a plan regression, or locks a hot range for too long gives other sessions more time to collide with it.

Use the XML to identify objects and indexes involved, then check actual plans, Query Store history, and workload frequency. One deadlock graph is a starting point, not an index-design mandate.

Pattern	What to check
Key locks on the same table from different statements	Access order, index choice, and transaction scope.
Wide reads before writes	Plan shape, predicates, row estimates, and supporting indexes.
Deadlocks after a release	Plan changes, new query shape, changed transaction order, or new batch timing.
Deadlocks during jobs	Job timing, isolation, batch size, and overlap with live traffic.

How isolation level and retry logic change deadlocks

Isolation-level changes can reduce some read/write conflicts, but they also change semantics and may move work into row versioning and tempdb. Treat them as design changes, not incident buttons.

Applications should handle error 1205 deliberately. Retrying can be valid, but blind immediate retries can amplify the same concurrency pattern.

Whether retry logic has a small delay, a retry limit, and logging.

Whether the operation is safe to retry without duplicating business effects.

Whether DEADLOCK_PRIORITY is being used for deliberate victim choice.

Whether isolation changes would alter correctness expectations.

How to check active blocking while deadlocks are happening

Use this only when deadlock errors are happening now and sessions are still waiting.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.open_transaction_count,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID
  AND (r.blocking_session_id <> 0 OR r.wait_type LIKE N'LCK%')
ORDER BY r.wait_time DESC;

This does not show past deadlocks; it only shows current waiting requests. LCK waits during the same window can help connect deadlocks to broader blocking. If the pattern is still active, continue with the blocking guide checks. If this returns no rows, rely on deadlock XML, Query Store, application logs, and job timing.

What not to change during a deadlock incident

Deadlock fixes should be tied to the graph. The wrong change can hide the symptom, make data correctness weaker, or create a different concurrency problem.

Do not kill or rewrite the victim query just because it received error 1205; first check what the other session owned.

Do not accept application logs that only say `Transaction was deadlocked`; capture the XML so the involved objects and lock modes are visible.

Do not use SQL Profiler or SQL Trace as the preferred modern capture method.

Do not set DEADLOCK_PRIORITY as a fix unless the business victim choice is deliberate.

Do not add indexes from one graph without checking workload frequency, write cost, and plan impact.

Do not rely on blind retry loops without delay, bounded retries, and logging.

When to request a SQL Server performance review

A performance review makes sense when deadlocks keep returning, the graph is hard to read, or the fix might touch application flow, indexing, isolation, and retry behavior at the same time.

Send the deadlock XML, timing, query text, plans, application error 1205 details, retry behavior, and what changed recently. That is enough to start separating transaction design, access paths, job timing, and application behavior.

Request performance review Read the blocking guide

Next step

If the deadlock pattern is active and production impact is visible, use the SQL Server performance review page or request the review above.

Next useful reads: the SQL Server blocking guide for live lock chains, the SQL Server indexing guide for access-path fixes, and the SQL Server monitoring guide for earlier capture of recurrence.