16 - Deadlock Detection and Prevention

18 min read

Deadlock Detection and Prevention

Deadlock Fundamentals

This section establishes the conceptual baseline: how a deadlock differs from ordinary blocking, and the decision flow to follow when one is reported in production.

SQL Server | Deadlock | Concept and triage

A deadlock is a circular wait between sessions, not a linear queue. Recognizing that distinction is what allows a DBA to jump straight from “error 1205” to the deadlock graph instead of hunting through wait stats.

Circular wait vs. linear blocking

Blocking is linear: one session waits for another to finish. A deadlock is circular: session A needs a lock held by session B, while session B needs a lock held by session A. That cycle cannot resolve without intervention.

flowchart LR
    A[Session 55<br/>holds lock on deadlock_demo_a] --> B[Needs lock on deadlock_demo_b]
    C[Session 56<br/>holds lock on deadlock_demo_b] --> D[Needs lock on deadlock_demo_a]
    B --> C
    D --> A

Production triage decision flow

flowchart TD
    A[Deadlock error 1205 or stalled workload] --> B{Do you already have a deadlock graph?}
    B --> Y1[YES]
    B --> N1[NO]
    Y1 --> C[Identify victim, survivor, and contested objects]
    N1 --> D[Check system_health and persistent XE capture]
    D --> E{Was a deadlock captured?}
    E --> Y2[YES]
    E --> N2[NO]
    Y2 --> F[Fix access order, access path, or transaction scope]
    N2 --> G[Create persistent deadlock XE session]

    classDef yes fill:#1f3b2d,stroke:#73d13d,color:#c0caf5;
    classDef no fill:#4a1f24,stroke:#db4b4b,color:#c0caf5;
    class Y1,Y2 yes;
    class N1,N2 no;

Detection From system_health

The built-in system_health Extended Events session captures xml_deadlock_report events by default on modern SQL Server builds. These three queries extract progressively more detail from it — from a simple count to a full victim-and-resource summary.

SQL Server | system_health | Forensic extraction

system_health is usually the first stop when investigating a deadlock because it requires no setup and persists across restarts. The queries below locate its event file, count its captured deadlocks, and extract the latest graph.

Count captured deadlock events

The fastest production check is to count how many deadlock reports are already present in the built-in system_health Extended Events session.

DECLARE @path nvarchar(4000);
 
SELECT @path = REPLACE(
    CAST(t.target_data AS xml).value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)'),
    '.xel',
    '*.xel'
)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
WHERE s.name = 'system_health'
  AND t.target_name = 'event_file';
 
SELECT COUNT(*) AS deadlock_event_count
FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';
deadlock_event_count
2

This instance already has two captured deadlock graphs in system_health. That is enough to do real forensic analysis without waiting for the next deadlock.

List recent deadlock reports with sessions and objects

The next step is to extract a compact summary of the latest deadlock reports so you can see the victim, the sessions involved, and the contested objects.

DECLARE @path nvarchar(4000);
 
SELECT @path = REPLACE(
    CAST(t.target_data AS xml).value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)'),
    '.xel',
    '*.xel'
)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
WHERE s.name = 'system_health'
  AND t.target_name = 'event_file';
 
;WITH src AS (
    SELECT TOP (5)
        CAST(event_data AS xml) AS event_xml,
        file_name,
        file_offset
    FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL)
    WHERE object_name = 'xml_deadlock_report'
    ORDER BY file_name DESC, file_offset DESC
)
SELECT
    event_xml.value('(event/@timestamp)[1]', 'datetime2') AS utc_time,
    event_xml.value('(event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'nvarchar(100)') AS victim_process_id,
    event_xml.value('(event/data/value/deadlock/process-list/process[1]/@spid)[1]', 'int') AS process1_spid,
    event_xml.value('(event/data/value/deadlock/process-list/process[2]/@spid)[1]', 'int') AS process2_spid,
    event_xml.value('(event/data/value/deadlock/resource-list/*[1]/@objectname)[1]', 'nvarchar(256)') AS resource1_object,
    event_xml.value('(event/data/value/deadlock/resource-list/*[2]/@objectname)[1]', 'nvarchar(256)') AS resource2_object
FROM src
ORDER BY utc_time DESC;
utc_timevictim_process_idprocess1_spidprocess2_spidresource1_objectresource2_object
2026-04-08 17:40:24.9920000processf00070ca85556stoxx.dbo.deadlock_demo_bstoxx.dbo.deadlock_demo_a
2026-04-08 11:18:05.8550000processf000688c85356stoxx.dbo.dm_exec_requests_demostoxx.dbo.dm_exec_requests_demo

The newest deadlock is the controlled two-table demo: session 55 and session 56 deadlocked while touching deadlock_demo_a and deadlock_demo_b in opposite order. The older event shows a separate deadlock on dm_exec_requests_demo, which confirms this instance has already seen more than one concurrency pattern.

ColumnValueWatchMeaningImplication
utc_timeRecent timestampDependsWhen the deadlock occurred.Correlate with deployment windows, job schedules, and app logs.
victim_process_idNon-null process idNeutralDeadlock XML process identifier.Use it to map the victim inside the full graph.
process1_spid / process2_spidPositive session idsDependsSQL Server sessions involved in the cycle.These are the sessions to correlate with logs or captured SQL text.
resource*_objectSame object on both rows❌ when unexpectedBoth sides contended on the same table or index.Look for conflicting access order or hot-key activity.
resource*_objectDifferent objectsDependsThe cycle crossed tables or indexes.Ordered object access is often the first fix to test.

Extract the latest deadlock graph summary

For root-cause work, you need more than a count. You need the victim, the number of processes in the cycle, and the exact resources each side waited on.

DECLARE @path nvarchar(4000);
 
SELECT @path = REPLACE(
    CAST(t.target_data AS xml).value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)'),
    '.xel',
    '*.xel'
)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
WHERE s.name = 'system_health'
  AND t.target_name = 'event_file';
 
;WITH src AS (
    SELECT TOP (1)
        CAST(event_data AS xml) AS event_xml
    FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL)
    WHERE object_name = 'xml_deadlock_report'
    ORDER BY file_name DESC, file_offset DESC
)
SELECT
    event_xml.value('(event/@timestamp)[1]', 'datetime2') AS utc_time,
    event_xml.value('(event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'nvarchar(100)') AS victim_process_id,
    event_xml.value('count((event/data/value/deadlock/process-list/process))', 'int') AS process_count,
    event_xml.value('count((event/data/value/deadlock/resource-list/*))', 'int') AS resource_count,
    event_xml.value('(event/data/value/deadlock/process-list/process[1]/@spid)[1]', 'int') AS process1_spid,
    event_xml.value('(event/data/value/deadlock/process-list/process[1]/@waitresource)[1]', 'nvarchar(400)') AS process1_waitresource,
    event_xml.value('(event/data/value/deadlock/process-list/process[2]/@spid)[1]', 'int') AS process2_spid,
    event_xml.value('(event/data/value/deadlock/process-list/process[2]/@waitresource)[1]', 'nvarchar(400)') AS process2_waitresource
FROM src;
utc_timevictim_process_idprocess_countresource_countprocess1_spidprocess1_waitresourceprocess2_spidprocess2_waitresource
2026-04-08 17:40:24.9920000processf00070ca82255KEY: 5:72057594062241792 (8194443284a0)56KEY: 5:72057594062176256 (8194443284a0)

This graph is the classic two-process, two-resource deadlock: session 55 waited for a key on deadlock_demo_b, session 56 waited for a key on deadlock_demo_a, and SQL Server chose process processf00070ca8 as the victim. The graph is small, which is typical for ordered-access deadlocks.

ColumnValueWatchMeaningImplication
victim_process_idNon-null process idDependsProcess chosen for rollback.This is the workload that received error 1205.
process_count2NeutralTwo sessions participated.This is the most common deadlock shape.
process_countGreater than 2❌More than two sessions participated.Look for parallel fan-in, queue consumers, or wider graph complexity.
resource_count2NeutralTwo contested resources appear in the graph.Often points to opposite-order access across two objects or keys.
process*_waitresourceKEY: resourceDependsThe wait was on an index key.Narrow index access can still deadlock if lock order conflicts.
process*_waitresourcePAGE: or OBJECT: resource❌ when frequentThe deadlock involved broader resources.Look for scans, escalation, or DDL interaction.

Persistent Deadlock Capture

system_health rolls over and eventually loses older deadlock reports. When a workload is deadlock-sensitive, a dedicated Extended Events session with an owner-controlled retention policy is the right long-term answer.

SQL Server | Extended Events | Dedicated deadlock session

A dedicated session writes xml_deadlock_report events to a file target you control. Retention, storage location, and rollover behavior stop being shared with the general health session.

Create a persistent XE session for deadlocks

The built-in system_health session is useful, but production environments benefit from a dedicated deadlock capture session with a retention policy you control.

Warning

A dedicated Extended Events session changes server metadata and writes more diagnostic files. It is usually safe, but it should still follow change management and storage-retention standards.

Success

Use a dedicated deadlock session when deadlocks are important enough to warrant longer retention than the system_health rollover files provide.

CREATE EVENT SESSION [deadlock_capture_persistent]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = '/var/opt/mssql/log/deadlock_capture_persistent'
);
GO
 
ALTER EVENT SESSION [deadlock_capture_persistent]
ON SERVER
STATE = START;
GO

Deterministic Deadlock Reproduction

A reproducible deadlock is the fastest way to validate detection queries, retry policies, and Extended Events capture. The demo below builds a deterministic two-table cycle, confirms the victim error, and inspects the surviving row state.

SQL Server | T-SQL | Two-table deadlock demo

The following demo creates a deterministic two-table deadlock by updating the same two tables in opposite order. Each cell is a separate step to be executed in its own SSMS window or session.

Set up the demo tables and seed data

USE stoxx;
GO
 
IF OBJECT_ID('dbo.deadlock_demo_a', 'U') IS NOT NULL
    DROP TABLE dbo.deadlock_demo_a;
IF OBJECT_ID('dbo.deadlock_demo_b', 'U') IS NOT NULL
    DROP TABLE dbo.deadlock_demo_b;
GO
 
CREATE TABLE dbo.deadlock_demo_a
(
    id int NOT NULL PRIMARY KEY,
    payload int NOT NULL
);
 
CREATE TABLE dbo.deadlock_demo_b
(
    id int NOT NULL PRIMARY KEY,
    payload int NOT NULL
);
GO
 
INSERT INTO dbo.deadlock_demo_a(id, payload) VALUES (1, 10);
INSERT INTO dbo.deadlock_demo_b(id, payload) VALUES (1, 20);
GO

Run Session 1 (update A, then B)

USE stoxx;
GO
 
SET DEADLOCK_PRIORITY LOW;
 
BEGIN TRAN;
 
UPDATE dbo.deadlock_demo_a
SET payload = payload + 1
WHERE id = 1;
 
WAITFOR DELAY '00:00:05';
 
UPDATE dbo.deadlock_demo_b
SET payload = payload + 1
WHERE id = 1;
 
COMMIT TRAN;
GO

Run Session 2 (update B, then A)

USE stoxx;
GO
 
BEGIN TRAN;
 
UPDATE dbo.deadlock_demo_b
SET payload = payload + 1
WHERE id = 1;
 
WAITFOR DELAY '00:00:05';
 
UPDATE dbo.deadlock_demo_a
SET payload = payload + 1
WHERE id = 1;
 
COMMIT TRAN;
GO

Clean up the demo tables

USE stoxx;
GO
 
DROP TABLE IF EXISTS dbo.deadlock_demo_a;
DROP TABLE IF EXISTS dbo.deadlock_demo_b;
GO

SQL Server | T-SQL | Victim outcome verification

Once the demo deadlock has fired, two things must be confirmed: the victim session received error 1205, and the surviving session’s changes actually committed.

Deadlock victim error 1205

The deadlock victim gets error 1205. The surviving session completes and commits its changes.

sourcemessage_numbermessage_text
Session 11205Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Error 1205 is the normal deadlock-victim signal. SQL Server has already rolled back the victim transaction. The application should not treat this as an unknown failure; it should treat it as a retry candidate after validating idempotency.

ColumnValueWatchMeaningImplication
message_number1205❌Deadlock victim error.The transaction was rolled back by SQL Server and may need a retry.
message_numberOther runtime errorDependsDifferent failure mode.Use the appropriate error-handling path; do not assume deadlock semantics.

Inspect surviving row state after rollback

After the victim rolls back, the surviving transaction’s change is still visible in the target table. Querying both tables confirms which side committed.

SELECT 'deadlock_demo_a' AS table_name, id, payload FROM dbo.deadlock_demo_a
UNION ALL
SELECT 'deadlock_demo_b' AS table_name, id, payload FROM dbo.deadlock_demo_b
ORDER BY table_name, id;
table_nameidpayload
deadlock_demo_a111
deadlock_demo_b121

Only the surviving transaction committed. Each table increased by 1, not by 2, which is exactly what you expect when one transaction becomes the deadlock victim and rolls back fully.

Deadlock Prevention Strategies

Prevention falls into three families: lock-order discipline at the transaction boundary, isolation-level changes that remove reader/writer conflict, and targeted use of DEADLOCK_PRIORITY when one workload should always lose.

SQL Server | Transactions | Lock-order discipline

Most production deadlocks come from code paths touching the same objects in different orders, or from plan shapes that widen the locking footprint. These three patterns attack the problem at its source.

Enforce a consistent access order

If every transaction touches tables or indexes in the same order, the most common two-object deadlock disappears.

  • Bad pattern: one code path updates OrderHeader then OrderLine, while another updates OrderLine then OrderHeader.
  • Better pattern: all code paths acquire locks in the same object order and with the same lookup shape.

Narrow the access path

Many deadlocks are really plan problems in disguise. Broad scans, key lookups, and non-SARGable predicates expand the lock footprint and increase the chance of conflicting lock order.

  • Add or fix supporting indexes.
  • Remove unnecessary lookups when they widen the locking pattern.
  • Revisit parameter-sensitive plans if the deadlock happens only for some parameter values.

Keep transactions short

The longer a transaction stays open, the larger the window for a cycle to form.

  • Do not wait for user input inside a transaction.
  • Do not perform remote calls inside a transaction unless they are unavoidable.
  • Stage data first, then open the transaction only for the final mutation.

SQL Server | Isolation | Row versioning for reader/writer cycles

Row versioning changes what a reader has to acquire. It cannot break writer-versus-writer cycles, but it often eliminates the reader-versus-writer half of a cycle entirely.

Use READ COMMITTED SNAPSHOT or SNAPSHOT to break reader/writer cycles

READ COMMITTED SNAPSHOT and SNAPSHOT do not fix writer-versus-writer deadlocks, but they often remove reader-versus-writer cycles caused by shared locks.

  • If the deadlock involves only writers, row versioning is not enough.
  • If one side is a long reader and the other is a writer, row versioning can remove that half of the cycle.

SQL Server | T-SQL | DEADLOCK_PRIORITY

When cycles cannot be fully eliminated, making a specific workload the designated victim turns random production pain into a predictable, retryable outcome.

Lower deadlock priority for background workloads

Sometimes the right fix is not “make deadlocks impossible”; it is “make the least important session lose predictably”.

Success

Lower the deadlock priority for background work such as cache refreshes, ETL backfills, or report warmups when those workloads can safely retry.

SET DEADLOCK_PRIORITY LOW;
GO

Application Retry Policy

Deadlocks are a normal part of a concurrent workload, so well-behaved applications must be prepared to retry them — but only when the unit of work is safe to replay.

C# | ADO.NET | Deadlock-safe retry helper

A dedicated retry helper keeps deadlock handling in one place, uses a bounded attempt count, and refuses to swallow anything other than error 1205.

Retry SqlException 1205 with bounded backoff

Applications should retry 1205 only when the operation is safe to replay.

Warning

Deadlock retry logic is correct only for idempotent or safely replayable units of work. Never wrap a non-idempotent side effect in blind retries.

public static async Task<T> ExecuteWithDeadlockRetryAsync<T>(
    Func<Task<T>> operation,
    int maxRetries = 3,
    int baseDelayMs = 250)
{
    for (var attempt = 1; ; attempt++)
    {
        try
        {
            return await operation();
        }
        catch (SqlException ex) when (ex.Number == 1205 && attempt <= maxRetries)
        {
            await Task.Delay(baseDelayMs * attempt);
        }
    }
}

SQL Server Deadlock Detection and Prevention Recommendations

  • Treat the deadlock graph as the source of truth. Do not guess from waits alone when a graph already exists.
  • Fix access order first when the graph spans multiple tables or indexes.
  • Fix access paths when the graph shows broad scans, hot keys, or unexpected objects.
  • Lower deadlock priority for background work only when retries are cheap and safe.
  • Keep a dedicated deadlock capture session on systems where rollover of system_health is not enough.

Graph View

Backlinks