sql server hub / deadlock guide

Deadlock on SQL Server

A deadlock on SQL Server happens when two or more tasks form a circular dependency. Each task holds a resource that another task needs. SQL Server breaks the cycle by rolling one transaction back and returning error 1205. Start with the saved deadlock graph because it shows the statements, owners, waiters, and resources in the cycle.

Guide~19 min readUpdated 27 Jul 2026

LinkedIn X Email

01How to find deadlocks in SQL Server
02How to read a SQL Server deadlock graph
03SQL Server deadlock troubleshooting: trace the full cycle
04What causes a deadlock on SQL Server?
05Common SQL Server deadlock examples
06Why a SELECT can be part of a SQL Server deadlock
07How to resolve a deadlock in SQL Server
08How to retry SQL Server error 1205 safely
09How to detect and alert on SQL Server deadlocks
10How to avoid deadlocks in SQL Server
11Microsoft documentation for SQL Server deadlocks
12SQL Server deadlock FAQ

How to find deadlocks in SQL Server

On SQL Server and SQL Managed Instance, check the built-in system_health Extended Events session first. It captures xml_deadlock_report events by default, so the graph may already be there even when nobody set up a separate deadlock monitor.

The ring buffer is the quickest place to look. The event file usually keeps more history because it can retain several rollover files. Both targets have limits, so an older graph may already be gone on a busy instance. Add a dedicated Extended Events session when the normal retention window is shorter than your response time.

These queries are read-only. Access to the server-scoped Extended Events views normally requires VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Azure SQL Database uses database-scoped Extended Events and does not provide the same built-in system_health session.

SQL Server deadlock detection query for system_health

Reads xml_deadlock_report events from the system_health ring buffer.

SQL

How to read deadlocks from system_health ring buffer

T-SQL · 15 lines

SELECT    xdr.value('@timestamp', 'datetime2') AS deadlock_time,    xdr.query('(data/value/deadlock)[1]') AS deadlock_xmlFROM (    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 rbCROSS APPLY rb.target_data.nodes(    'RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)ORDER BY deadlock_time DESC;

Review before runningT-SQLUTF-815 lines

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 and confirm the application connected to this instance.

Check the system_health event file when the ring buffer is empty

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

SQL

How to read deadlocks from system_health event files

T-SQL · 11 lines

SELECT    xed.timestamp_utc,    CAST(xed.event_data AS xml).query('(event/data/value/deadlock)[1]') AS deadlock_xmlFROM sys.fn_xe_file_target_read_file(    N'system_health*.xel',    NULL,    NULL,    NULL) AS xedWHERE xed.object_name = N'xml_deadlock_report'ORDER BY xed.timestamp_utc DESC;

Review before runningT-SQLUTF-811 lines

The wildcard reads every retained system_health rollover file that SQL Server can find in the default log folder. timestamp_utc is available in SQL Server 2017 and later. On older versions, use the event timestamp in the XML or open the files in SSMS. Keep the returned XML together with its timestamp for diagnosis. Azure SQL Database uses a database-scoped session with an Azure Storage event_file target. It does not have the built-in system_health session.

Keep a dedicated deadlock event file when incidents are missed

The script below creates a server-scoped session for SQL Server and starts it automatically with the instance. Change the path, retention, and file permissions before running it. Azure SQL Database and SQL Managed Instance need an Azure Storage event-file design instead of this local path.

Creates a server-level Extended Events session with a bounded event_file target.

SQL

Create a dedicated SQL Server deadlock capture

T-SQL · 26 lines

-- Change this path before running the script.-- The SQL Server service account needs write access to the folder.CREATE EVENT SESSION [deadlock_capture]ON SERVERADD EVENT sqlserver.xml_deadlock_reportADD TARGET package0.event_file(    SET        filename = N'D:SqlXEventsdeadlock_capture.xel',        max_file_size = 50,        max_rollover_files = 8)WITH(    MAX_MEMORY = 4096 KB,    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,    MAX_DISPATCH_LATENCY = 5 SECONDS,    TRACK_CAUSALITY = OFF,    STARTUP_STATE = ON);GO ALTER EVENT SESSION [deadlock_capture]ON SERVERSTATE = START;GO

Review before runningT-SQLUTF-826 lines

Run this once after reviewing the path and retention with the server owner. The folder must already exist and the SQL Server service account needs write access. The eight rollover files can contain SQL text, host names, login names, and application names. Restrict file access and set retention to match the incident process. Creating or starting a server event session needs the corresponding Extended Events permission.

How to read a SQL Server deadlock graph

Read the graph as a loop. The victim list identifies the transaction SQL Server rolled back. The process list shows each task and its executing statement. The resource list shows which process owned a resource and which process waited for it.

Start with the victim process id and find that id in the process list. Follow its requested resource to the process that owns it. Then find what the owner is waiting for. Continue until the edge comes back to the first process. Write the loop down in plain language before changing any code.

A deadlock XML loop in plain language

This sanitized graph keeps the important structure from an xml_deadlock_report. Process A owns the SalesOrder key and waits for the Invoice key. Process B owns the Invoice key and waits for the SalesOrder key. SQL Server chooses Process A as the victim.

Use executionStack/frame for the statement that was running. Use inputbuf to identify the submitted procedure call or batch.

XDL

Sanitized SQL Server deadlock graph

XML · 39 lines

<deadlock>  <victim-list>    <victimProcess id="processA" />  </victim-list>  <process-list>    <process id="processA" spid="57" clientapp="Order API"             isolationlevel="read committed (2)"             waitresource="KEY: 7:720575940...">      <executionStack>        <frame procname="dbo.PostOrder" line="18">          UPDATE dbo.Invoice SET InvoiceStatus = N'Ready'          WHERE InvoiceID = 9001;        </frame>      </executionStack>      <inputbuf>EXEC dbo.PostOrder @OrderID = 42;</inputbuf>    </process>    <process id="processB" spid="64" clientapp="Billing worker"             isolationlevel="read committed (2)"             waitresource="KEY: 7:720575941...">      <executionStack>        <frame procname="dbo.PostInvoice" line="22">          UPDATE dbo.SalesOrder SET Status = N'Posted'          WHERE SalesOrderID = 42;        </frame>      </executionStack>      <inputbuf>EXEC dbo.PostInvoice @InvoiceID = 9001;</inputbuf>    </process>  </process-list>  <resource-list>    <keylock objectname="Sales.dbo.SalesOrder" indexname="PK_SalesOrder">      <owner-list><owner id="processA" mode="X" /></owner-list>      <waiter-list><waiter id="processB" mode="X" requestType="wait" /></waiter-list>    </keylock>    <keylock objectname="Sales.dbo.Invoice" indexname="PK_Invoice">      <owner-list><owner id="processB" mode="X" /></owner-list>      <waiter-list><waiter id="processA" mode="X" requestType="wait" /></waiter-list>    </keylock>  </resource-list></deadlock>

Sanitized exampleXMLUTF-839 lines

Open the graphical deadlock view in SSMS

Run one of the capture queries and select the deadlock_xml cell in the SSMS results grid.

Save the XML document with an .xdl extension. Keep the original UTC event time in the filename or incident notes.

Close the XML tab, then open the .xdl file in SSMS to display the graphical deadlock view.

Return to the XML for executionStack, inputbuf, owner-list, waiter-list, and fields that the picture does not show clearly.

Deadlock graph fields that usually matter

XML area	What to read
victim-list	The process SQL Server selected for rollback. Match this id to process-list.
executionStack	The frame, procedure, line, and statement that were executing when the task waited.
inputbuf	The submitted procedure call or batch. This can be broader than the executing frame.
process attributes	The spid, application, host, login, isolation level, transaction count, wait resource, lock mode, and log used.
resource-list	Every resource in the cycle, including the owner-list and waiter-list for each resource.
UnderlyingResource	The key or object behind an xactlock resource when optimized locking is enabled.

Resource node	Where it points the investigation
keylock, pagelock, ridlock, objectlock	Object access order, transaction scope, query plan, and the rows or pages touched.
xactlock	A transaction ID lock used by optimized locking. Read its nested UnderlyingResource element.
exchangeEvent	A parallel plan. Identify every process and resource that completes the cycle before changing MAXDOP.
threadpool	Worker thread pressure combined with tasks that hold resources while waiting for a worker.
metadata	Concurrent metadata or schema work.
applicationlock	Application locks requested through sp_getapplock. Compare lock names and order.

Query the victim, process, and resource nodes

Extracts victim process IDs from recent deadlock XML.

SQL

How to find the deadlock victim

T-SQL · 22 lines

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_idFROM Deadlocks AS dCROSS APPLY d.deadlock_xml.nodes('/deadlock/victim-list/victimProcess') AS V(victim)ORDER BY d.deadlock_time DESC;

Review before runningT-SQLUTF-822 lines

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.

Extracts the executing frame, outer input buffer, session details, isolation level, wait resource, and transaction data from process-list.

SQL

How to extract process details

T-SQL · 43 lines

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,    frame.value('@procname', 'nvarchar(512)') AS procedure_name,    TRY_CONVERT(int, frame.value('@line', 'nvarchar(20)')) AS line_number,    frame.value('@sqlhandle', 'varchar(130)') AS sql_handle,    NULLIF(        LTRIM(RTRIM(frame.value('(text())[1]', 'nvarchar(max)'))),        N''    ) AS executing_statement,    p.value('(inputbuf/text())[1]', 'nvarchar(max)') AS input_bufferFROM Deadlocks AS dCROSS APPLY d.deadlock_xml.nodes('/deadlock/process-list/process') AS P(p)OUTER APPLY p.nodes('executionStack/frame') AS F(frame)ORDER BY d.deadlock_time DESC, spid, line_number;

Review before runningT-SQLUTF-843 lines

executing_statement, procedure_name, and line_number point to the code that was running at the wait. input_buffer identifies the outer submitted batch or procedure call and can be less specific. 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.

Returns owners and waiters as separate rows and reads the nested resource behind SQL Server 2025 xactlock nodes.

SQL

How to extract resource details

T-SQL · 74 lines

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),Resources AS (    SELECT        d.deadlock_time,        r.query('.') AS resource_xml,        r.value('local-name(.)', 'nvarchar(80)') AS resource_type,        NULLIF(            r.value('local-name((UnderlyingResource/*)[1])', 'nvarchar(80)'),            N''        ) AS underlying_resource_type,        COALESCE(            NULLIF(r.value('@objectname', 'nvarchar(512)'), N''),            NULLIF(                r.value('(UnderlyingResource/*/@objectname)[1]', 'nvarchar(512)'),                N''            )        ) AS object_name,        COALESCE(            NULLIF(r.value('@indexname', 'nvarchar(512)'), N''),            NULLIF(                r.value('(UnderlyingResource/*/@indexname)[1]', 'nvarchar(512)'),                N''            )        ) AS index_name,        r.value('@mode', 'nvarchar(20)') AS resource_mode    FROM Deadlocks AS d    CROSS APPLY d.deadlock_xml.nodes('/deadlock/resource-list/*') AS R(r))SELECT    r.deadlock_time,    r.resource_type,    r.underlying_resource_type,    r.object_name,    r.index_name,    r.resource_mode,    N'owner' AS participant_role,    owner.value('@id', 'nvarchar(100)') AS process_id,    owner.value('@mode', 'nvarchar(20)') AS lock_mode,    CAST(NULL AS nvarchar(60)) AS request_typeFROM Resources AS rCROSS APPLY r.resource_xml.nodes('/*/owner-list/owner') AS O(owner) UNION ALL SELECT    r.deadlock_time,    r.resource_type,    r.underlying_resource_type,    r.object_name,    r.index_name,    r.resource_mode,    N'waiter' AS participant_role,    waiter.value('@id', 'nvarchar(100)') AS process_id,    waiter.value('@mode', 'nvarchar(20)') AS lock_mode,    waiter.value('@requestType', 'nvarchar(60)') AS request_typeFROM Resources AS rCROSS APPLY r.resource_xml.nodes('/*/waiter-list/waiter') AS W(waiter)ORDER BY deadlock_time DESC, resource_type, object_name, participant_role;

Review before runningT-SQLUTF-874 lines

participant_role keeps owners and waiters separate instead of creating false owner-by-waiter pairs. lock_mode shows the held or requested mode for that participant. object_name and index_name are not always present, but they are very useful when available. For xactlock, underlying_resource_type, object_name, and index_name come from UnderlyingResource.

SQL Server deadlock troubleshooting: trace the full cycle

Error 1205 names the victim and little else. Save the complete XML with the UTC event time and the matching application error. Add the application action, SQL Agent job, release, or workload window that was active at the same time.

The captured statement is the point where a task waited. Read the complete transaction path around it, including earlier statements that took the locks shown in the owner lists. That is where opposite access order and long transaction scope usually become clear.

Match the victim id to process-list, then record every executing frame, application, database, and isolation level.

Follow each waiter to the process that owns the requested resource.

Continue through owner and waiter links until the path returns to the first process.

Map every statement to the stored procedure, API action, job step, queue worker, or report that opened the transaction.

Change one part of the cycle, test the competing actions together, and keep the capture running.

Graph clue	First check	Likely fix area
The same objects appear in opposite order	Compare complete transaction paths, including statements before the captured frame.	Use one object access order and shorten the transaction.
A keylock covers a hot row or range	Inspect the actual plan, predicates, estimates, and Query Store history.	Change the access path, batch size, or plan only after workload testing.
A reader participates under SERIALIZABLE	Check why range protection is needed and how long the transaction stays open.	Change transaction scope or test an appropriate row-versioning design.
exchangeEvent appears	Inspect every process in the cycle and the parallel plan.	Correct the plan or resource pressure first. Change MAXDOP only with a plan-specific reason.
applicationlock appears	Compare sp_getapplock resource names and request order.	Use one application-lock order across every code path.

What causes a deadlock on SQL Server?

A common deadlock pattern starts when two transactions take locks in a different order. Each task owns a row, key, page, object, or application lock that another task needs. The second task owns a resource needed by the first, which closes the loop.

Deadlocks can also involve worker threads, memory, parallel exchange resources, metadata, or session and transaction mutexes. SQL Server's deadlock monitor finds the cycle, chooses a victim, rolls back that transaction, and returns error 1205. Victim selection uses deadlock priority and rollback cost.

Long transactions, wide scans, key-range locks, foreign key checks, parallel plans, and jobs that overlap with user traffic make cycles easier to form. Ordinary blocking follows a wait chain that can clear when the blocker finishes. Use the SQL Server blocking guide when no deadlock graph or victim exists.

Symptom	What it means	What to check
Error 1205	SQL Server selected this transaction as the victim and rolled it back.	The complete deadlock XML and the application operation that failed.
Requests wait with no victim	A blocking chain is still active and might clear when the head blocker finishes.	Live sessions, open transactions, current requests, waits, and locks.
Intermittent rollback with little application detail	The application caught or hid error 1205.	system_health, a dedicated Extended Events session, and application logs.

Common SQL Server deadlock examples

The statement in the graph is only the point where the session was waiting. The transaction may have taken several locks before that statement ran. Check the stored procedure, API endpoint, job step, report, or queue worker that opened the transaction.

The test pairs below create four clear cycles: opposite update order, a serializable range read against a write, a foreign key lookup, and application locks requested in the wrong order. Run them only in a disposable database.

Map executionStack, inputbuf, clientapp, hostname, loginname, and the event time to the application action or job step.

Read the complete transaction around each captured statement, including earlier reads and writes.

Compare object access order, isolation level, batch size, execution plan, and workload overlap.

Group repeated graphs by the same processes and resources before deciding that a pattern is fixed.

Four SQL Server deadlock cycles you can test

Open Session A and Session B in separate SSMS query windows, connect both to the same disposable database, and start them close together. The delays are there to make the lock order visible. Object names, timing, and plans still affect the exact graph.

Opposite update order

Two workflows update the same rows in a different order. Run both batches at the same time in a disposable test database. The delay gives each session time to secure its first lock.

SQL

Session A

T-SQL · 13 lines

BEGIN TRAN; UPDATE dbo.SalesOrderSET Status = N'Invoicing'WHERE SalesOrderID = 42; WAITFOR DELAY '00:00:05'; UPDATE dbo.InvoiceSET InvoiceStatus = N'Ready'WHERE InvoiceID = 9001; COMMIT;

Review before runningT-SQLUTF-813 lines

SQL

Session B

T-SQL · 13 lines

BEGIN TRAN; UPDATE dbo.InvoiceSET InvoiceStatus = N'Posting'WHERE InvoiceID = 9001; WAITFOR DELAY '00:00:05'; UPDATE dbo.SalesOrderSET Status = N'Posted'WHERE SalesOrderID = 42; COMMIT;

Review before runningT-SQLUTF-813 lines

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

Session A protects an invoice range and then requests the summary row. Session B owns the summary row and then tries to insert into the protected range. This creates the missing circular wait.

Run this once in a disposable test database, then open two query windows for Session A and Session B.

SQL

Range read collides with a write test setup

T-SQL · 24 lines

CREATE TABLE dbo.DeadlockCustomerSummaryDemo(    CustomerID int NOT NULL        CONSTRAINT PK_DeadlockCustomerSummaryDemo PRIMARY KEY,    OpenInvoiceCount int NOT NULL,    OpenInvoiceAmount decimal(12, 2) NOT NULL); CREATE TABLE dbo.DeadlockInvoiceDemo(    InvoiceID int IDENTITY(1, 1) NOT NULL        CONSTRAINT PK_DeadlockInvoiceDemo PRIMARY KEY,    CustomerID int NOT NULL,    InvoiceStatus nvarchar(20) NOT NULL,    Amount decimal(12, 2) NOT NULL); CREATE INDEX IX_DeadlockInvoiceDemo_Customer_StatusON dbo.DeadlockInvoiceDemo (CustomerID, InvoiceStatus); INSERT dbo.DeadlockCustomerSummaryDemo    (CustomerID, OpenInvoiceCount, OpenInvoiceAmount)VALUES    (42, 0, 0.00);

Review before runningT-SQLUTF-824 lines

SQL

Session A

T-SQL · 15 lines

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRAN; SELECT InvoiceIDFROM dbo.DeadlockInvoiceDemoWHERE CustomerID = 42  AND InvoiceStatus = N'Unpaid'; WAITFOR DELAY '00:00:05'; UPDATE dbo.DeadlockCustomerSummaryDemoSET OpenInvoiceCount = OpenInvoiceCount + 1WHERE CustomerID = 42; COMMIT;

Review before runningT-SQLUTF-815 lines

SQL

Session B

T-SQL · 12 lines

BEGIN TRAN; UPDATE dbo.DeadlockCustomerSummaryDemoSET OpenInvoiceAmount = OpenInvoiceAmount + 149.00WHERE CustomerID = 42; WAITFOR DELAY '00:00:05'; INSERT dbo.DeadlockInvoiceDemo (CustomerID, InvoiceStatus, Amount)VALUES (42, N'Unpaid', 149.00); COMMIT;

Review before runningT-SQLUTF-812 lines

What to look for in XML: Look for SERIALIZABLE on Session A, a key-range lock on IX_DeadlockInvoiceDemo_Customer_Status, and an owner/waiter loop through the CustomerSummary row.

Foreign key lookup deadlock

Session A owns the parent customer row and requests the order-line row. Session B owns the order-line row and changes OrderHeader.CustomerID from 43 to 42, which makes the foreign key check read the parent row.

Run this once in a disposable test database, then open two query windows for Session A and Session B.

SQL

Foreign key lookup deadlock test setup

T-SQL · 36 lines

CREATE TABLE dbo.DeadlockCustomerDemo(    CustomerID int NOT NULL        CONSTRAINT PK_DeadlockCustomerDemo PRIMARY KEY,    AccountStatus nvarchar(20) NOT NULL); CREATE TABLE dbo.DeadlockOrderHeaderDemo(    OrderID int NOT NULL        CONSTRAINT PK_DeadlockOrderHeaderDemo PRIMARY KEY,    CustomerID int NOT NULL,    CONSTRAINT FK_DeadlockOrderHeaderDemo_Customer        FOREIGN KEY (CustomerID)        REFERENCES dbo.DeadlockCustomerDemo (CustomerID)); CREATE TABLE dbo.DeadlockOrderLineDemo(    OrderLineID int NOT NULL        CONSTRAINT PK_DeadlockOrderLineDemo PRIMARY KEY,    OrderID int NOT NULL,    Quantity int NOT NULL,    CONSTRAINT FK_DeadlockOrderLineDemo_OrderHeader        FOREIGN KEY (OrderID)        REFERENCES dbo.DeadlockOrderHeaderDemo (OrderID)); INSERT dbo.DeadlockCustomerDemo (CustomerID, AccountStatus)VALUES (42, N'Active'), (43, N'Active'); INSERT dbo.DeadlockOrderHeaderDemo (OrderID, CustomerID)VALUES (5005, 43); INSERT dbo.DeadlockOrderLineDemo (OrderLineID, OrderID, Quantity)VALUES (7007, 5005, 1);

Review before runningT-SQLUTF-836 lines

SQL

Session A

T-SQL · 13 lines

BEGIN TRAN; UPDATE dbo.DeadlockCustomerDemoSET AccountStatus = N'Review'WHERE CustomerID = 42; WAITFOR DELAY '00:00:05'; UPDATE dbo.DeadlockOrderLineDemoSET Quantity = Quantity + 1WHERE OrderLineID = 7007; COMMIT;

Review before runningT-SQLUTF-813 lines

SQL

Session B

T-SQL · 13 lines

BEGIN TRAN; UPDATE dbo.DeadlockOrderLineDemoSET Quantity = Quantity + 2WHERE OrderLineID = 7007; WAITFOR DELAY '00:00:05'; UPDATE dbo.DeadlockOrderHeaderDemoSET CustomerID = 42WHERE OrderID = 5005; COMMIT;

Review before runningT-SQLUTF-813 lines

What to look for in XML: Match the Customer primary-key resource to the foreign key check and the OrderLine primary-key resource to the earlier update. The cycle depends on the parent lookup, not on a guessed missing index.

Application lock order deadlock

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

SQL

Session A

T-SQL · 15 lines

BEGIN TRAN; EXEC sys.sp_getapplock    @Resource = N'Customer:42',    @LockMode = N'Exclusive',    @LockOwner = N'Transaction'; WAITFOR DELAY '00:00:05'; EXEC sys.sp_getapplock    @Resource = N'Invoice:42',    @LockMode = N'Exclusive',    @LockOwner = N'Transaction'; COMMIT;

Review before runningT-SQLUTF-815 lines

SQL

Session B

T-SQL · 15 lines

BEGIN TRAN; EXEC sys.sp_getapplock    @Resource = N'Invoice:42',    @LockMode = N'Exclusive',    @LockOwner = N'Transaction'; WAITFOR DELAY '00:00:05'; EXEC sys.sp_getapplock    @Resource = N'Customer:42',    @LockMode = N'Exclusive',    @LockOwner = N'Transaction'; COMMIT;

Review before runningT-SQLUTF-815 lines

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

Treat exchangeEvent as a graph-recognition pattern

A single parallel SELECT is not a useful deadlock reproduction by itself. When exchangeEvent appears, use the captured graph to identify every execution context and any lock, worker, or other process that completes the cycle. Then inspect the actual parallel plan, row estimates, exchange operators, memory grant, and worker pressure.

Graph detail	What to check
Several process nodes share one spid and have different ecid values	These are execution contexts from the same parallel request. Match each one to its waiter and owner resource.
exchangeEvent appears with keylock, pagelock, or objectlock	Find the outside transaction or worker that completes the cycle. Read the full transaction around it.
exchangeEvent appears with threadpool	Check worker use, runnable work, concurrency, and the plans holding resources while waiting for workers.
The plan changed before the incident	Compare the good and bad plans, estimates, memory grant, degree of parallelism, and runtime shape before changing MAXDOP.

Why a SELECT can be part of a SQL Server deadlock

A SELECT can hold shared or key-range locks while it waits for a resource owned by a writer. This often happens when the read sits inside a longer transaction, uses REPEATABLE READ or SERIALIZABLE, scans a wide range, or reaches tables in a different order from the write path.

When the SELECT is the victim, check the locks it already owns, the isolation level, the actual plan, and the statements that ran earlier in the same transaction. A parallel SELECT can also appear in an exchangeEvent deadlock. That pattern needs plan analysis because lock hints can easily move the problem elsewhere.

Check whether the SELECT is inside an explicit transaction and what ran before it.

Confirm the isolation level and whether locking hints request longer or wider protection.

Inspect the actual plan for scans, poor estimates, lookups, parallel exchanges, and the objects reached before the wait.

Evaluate READ_COMMITTED_SNAPSHOT only when row-versioned read semantics and tempdb impact are acceptable.

Keep write-write, schema, and application-lock deadlocks in scope because row versioning will not remove them.

How to resolve a deadlock in SQL Server

The right fix removes one edge from the cycle or makes the collision window much smaller. In an opposite-order deadlock, make both code paths touch shared objects in the same order. In a long-transaction deadlock, open the transaction closer to the work and finish it sooner.

A query or index change can help when it reduces the rows touched and the time locks are held. Check the actual execution plan and Query Store history before adding an index. A graph names the objects in the cycle; it cannot tell you whether a new index is safe for the rest of the workload.

Cause in the graph	Likely fix	How to check it
Objects are locked in opposite order	Use one object access order in every transaction path.	Run the competing actions together and check whether the same graph returns.
The transaction stays open too long	Move remote calls and unrelated work outside the transaction.	Measure transaction duration and test every commit, error, and cancellation path.
A query scans more rows than expected	Fix the predicate, plan regression, estimates, or supporting index.	Compare actual plans, reads, duration, and write cost under realistic load.
A batch or SQL Agent job overlaps with live work	Use safe smaller batches or change the schedule.	Test the job with normal user traffic and retain the deadlock capture.
Readers and writers form the cycle	Test whether row-versioned reads fit the application and tempdb capacity.	Check result semantics, version-store use, and the write-write deadlocks that remain.

SQL Server 2025 optimized locking can reduce lock memory, lock escalation, and some deadlock patterns. It is off by default on SQL Server 2025 and needs accelerated database recovery. Its lock-after-qualification behavior also needs read committed snapshot isolation. An optimized-locking graph can contain xactlock nodes with a nested UnderlyingResource, which the resource query above reads. Review Microsoft's optimized locking documentation before treating it as a database change.

How to retry SQL Server error 1205 safely

SQL Server rolls the victim transaction back, so the application can often try the operation again. Start a new transaction and repeat the complete unit of work. Retrying only the statement that received error 1205 skips earlier statements that were rolled back with it.

Use a short delay, a strict attempt limit, and a log entry for every retry. A little random jitter stops competing requests from immediately lining up in the same order. Confirm that the operation can be repeated without duplicating a payment, message, email, or other external action.

Retry code handles an occasional collision. Repeated graphs with the same resources need a database or application fix. An immediate unlimited loop adds load and can turn a small concurrency problem into a larger one.

Catch SQL Server error number 1205 at the transaction boundary.

Roll back or dispose the failed transaction before starting a new attempt.

Retry the complete unit of work with bounded attempts, a short delay, and jitter.

Record the operation, attempt number, correlation id, SQL error, and final outcome.

Escalate repeated deadlocks instead of hiding them behind successful retries.

How to detect and alert on SQL Server deadlocks

SQL Server's deadlock monitor detects the cycle and raises an xml_deadlock_report event. For recurring incidents, copy that event to a place where it will still exist when someone investigates. Include the event time, instance, database, victim, application names, and original XML in the alert.

Set the alert severity from the effect on users and how often the same graph appears. Group matching graphs so one noisy workload does not page someone for every victim. Keep the raw XML because summaries often drop the owner and waiter details needed to fix the cycle.

Monitoring need	Useful capture	Operational note
Fast incident notification	xml_deadlock_report with instance, database, UTC time, and victim details.	Route by production impact and repetition.
Pattern analysis	Original XML plus a stable fingerprint of processes and resources.	Group the same cycle and compare frequency before and after a fix.
Application correlation	Error 1205 log with operation and correlation id.	Use the same time basis as the database event.
Longer retention	Dedicated Extended Events event_file target or monitoring history.	Review storage, rollover, permissions, and sensitive query text.

Live blocking data answers a different question. It can show the workload around the same incident, including sleeping sessions with open transactions, but it cannot rebuild a deadlock that has finished. Use the saved XML for the cycle and the SQL Server blocking guide for the current head blocker and wait chain.

How to avoid deadlocks in SQL Server

Use the same object order in competing transactions, keep transactions short, and make queries touch as few rows as the work allows. Schedule large jobs away from busy OLTP periods when possible, and keep deadlock capture running after a fix so you can see whether the same cycle returns.

Use one object and application-lock order across every code path.

Open the transaction immediately before the work that must commit together.

Commit or roll back on success, error, timeout, and cancellation paths.

Tune scans and plan regressions that hold locks across more rows or for longer than expected.

Test batch size, job timing, and row-versioning changes with the real concurrent workload.

Deadlock fixes that can make the problem worse

A quick setting or lock hint can hide one graph and create another concurrency problem. Use the captured cycle to justify the change, then test it under load.

NOLOCK allows inconsistent reads and does not remove write-write, schema, application-lock, or parallel deadlocks.

DEADLOCK_PRIORITY changes victim selection. It does not break the cycle.

ROWLOCK, UPDLOCK, TABLOCK, and isolation changes need a graph-specific design reason and concurrency testing.

Killing the victim or rewriting only its statement ignores what the other processes owned.

SQL Profiler and SQL Trace are legacy capture choices. Use Extended Events for current deadlock work.

An index from one graph needs plan, workload, and write-cost validation before deployment.

An unlimited retry loop hides the repeated graph, adds load, and can repeat external business effects.

Microsoft documentation for SQL Server deadlocks

These Microsoft pages cover deadlock behavior, Extended Events capture, error 1205, application retries, and transaction isolation. Check the page for the SQL Server or Azure service version you run.

SQL Server deadlocks guide

Deadlock detection, graph structure, handling, and prevention.

Use the system_health session

What the built-in session captures and how to inspect its data.

Read Extended Events files

Wildcard rollover reads, timestamp support, and version-specific permissions.

Extended Events event_file target

File size, rollover retention, storage, and sensitive-data guidance.

View Extended Events data in SSMS

Open XEL files, merge rollover files, and inspect target data.

Database Engine error 1205

Microsoft's deadlock victim guidance and retry note.

Handle deadlocks in application code

Why an application should retry the entire transaction.

Transaction isolation levels

Locking and row-versioning behavior that affects reader-writer conflicts.

Optimized locking

SQL Server 2025 prerequisites and xactlock deadlock graph fields.

SQL Server deadlock FAQ

What causes a deadlock in SQL Server?

A deadlock forms when two or more tasks create a cycle of dependencies. Each task holds a resource and waits for another resource held by a task in the same cycle. Common causes include inconsistent object access order, long transactions, wide scans, key-range locks, foreign key checks, parallel exchange operators, and application locks requested in different orders.

How do I find the latest deadlock in SQL Server?

Start with the system_health Extended Events session on SQL Server and SQL Managed Instance. Its xml_deadlock_report events normally contain recent deadlock graphs. Read the ring buffer for a quick check or the event file for better retention. Azure SQL Database does not provide the same built-in system_health session, so use a database-scoped Extended Events session or your monitoring history there.

Can a SELECT statement cause a SQL Server deadlock?

Yes. A SELECT can hold shared or key-range locks while waiting for another resource, especially inside an explicit transaction or under REPEATABLE READ or SERIALIZABLE. A SELECT can also participate in a parallel exchange deadlock. The graph shows whether the reader is part of the cycle and which resource it owns or requests.

Does SQL Server resolve deadlocks automatically?

SQL Server resolves the immediate cycle by choosing a victim, rolling its transaction back, and returning error 1205. That restores progress but does not remove the underlying concurrency pattern. Repeated deadlocks still need a graph-based fix.

Should an application retry SQL Server error 1205?

Usually, if the whole transaction is safe to repeat. Retry the complete unit of work with a small delay, a strict retry limit, and logging. Confirm that the operation is idempotent or otherwise protected from duplicate business effects. Repeated retries are a fallback, not a permanent deadlock fix.

What is the difference between SQL Server blocking and a deadlock?

Blocking is normally a one-way wait or a chain that can clear when the blocker finishes. A deadlock is a cycle that cannot make progress, so SQL Server must roll back one participant. Use live request and blocking-chain data for blocking. Use the saved deadlock XML graph for a deadlock.

When to get help with a deadlock on SQL Server

A performance review is useful when the same deadlock keeps returning, the graph is hard to connect to application code, or the fix crosses transaction flow, query plans, indexing, isolation, and retry handling.

Send the original XML, UTC event times, affected application actions, relevant plans, error 1205 logs, retry behavior, and recent releases or job changes. That is enough for a useful first pass.

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 waits guide for lock-wait context, the SQL Server slow performance guide for broad slowness, the SQL Server indexing guide for access-path fixes, and the SQL Server monitoring guide for earlier capture of recurrence.