SQL Server Deadlocks: How to Find, Analyze and Prevent Them

A deadlock happens when two sessions each hold a lock the other one needs, so neither can ever continue. SQL Server breaks the cycle by cancelling one of them, called the deadlock victim, and that session gets error 1205. In SSMS or in your application logs it looks like this:

Occasional deadlocks are normal in a busy database. Frequent ones point to a design problem in the application or the database, and in our experience they’re one of the most common side effects of applications that don’t manage their transactions well.

This guide explains what a deadlock is and how it differs from blocking, how SQL Server detects and resolves it, how to capture and read deadlock graphs, the patterns behind most deadlocks, and the fixes that actually work.

Deadlock vs. Blocking

The two are often confused, but they behave very differently:

Long blocking chains are their own problem; the first steps for diagnosing them are in our SQL Server outage runbook.

How SQL Server Detects and Resolves Deadlocks

A background lock monitor looks for deadlock cycles every 5 seconds by default. When it starts finding deadlocks, it checks more often, down to every 100 milliseconds, and backs off again when they stop. When it finds a cycle, SQL Server:

1. Chooses a deadlock victim: by default, the transaction that is least expensive to roll back.
2. Terminates the victim’s current batch, rolls back its transaction and releases its locks.
3. Returns error 1205 to the victim’s application. The other transaction continues.

You can influence victim selection with SET DEADLOCK_PRIORITY (LOW, NORMAL, HIGH or a number from -10 to 10). When sessions have different priorities, the one with the lower priority becomes the victim. Setting LOW on background jobs and reports protects user transactions.

Capture Deadlocks: system_health Already Records Them

You don’t need to set anything up to start investigating. The system_health Extended Events session, enabled by default, captures every deadlock as an xml_deadlock_report event, including the full deadlock graph.

In SSMS: Management → Extended Events → Sessions → system_health → open event_file, then filter on xml_deadlock_report. Clicking an event shows a graphical deadlock graph.

With T-SQL, this query lists the deadlocks recorded in the session’s event files:

SELECT x.ed.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time_utc,
       x.ed.query('(event/data/value/deadlock)[1]') AS deadlock_graph
FROM (SELECT CAST(event_data AS xml) AS ed
      FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
      WHERE object_name = N'xml_deadlock_report') AS x
ORDER BY deadlock_time_utc DESC;

Save a deadlock_graph value as a .xdl file and open it in SSMS to see the graph. The system_health files roll over, so on a busy server older deadlocks disappear; if you’re chasing a recurring problem, create a dedicated Extended Events session for xml_deadlock_report with its own file target and retention.

To see how often deadlocks happen over time, track the Number of Deadlocks/sec performance counter (SQLServer:Locks) in your monitoring.

Reading a Deadlock Graph

Every deadlock graph answers the same questions:

• Who was involved? The process list shows each session, its login, host, application name, isolation level and the statement it was running (inputbuf). One is marked as the victim.
• What did they fight over? The resource list shows the locked objects: key locks on a specific index, page or row locks, object locks.
• In which order? For each resource, which process owned which lock mode (S, U, X) and which process was waiting for it. Following the owner and waiter arrows reveals the cycle.

Two details are especially useful: the index name on key locks, which often points straight to a missing or poorly chosen index, and the isolation level, because an unexpected SERIALIZABLE or REPEATABLE READ (often set by a framework or driver default) is a frequent cause.

The Patterns Behind Most Deadlocks

1. The same tables accessed in reverse order

The classic case: one transaction updates table A and then table B, another updates B and then A. Here’s a minimal reproduction:

CREATE TABLE dbo.ProductSold      (ProductID int PRIMARY KEY, QuantitySold int);
CREATE TABLE dbo.UpdatedInventory (ProductID int PRIMARY KEY, QuantityInStock int);
INSERT dbo.ProductSold      VALUES (1, 10), (2, 20);
INSERT dbo.UpdatedInventory VALUES (1, 100), (2, 200);

-- Session 1                                    -- Session 2
BEGIN TRAN;                                     BEGIN TRAN;
UPDATE dbo.ProductSold                          UPDATE dbo.UpdatedInventory
   SET QuantitySold += 1                           SET QuantityInStock -= 10
 WHERE ProductID = 1;   -- X lock on A           WHERE ProductID = 1;   -- X lock on B
UPDATE dbo.UpdatedInventory                     UPDATE dbo.ProductSold
   SET QuantityInStock -= 1                        SET QuantitySold += 10
 WHERE ProductID = 1;   -- waits for B           WHERE ProductID = 1;   -- waits for A: deadlock

The fix: every code path that touches these tables must access them in the same order. Putting the logic in one stored procedure makes the order impossible to get wrong:

CREATE OR ALTER PROCEDURE dbo.RecordSale @ProductID int, @Quantity int
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRAN;
        UPDATE dbo.ProductSold      SET QuantitySold    += @Quantity WHERE ProductID = @ProductID;  -- always first
        UPDATE dbo.UpdatedInventory SET QuantityInStock -= @Quantity WHERE ProductID = @ProductID;  -- always second
    COMMIT;
END;

2. Readers and writers blocking each other

Under the default, lock-based READ COMMITTED isolation level, readers take shared locks that conflict with writers’ exclusive locks. A report reading two tables while a transaction updates them in a different order can deadlock. Row versioning removes this whole class (see below).

3. Lookup deadlocks between an index and the table

A query that seeks a nonclustered index and then looks up the remaining columns in the clustered index locks the index first and the table second. An update that changes a column in that index modifies the clustered index first and the nonclustered index second. The two lock the same row in opposite order. A covering index that includes the columns the query needs removes the lookup and usually the deadlock.

4. Long transactions and application design

The longer a transaction holds locks, the more likely it is to collide with another. In the field, the root cause is very often the application:

• Transactions that stay open while waiting for user input or an external service call.
• Many statements, or a whole batch import, wrapped in one huge transaction.
• Connections returned to the pool with a transaction still open, holding locks while the session sleeps.
• ORM or driver defaults that silently raise the isolation level to SERIALIZABLE.

5. Parallelism

Occasionally a single parallel query deadlocks with itself, or parallel threads take part in a cycle with another session. The graph shows the same session ID several times with different execution contexts (ecid). Tuning the query, adding a suitable index, or limiting MAXDOP for that query usually resolves it.

How to Prevent Deadlocks

1. Access objects in the same order in every transaction and code path.
2. Keep transactions short and in one batch. No user interaction, no external calls, and no unnecessary work inside the transaction.
3. Index for the queries involved. Better indexes mean fewer rows locked, shorter lock durations and fewer lookups. The index names in the deadlock graph show where to start.
4. Use row versioning. With READ_COMMITTED_SNAPSHOT on, readers no longer take shared locks; they read the last committed version of the row. Microsoft recommends row-versioning-based READ COMMITTED for all applications unless an application depends on the blocking behaviour of lock-based READ COMMITTED. It adds work in tempdb (or in the database with Accelerated Database Recovery), so test it before enabling it in production.
5. Use the lowest isolation level that’s correct. Check that SERIALIZABLE or REPEATABLE READ is truly needed wherever it’s used.
6. Set DEADLOCK_PRIORITY LOW for background jobs and reports, so user transactions aren’t chosen as victims.
7. Handle error 1205 in the application and retry the transaction.

-- Retry pattern for deadlock victims (error 1205)
DECLARE @retry int = 3;
WHILE @retry > 0
BEGIN
    BEGIN TRY
        EXEC dbo.RecordSale @ProductID = 1, @Quantity = 1;
        SET @retry = 0;                       -- success
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205 AND @retry > 1
        BEGIN
            SET @retry -= 1;
            WAITFOR DELAY '00:00:00.200';     -- short pause before retrying
        END
        ELSE
            THROW;                            -- other errors, or retries exhausted
    END CATCH
END;

NOLOCK Is Not a Deadlock Fix

Frequently Asked Questions