How to find and fix deadlocks in SQL Server

Deadlock or blocking

Blocking is one session waiting for another to finish. It ends when the first session commits or rolls back. A deadlock is two or more sessions each waiting for a lock the other holds. None of them can ever finish, so SQL Server has to break the cycle.

• The lock monitor checks for deadlocks every 5 seconds, and more often (down to 100 ms) while it keeps finding them.
• It picks a victim: the session with the lowest DEADLOCK_PRIORITY, or, when priorities are equal, the one that is cheapest to roll back.
• The victim's batch is stopped, its transaction is rolled back, and the client gets error 1205: “Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

The other session carries on as if nothing happened. So the evidence is in two places: the 1205 errors in your application logs, and the deadlock report SQL Server writes every time.

1. Confirm they are happening

SQL Server counts deadlocks in a performance counter. Despite the name, the value in this view is a running total since the instance started, not a rate.

SELECT cntr_value AS deadlocks_since_startup
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Locks%'
  AND counter_name LIKE 'Number of Deadlocks/sec%'
  AND instance_name = '_Total';

Run it twice, a few minutes apart. The difference is how many deadlocks happened in between. A number that grows steadily is a pattern worth fixing; a single deadlock a week usually only needs a retry in the application.

2. Read recent deadlocks

You do not need to set anything up first. The built-in system_health Extended Events session is on by default and records an xml_deadlock_report event for every deadlock, to files in the instance's log folder.

SELECT CAST(x.timestamp_utc AS datetime2(0)) AS deadlock_time_utc,
       CAST(x.event_data AS xml).query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) AS x
WHERE x.object_name = 'xml_deadlock_report'
  AND CAST(x.timestamp_utc AS datetime2(7)) > DATEADD(DAY, -7, SYSUTCDATETIME())
ORDER BY deadlock_time_utc DESC;

• The timestamp_utc column exists from SQL Server 2017. On older versions, drop it and read the timestamp attribute from event_data instead.
• In SQL Server Management Studio, click a deadlock_graph value, save it with the .xdl extension and open it again to see the graph drawn.
• system_health files roll over. On a busy server the history may cover only hours or days, so save the graphs you need. Step 7 sets up a longer history.

3. Read the deadlock graph

Each report is an XML document with three parts:

• victim-list: the id of the process that was rolled back.
• process-list: one entry per session in the cycle, with its session id (spid), clientapp, hostname, loginname, isolationlevel, waitresource, the lock mode it asked for (lockMode), logused, the statement stack (executionStack) and the last batch it sent (inputbuf).
• resource-list: the locked objects, each with an owner-list (who holds it, in which mode) and a waiter-list (who wants it).

This query turns each report into one row per session, so you can see both sides at once:

WITH dl AS (
    SELECT CAST(x.timestamp_utc AS datetime2(0)) AS deadlock_time_utc,
           CAST(x.event_data AS xml).query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS graph
    FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) AS x
    WHERE x.object_name = 'xml_deadlock_report'
)
SELECT dl.deadlock_time_utc,
       CASE WHEN p.value('@id', 'varchar(50)')
                 = dl.graph.value('(deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)')
            THEN 'victim' ELSE 'survivor' END             AS outcome,
       p.value('@spid', 'int')                            AS spid,
       p.value('@clientapp', 'nvarchar(128)')             AS client_app,
       p.value('@hostname', 'nvarchar(128)')              AS host_name,
       p.value('@loginname', 'nvarchar(128)')             AS login_name,
       p.value('@isolationlevel', 'varchar(40)')          AS isolation_level,
       p.value('@waitresource', 'varchar(200)')           AS wait_resource,
       p.value('@lockMode', 'varchar(10)')                AS wanted_lock,
       p.value('@logused', 'bigint')                      AS log_bytes_used,
       LEFT(p.value('(inputbuf)[1]', 'nvarchar(max)'), 200) AS last_batch
FROM dl
CROSS APPLY dl.graph.nodes('deadlock/process-list/process') AS t(p)
ORDER BY dl.deadlock_time_utc DESC, outcome;

Read each deadlock as a pair of sentences: “Session A holds X and wants Y. Session B holds Y and wants X.” The resource-list gives you X and Y; wait_resource and wanted_lock give you what each side asked for.

To turn a wait_resource such as KEY: 5:72057594045071360 (8194443284a0) into a table and index, look up the middle number (the HoBT id) in the database named by the first number:

-- run in the database whose id is the first number (5 above)
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id)        AS table_name,
       i.name                          AS index_name
FROM sys.partitions AS p
JOIN sys.indexes AS i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.hobt_id = 72057594045071360;

4. Recognise the pattern

Most deadlocks are one of a few shapes. The graph tells you which.

• Opposite order. Two transactions update the same two tables or rows, but in a different order. Both sides hold an X lock and want the other's. Typical in code paths that were written separately, such as “transfer” and “refund”.
• Reader and writer on two indexes. A SELECT finds rows through a nonclustered index and then looks up the remaining columns in the clustered index. An UPDATE that changes a column in that nonclustered index locks the clustered index row first and then the nonclustered one. Each holds a lock on one index and wants the other. One side shows lockMode S, the other X, on two different indexes of the same table.
• Read, then update. Two sessions read the same row under REPEATABLE READ or SERIALIZABLE, keep their shared locks, and then both try to update it. Neither can convert its lock while the other still holds one.
• Range locks. Under SERIALIZABLE, key-range locks (RangeS-S, RangeI-N and similar) protect gaps between rows, so two inserts or upserts can deadlock even when they touch different rows. Some libraries default to SERIALIZABLE, for example TransactionScope in .NET; check isolation_level before assuming the code asked for it.

5. Fix the cause

Pick the fix that matches the pattern:

• Same order everywhere. Make every transaction touch shared tables and rows in the same order, for example always the lower account id first. This removes opposite-order deadlocks completely.
• Shorter transactions. Do the reads, calculations and calls to other services before BEGIN TRANSACTION, and never wait for user input inside one. Locks are held until commit or rollback.
• A covering index. For reader and writer deadlocks, an index that holds every column the SELECT needs removes the lookup into the clustered index, so the reader locks one index instead of two. Weigh the extra storage and write cost before adding it.
• The lowest isolation level that is correct. Avoid REPEATABLE READ and SERIALIZABLE unless the code relies on them.
• Row versioning. With read committed snapshot isolation (RCSI), readers see the last committed version of a row and do not take shared locks, which removes most reader and writer deadlocks.

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'Shop';

ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SQL Server 2025 adds optimized locking, which releases row and page locks as soon as each row is updated and, with RCSI on, avoids update locks. Both reduce deadlocks. It is off by default in SQL Server 2025, needs accelerated database recovery, and is always on in Azure SQL Database.

SELECT name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = 'Shop';

6. Retry the victim

Even a well-designed system has the occasional deadlock, so the code that can be chosen as a victim must handle error 1205. If it does not, the application may carry on without knowing its transaction was rolled back. Retry the whole transaction a small number of times, after a short random pause.

DECLARE @attempt int = 1, @pause char(8);

WHILE @attempt <= 3
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE dbo.accounts SET balance = balance - 100 WHERE account_id = 1;
            UPDATE dbo.accounts SET balance = balance + 100 WHERE account_id = 2;
        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        IF ERROR_NUMBER() <> 1205 OR @attempt = 3 THROW;  -- give up loudly
        SET @pause = '00:00:0' + CAST(1 + ABS(CHECKSUM(NEWID())) % 3 AS char(1));
        WAITFOR DELAY @pause;                               -- 1 to 3 seconds
        SET @attempt += 1;
    END CATCH
END;

If one side should always lose, for example a nightly batch against interactive users, set its priority. The session with the lower priority is chosen as the victim.

SET DEADLOCK_PRIORITY LOW;   -- LOW, NORMAL, HIGH, or -10 to 10

7. Keep a longer history

If system_health rolls over before you get to it, create a session that captures only deadlocks, so its files fill slowly.

CREATE EVENT SESSION deadlock_capture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (
    SET filename = N'deadlock_capture.xel',
        max_file_size = 20,        -- MB per file
        max_rollover_files = 10
)
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION deadlock_capture ON SERVER STATE = START;

The files go to the instance's log folder. Read them with the queries in steps 2 and 3, replacing system_health*.xel with deadlock_capture*.xel. On Azure SQL Managed Instance, event files for your own sessions are written to Azure Storage, so filename must be a blob URL with a credential.

The same steps in Quorum

Quorum draws blocking and deadlocks for you: who waited for whom, the root blocker, the transaction age and the program that owns each session, beside the wait-time analysis for the same window.

• Blocking and deadlocks drawn with the root blocker, transaction age and owning program, instead of reading XML.
• Alerts grouped with cooldowns, so a burst of deadlocks is one alert, and the notification opens the investigation.
• Change timeline: see which deployment or job started just before the deadlocks did.
• Index advice ranked by measured impact, with the storage and write overhead shown.
• Jira with an RCA draft when the cause is in application code that someone else owns.