How to Catch the SQL Server Deadlocks You Never See

A SQL Server deadlock happens when two or more transactions each hold a lock another one needs. The result is a cycle that nothing inside the transactions can break. SQL Server breaks it instead. It picks one transaction as the victim and rolls it back. The whole event is over in seconds, and most deadlocks never reach anyone’s attention. 

Here is how the engine resolves them and how to catch the ones you never see.

Key Takeaways

• A SQL Server deadlock is a circular lock dependency. The lock monitor finds the cycle, picks a victim, and rolls that transaction back with error 1205.
• The monitor runs every five seconds by default and speeds up to as little as 100 milliseconds while deadlocks keep appearing.
• Blocking is one transaction waiting on another and can persist indefinitely. A deadlock cannot resolve itself and ends in seconds because the engine kills a participant.
• Most deadlocks trace to inconsistent lock ordering, missing indexes, or long transactions. Others come from memory grants, worker threads, or parallel query exchange, where lock order is irrelevant.
• The xml_deadlock_report Extended Event is Microsoft’s recommended capture method. Trace flags 1204 and 1222 still work, but Microsoft warns against them on busy systems.
• Error 1205 is an expected outcome. Application code should catch it and retry the transaction from the start.

What SQL Server Does When It Detects a Deadlock

Deadlocks clear themselves. An application logs a failed transaction overnight. The user retries, the work goes through, and by morning the locks are gone. Wait statistics show nothing because nothing was still waiting.

The Database Engine runs a lock monitor thread that checks all running tasks for circular lock dependencies. The default interval is five seconds. It drops to as low as 100 milliseconds while deadlocks keep appearing, then relaxes back to five once they stop. When the monitor finds a cycle, it picks one task as the victim. It ends the victim’s batch, rolls back the transaction, and returns error 1205 to the application. The victim’s locks are released, and the surviving transactions carry on.

Victim selection follows three rules, in order:

1. Priority. A session with a lower DEADLOCK_PRIORITY loses. Priority accepts LOW, NORMAL, HIGH, or any integer from -10 to 10.
2. Rollback cost. At equal priority, SQL Server kills whichever transaction is cheapest to roll back, measured by log bytes written.
3. Chance. At equal priority and equal cost, the engine picks the victim at random.

A transaction already rolling back can never be selected.

Without real-time alerting, the only evidence a deadlock leaves is the graph, an XML record of the processes, resources, and lock modes in the cycle. No other source tells you which queries collided and on what.

Deadlocks and Blocking Are Separate Diagnoses

Blocking is one transaction waiting for a lock another transaction holds. It clears when the holder commits or rolls back. By default, it waits forever unless you set LOCK_TIMEOUT. A deadlock is different. Every participant waits on a lock held by another participant, so waiting longer never helps. Microsoft’s guide notes the condition is sometimes called a deadly embrace.

Sustained LCK_M_* waits and a growing blocking chain mean blocking. A transaction that vanished with error 1205 means a deadlock. Treating one as the other sends you to the wrong tools.

What Actually Causes SQL Server Deadlocks

Three patterns cause most production deadlocks, and they compound each other.

Access ordering is the one cause you can eliminate outright. When every transaction touches shared objects in the same sequence, no cycle can form. Reverse that order anywhere in the codebase, and a cycle becomes possible the first time two sessions overlap.

Indexes get treated as a query-tuning concern. They are a concurrency concern too. Without a usable index, SQL Server scans and locks far more rows than the query needs. Two unrelated statements can then collide on rows neither of them cares about. Stale statistics and index fragmentation push the optimizer toward scans in the same way, widening the locked range without changing the query.

Transaction duration multiplies both effects. A transaction holding locks for 20 milliseconds and one holding them for 20 seconds can share identical lock ordering. Their deadlock rates will still differ greatly. Any explicit transaction that waits on something outside the engine, a web service call or a user confirmation, holds that window open.

Deadlocks That Have Nothing to Do With Lock Order

Lock ordering explains most deadlocks. Microsoft’s guide lists resources beyond locks that can deadlock, and some lock deadlocks form even when every session takes the same order. Five show up in production.

1. Memory grants. Two concurrent queries each reserve part of the available workspace memory, and each needs more than what remains. Neither releases until it finishes, and neither can finish.
2. Worker threads. A session holding a lock goes idle while every remaining worker thread is queued behind that lock. The holder cannot get a thread to commit on.
3. Parallel query exchange. Coordinator, producer, and consumer threads in a parallel query block each other, usually involving at least one process outside the query.
4. Conversion deadlocks. Two sessions hold a shared lock on the same row, and both then request an update or exclusive lock on it. The access order matches, and a cycle still forms.
5. Partitioned tables under LOCK_ESCALATION = AUTO. Separate transactions hold locks on different partitions, and each wants a lock on the other’s. Setting LOCK_ESCALATION to TABLE prevents this, at the cost of concurrency.

The graph tells you which kind you have. A keylock or ridlock node in the resource list means an ordinary lock deadlock. An exchangeEvent or threadPool node means no amount of access-order discipline would have prevented it.

How to Detect SQL Server Deadlocks

The xml_deadlock_report Extended Event is the recommended way to capture deadlock information. The trace flags are the legacy path.

Capture the Graph From system_health

Every modern instance runs the system_health Extended Events session by default, and it captures xml_deadlock_report events with no setup. The graph is already sitting on the server. This query pulls what the ring buffer target still holds.

SELECT
    xed.value('@timestamp', 'datetime2') AS deadlock_utc,
    xed.query('.')                       AS deadlock_graph
FROM
(
    SELECT CAST(xt.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 src
CROSS APPLY target_data.nodes
    ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed)
ORDER BY deadlock_utc DESC;

The ring buffer target is capped at 4 MB by default, and the event_file target rolls over. The query reaches back only as far as the session has retained. A deadlock from last month is gone. For durable history, create a dedicated event session that writes xml_deadlock_report to its own file target. Or collect the events into a monitoring repository.

Trace Flags 1204 and 1222 Still Work, With a Caveat

Both trace flags write deadlock details to the SQL Server error log. Flag 1204 organizes output by the nodes in the cycle. Flag 1222 organizes it by process, then by resource, in an XML-like format. You can enable either globally at runtime.

DBCC TRACEON (1222, -1);   -- global, until the next restart
-- persist across restarts with the -T1222 startup parameter

Microsoft advises against both on busy systems. The guide warns the flags might introduce performance issues on heavily loaded servers experiencing deadlocks and points to Extended Events instead.

How to Read a Deadlock Graph

Every graph has three sections. The <victim-list> names the process the engine killed. The <process-list> describes each participant, including its inputbuf and executionStack. The <resource-list> shows what they were fighting over and which lock modes they held.

Every graph has three nodes. victim-list names the process the engine killed. process-list describes each participant, including its inputbuf and executionStack. resource-list describes what they were fighting over and in which lock modes.

<deadlock>
  <victim-list>
    <victimProcess id="process27b9b0b9848" />
  </victim-list>
  <process-list>
    <process id="process27b9b0b9848" lockMode="S" waitresource="KEY: 5:72057594214350848">
      <inputbuf> EXEC dbo.usp_GetOrderTotals @OrderId = 4 </inputbuf>
    </process>
    <process id="process27b9ee33c28" lockMode="X" waitresource="KEY: 5:72057594214416384">
      <inputbuf> EXEC dbo.usp_ApplyOrderLine @OrderId = 4 </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock objectname="Sales.dbo.Orders" indexname="cidx" mode="X">
      <owner-list><owner  id="process27b9ee33c28" mode="X" /></owner-list>
      <waiter-list><waiter id="process27b9b0b9848" mode="S" /></waiter-list>
    </keylock>
    <keylock objectname="Sales.dbo.OrderLines" indexname="idx1" mode="S">
      <owner-list><owner  id="process27b9b0b9848" mode="S" /></owner-list>
      <waiter-list><waiter id="process27b9ee33c28" mode="X" /></waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Read the XML in this order:

1. Read resource-list to see which objects and indexes are involved.
2. Match each owner and waiter back to a process ID.
3. Read each process’s inputbuf to get the running statement.
4. Compare the two access orders.

Comparing the Three Approaches

The three differ in when the signal reaches you and how much work each event costs.

All three approaches are reactive. You go looking for the record after something breaks, usually an application error or a support ticket. And each record covers one event. A single graph shows that two statements collided once. It cannot show whether they collide every night, or whether the same missing index is causing deadlocks on other instances. Answering those questions from the error log means hand-parsing dozens of XML payloads.

A monitoring platform closes that gap. IDERA SQL Diagnostic Manager captures every deadlock automatically and renders the graph visually. You see the exact queries and resources on both sides of the conflict, plus the history that shows whether the rate is rising.

How to Prevent SQL Server Deadlocks

SQL Server resolves the deadlock in front of you. Keeping it from happening again belongs to the DBA and the application team.

• Standardize access order. Give the tables in multi-object transactions one agreed sequence, document it, and enforce it in code review. It is the only measure that removes the possibility of a cycle. Everything else lowers the odds.
• Shorten transactions. Open as late as possible and commit as early as possible. Keep validation, formatting, and external calls outside the BEGIN TRAN boundary.
• Index for the access pattern that runs in production. An index that supports your high-concurrency statements lets SQL Server lock only the rows it needs instead of scanning a wide range. That reduces deadlocks and blocking.
• Tune isolation deliberately. Read Committed Snapshot Isolation serves readers from the version store instead of taking shared locks, which removes reader-writer conflicts. It also shifts load to tempdb and changes read semantics, so treat it as an architectural decision.
• Set DEADLOCK_PRIORITY LOW on work you can afford to lose. A nightly report or a bulk load can lose every cycle it enters. The interactive transaction survives, and the batch retries on its own schedule.

Fixing the Read-Then-Update Deadlock

The most common deadlock in OLTP code comes from two sessions reading the same row and then updating it. Both take a shared lock on the read. Both request an exclusive lock on the update. Each blocks the other’s shared lock. Both sessions touch one table in one order, and it still deadlocks.

Take the restrictive lock at read time instead of at update time.

All three share a limitation. Each preserves a record you go and retrieve after an application error, a support ticket, or a user complaint. A single graph tells you two statements collided once. It says nothing about whether that pair collides nightly in the same job window, whether the rate is climbing, or whether one missing index sits behind four other deadlocks on three other instances. Reconstructing that from the error log means hand-parsing dozens of XML payloads.

A monitoring platform closes that gap. IDERA SQL Diagnostic Manager captures every deadlock automatically and renders the graph visually, with the exact queries and resources on both sides of the conflict, alongside the historical data that shows whether the rate is rising.

How to Prevent SQL Server Deadlocks

SQL Server resolves the deadlock in front of you. Keeping it from happening again belongs to the DBA and the application team.

1. Standardize access order. Give the tables that participate in multi-object transactions a canonical sequence, document it, and enforce it in code review. It is the only measure that removes the possibility of a cycle rather than lowering the odds.
2. Shorten transactions. Open as late as possible, commit as early as possible, and keep anything that does not need transactional consistency outside the BEGIN TRAN boundary. Validation, formatting, and external calls all belong ahead of it.
3. Index for the access pattern that runs in production. An index supporting the predicates in your high-concurrency statements lets SQL Server lock only the rows it needs instead of scanning a wide range. Narrowing the lock footprint reduces deadlocks and general blocking together.
4. Tune isolation deliberately. Read Committed Snapshot Isolation serves readers from the version store instead of taking shared locks, removing reader-writer conflicts outright. It shifts load to tempdb and changes read semantics, so treat it as an architectural decision rather than a switch.
5. Set DEADLOCK_PRIORITY LOW on work you can afford to lose. A nightly report or a bulk load can be told to lose every cycle it enters. The interactive transaction survives and the batch retries on its own schedule.

Fixing the Read-Then-Update Deadlock

The most common deadlock in OLTP code is not a reversed table order. It is two sessions reading the same row and then updating it. Both take a shared lock on the read, both request an exclusive lock on the update, and each is blocked by the other’s shared lock. Same table, same order, still a cycle.

Take the restrictive lock at read time instead of at update time.

BEGIN TRANSACTION;

SELECT @Balance = Balance
FROM   dbo.Accounts WITH (UPDLOCK, HOLDLOCK)
WHERE  AccountId = @AccountId;

UPDATE dbo.Accounts
SET    Balance = @Balance - @Amount
WHERE  AccountId = @AccountId;

COMMIT TRANSACTION;

UPDLOCK takes an update lock during the read, and update locks are not compatible with each other, so the second session waits instead of joining a cycle. HOLDLOCK keeps it until commit. The two sessions now serialize on that row, which is the correct trade.

Retry Logic for Error 1205

Error 1205 is a documented, expected outcome. The victim needs a second attempt.

DECLARE @Attempt INT = 1, @MaxAttempts INT = 3;

WHILE @Attempt <= @MaxAttempts
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        --  the work
        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        IF ERROR_NUMBER() <> 1205 OR @Attempt = @MaxAttempts
            THROW;

        SET @Attempt += 1;
        WAITFOR DELAY '00:00:00.100';
    END CATCH
END

Two rules matter more than the shape of the loop. The retry has to reopen the transaction from the start, because the engine already rolled the original one back and there is nothing to resume. And a WAITFOR inside the retry holds a worker thread while it sleeps, so on a busy instance the retry belongs in application code rather than in T-SQL.

Turning Isolated Deadlocks Into a Fixable Pattern

Every method above assumes you know a deadlock happened. Most deadlock work stalls on that assumption, because the graph is in a buffer nobody queried and the error the user saw was retried away.

IDERA SQL Diagnostic Manager alerts on each SQL Server deadlock as the cycle resolves and visualizes the graph, showing the exact queries and resources on both sides of the conflict without anyone parsing XML. Because the history lives in a repository you own, the second question, whether this pair collides every Tuesday night, has an answer too.

Ron Power, IT Systems Analyst at the City of St Johns, reports that “With SQL Diagnostic Manager, we resolve issues with deadlocks in a more efficient manner.” IDERA covers the workflow in more detail in find and fix SQL Server deadlocks and the accompanying deadlock solution brief.

Frequently Asked Questions

What is a deadlock in SQL Server?
A SQL Server deadlock is a cycle of lock dependencies that no participant can break. Two or more transactions each hold a lock another transaction in the cycle is waiting to acquire. The lock monitor detects the cycle, terminates one transaction with error 1205, releases its locks, and lets the rest finish.

How do you fix a deadlock in SQL Server?
SQL Server fixes the immediate deadlock by rolling back the victim. Fixing the cause is the DBA’s job. Give shared objects a consistent lock order, take UPDLOCK, HOLDLOCK on read-then-update patterns, index for the predicates in high-concurrency statements, shorten transaction duration, and retry error 1205 in application code.

How do you cause a deadlock in SQL Server?
Run two sessions against a test instance, each opening an explicit transaction and updating a row the other will need next.

--  Session 1
BEGIN TRANSACTION;
UPDATE dbo.Orders     SET Status = 'HOLD' WHERE OrderId = 1;
--  now run all of session 2, then come back and run the next statement
UPDATE dbo.OrderLines SET Qty    = 2      WHERE OrderId = 1;
COMMIT TRANSACTION;

--  Session 2
BEGIN TRANSACTION;
UPDATE dbo.OrderLines SET Qty    = 3      WHERE OrderId = 1;
UPDATE dbo.Orders     SET Status = 'OK'   WHERE OrderId = 1;
COMMIT TRANSACTION;

Session 2 blocks on Orders while session 1 blocks on OrderLines. Within five seconds the lock monitor closes the cycle and one session returns error 1205.

What is an example of a deadlock in SQL?
Transaction A takes an exclusive lock on a row in Orders and then requests a row in OrderLines. Transaction B already holds that OrderLines row and requests the Orders row A is holding. Neither can proceed, the lock monitor finds the cycle, and one of them rolls back.

Start Monitoring Deadlocks Before Users Report Them

SQL Server clears every deadlock and moves on, leaving the pattern intact and the graph unread until an application error escalates into a ticket. Consistent lock ordering, tighter transactions, UPDLOCK on read-then-update, and indexes matched to real access patterns will remove most of them. Error-log archaeology after the fact rarely produces the pattern view that says which one to fix first.

IDERA SQL Diagnostic Manager gives DBAs real-time SQL Server deadlock alerting with the graph rendered visually, the queries and plans that produced it, and years of history in a repository you own, across on-premises, Azure, AWS, and GCP instances. Start a 14-day free trial, agentless and live in about five minutes, and see which deadlock patterns your environment has been resolving quietly.