Deadlocks: Reading the Deadlock Graph, Common Patterns and Retrying in .NET

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10) at compatibility level 170; the .NET examples were built and run with .NET 10, Microsoft.Data.SqlClient 7.1.1, Entity Framework Core (EF Core) 10.0.14 and Polly.Core 8.9.1.

Guess First: Which One Becomes the Victim?

Two jobs run at the same time in the Bookstore. Checkout first reserves the book's stock, then updates the order. Cancellation first cancels the order, then puts the book back into stock. Same order, same book, opposite order of access:

/* checkout: reserve stock, then touch the order */
BEGIN TRANSACTION;
UPDATE dbo.StockItems SET QuantityReserved += 1 WHERE BookId = 1;
WAITFOR DELAY '00:00:02';
UPDATE dbo.Orders SET Status = 'PaymentPending' WHERE OrderId = 999001;
COMMIT;

/* cancel: touch the order, then return the book to stock */
BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 'Cancelled' WHERE OrderId = 999001;
WAITFOR DELAY '00:00:02';
UPDATE dbo.StockItems SET QuantityOnHand += 1 WHERE BookId = 1;
COMMIT;

We start them half a second apart. One will finish, one will get an error. Which one becomes the victim: the one that started first, the one that started second, or does SQL Server look at something else?

Msg 1205, Level 13, State 51, Server b8be943222fa, Line 5
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Checkout was the victim. Not the order, the cost decided: SQL Server picks the transaction that is cheapest to roll back and measures that by the number of bytes written to the transaction log. In a moment we'll see in the graph that checkout has logused="240" and the cancellation has logused="276". In this post we capture and read the deadlock graph, look at three patterns that really happen in the Bookstore, the ways to prevent them, and how to safely retry 1205 on the .NET side.

A deadlock is the blocking chain from the previous post turned into a cycle: A waits for what B holds, and B waits for what A holds. Neither can get out alone. By default SQL Server's lock monitor looks for such cycles every five seconds; when it finds one, it picks a victim, rolls back its transaction and returns error 1205 to that session.

Capturing the Deadlock Graph

Every deadlock has a report: the xml_deadlock_report event. SQL Server's system_health Extended Events session, which is on in every installation, already collects this event; on a production server that's the first place you look. This lab is on a shared server where other work runs too, so we opened our own session:

CREATE EVENT SESSION sqls_12_deadlocks ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (MAX_DURATION = 2 HOURS);
GO
ALTER EVENT SESSION sqls_12_deadlocks ON SERVER STATE = START;

In our first attempt we put a WHERE database_name = N'sqls_12' filter on the event and no event arrived at all. The reason: the only field of xml_deadlock_report itself is xml_report. The filter turns into the global predicate that looks at the current session's database, while the event is produced in the lock monitor's system context. You have to apply the filter while reading:

SELECT x.e.query('(data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM sys.dm_xe_session_targets AS t
JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
CROSS APPLY (SELECT CAST(t.target_data AS xml)) AS d(x)
CROSS APPLY d.x.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(e)
WHERE s.name = 'sqls_12_deadlocks'
  AND x.e.exist('data/value/deadlock/process-list/process[@currentdbname="sqls_12"]') = 1;

The same query works for system_health with the session name and target changed. A ring buffer lives in memory and is limited; in the key lookup experiment below we produced 1,955 deadlocks and only the last 285 stayed in the buffer. Use the event_file target for a persistent record.

Reading the Graph

The raw XML (Extensible Markup Language) is long; most of it is a stackFrames section full of Windows module names, even on Linux. Picking out the useful parts, the opening deadlock looks like this:

<victim-list><victimProcess id="processd981b8088"/></victim-list>
<process id="processd981b8088" spid="54" lockMode="X"
         waitresource="KEY: 55:72057594047430656 (94e983785ab1)"
         isolationlevel="read committed (2)" logused="240">
    inputbuf: UPDATE dbo.Orders SET Status = 'PaymentPending' WHERE OrderId = 999001;
<process id="processdd48d6c58" spid="60" lockMode="X"
         waitresource="KEY: 55:72057594047561728 (8194443284a0)"
         isolationlevel="read committed (2)" logused="276">
    inputbuf: UPDATE dbo.StockItems SET QuantityOnHand += 1 WHERE BookId = 1;
<resource-list>
  <keylock objectname="sqls_12.dbo.Orders" indexname="PK_Orders" mode="X">
    <owner id="processdd48d6c58" mode="X"/>
    <waiter id="processd981b8088" mode="X" requestType="wait"/>
  <keylock objectname="sqls_12.dbo.StockItems" indexname="PK_StockItems" mode="X">
    <owner id="processd981b8088" mode="X"/>
    <waiter id="processdd48d6c58" mode="X" requestType="wait"/>

The reading order is always the same. First the resource-list: for each resource, who owns it (owner) and who waits (waiter). There are two resources here: the order key in PK_Orders is held by the cancellation, the stock key in PK_StockItems is held by checkout, and each waits for the other's. That's the cycle. Then the process-list: the statement each process was waiting in at that moment (inputbuf and executionStack), its isolation level and log usage. Finally the victim-list: which one was sacrificed.

What you won't see in the graph is the order in which the locks were taken. You work that out by reading the code: checkout holds the stock and wants the order; the cancellation holds the order and wants the stock. One more thing to note: inputbuf shows the whole batch (we shortened it here), while executionStack shows the waiting line. In stored procedures, procname and line take you straight to the line.

Common Patterns

1. Opposite access order

The opening example. Two jobs touch the same resources in a different order. In real code this order is rarely visible: the checkout service calls InventoryService.ReserveAsync first and then OrderService.UpdateStatusAsync, while on the cancellation side another developer wrote the opposite. The fix is simple but takes discipline: every code path that writes to more than one table in the same transaction should follow the same order. For the Bookstore the rule could be "Orders first, then StockItems".

2. Check first, then insert

In the previous post we solved the upsert race with UPDLOCK, SERIALIZABLE. Write the same code with only HOLDLOCK and the race turns into a deadlock:

DECLARE @d date = '2026-11-08', @b int = 42;
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM dbo.DailyBookSales WITH (HOLDLOCK)
               WHERE SalesDate = @d AND BookId = @b)
BEGIN
    WAITFOR DELAY '00:00:02';
    INSERT dbo.DailyBookSales (SalesDate, BookId, Quantity) VALUES (@d, @b, 1);
END
ELSE
    UPDATE dbo.DailyBookSales SET Quantity += 1 WHERE SalesDate = @d AND BookId = @b;
COMMIT;

<process id="processdaac7e868" spid="60" lockMode="RangeI-N"
         waitresource="KEY: 55:72057594048348160 (ffffffffffff)" isolationlevel="read committed (2)">
<process id="processda5147438" spid="54" lockMode="RangeI-N"
         waitresource="KEY: 55:72057594048348160 (ffffffffffff)" isolationlevel="read committed (2)">
<keylock objectname="sqls_12.dbo.DailyBookSales" indexname="PK_DailyBookSales" mode="RangeS-S">
  <owner-list>
    <owner id="processda5147438" mode="RangeS-S"/>
    <owner id="processda5147438" mode="RangeI-N" requestType="convert"/>
  </owner-list>
  <waiter-list><waiter id="processdaac7e868" mode="RangeI-N" requestType="convert"/></waiter-list>

The table is empty, so both sessions lock the same resource, the range from the last key to infinity (ffffffffffff), with RangeS-S. RangeS-S is compatible with itself, so both get it. When it's time to INSERT, both want to convert their lock to RangeI-N (insert), and each waits for the other's RangeS-S. The requestType="convert" in the graph tells you this is a conversion deadlock. isolationlevel still shows read committed, because the hint is at table level, not statement level. The fix is to take the read lock as U from the start: in the UPDLOCK version the second session waits instead of deadlocking.

3. The key lookup deadlock: a reader and a writer

The most surprising one, because one side only runs a SELECT. The reader reads the status of a customer's orders; the writer moves an order to another customer:

/* reader */
SELECT @s = Status FROM dbo.Orders WHERE CustomerId = 15018;

/* writer */
UPDATE dbo.Orders
SET CustomerId = CASE CustomerId WHEN 15018 THEN 15709 ELSE 15018 END
WHERE OrderId = 250163;

The reader's plan is a nonclustered index seek followed by a key lookup:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([sqls_12].[dbo].[Orders].[OrderId]))
       |--Index Seek(OBJECT:([sqls_12].[dbo].[Orders].[IX_Orders_CustomerId]), SEEK:([sqls_12].[dbo].[Orders].[CustomerId]=(15018)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([sqls_12].[dbo].[Orders].[PK_Orders]), SEEK:([sqls_12].[dbo].[Orders].[OrderId]=[sqls_12].[dbo].[Orders].[OrderId]) LOOKUP ORDERED FORWARD)

We ran the two queries in a loop in two sessions, 20,000 times each. The reader was the victim 1,955 times, the writer never. The graph:

<process id="processdaac7e868" spid="60" lockMode="S"
         waitresource="KEY: 55:72057594047430656 (21a883cfb4e3)" logused="0">
<process id="processda5147438" spid="54" lockMode="X"
         waitresource="KEY: 55:72057594048020480 (cb19ef703de5)" logused="220">
<keylock objectname="sqls_12.dbo.Orders" indexname="PK_Orders" mode="X">
    <owner id="processda5147438" mode="X"/>
    <waiter id="processdaac7e868" mode="S" requestType="wait"/>
<keylock objectname="sqls_12.dbo.Orders" indexname="IX_Orders_CustomerId" mode="S">
    <owner id="processdaac7e868" mode="S"/>
    <waiter id="processda5147438" mode="X" requestType="wait"/>

The same table, two different indexes. The reader goes left to right: first S on the key in IX_Orders_CustomerId, then S on the row in PK_Orders for the lookup. The writer goes the other way: first X on the row in the clustered index, then, because CustomerId changes, X on the key in the nonclustered index. Opposite access order again, but this time inside a single table, and nobody wrote the order in code. The reader's log usage is 0, so it is picked as the victim every time.

4. Cascades and foreign key checks

A foreign key with ON DELETE CASCADE makes the statement that deletes the parent row take locks in the child tables too, and those locks can form a cycle with work that reaches the same child rows in a different order. Foreign keys without an index lead to the same result: deleting from the parent has to scan the child table and takes locks over a much wider area. The Bookstore schema has no cascades and every foreign key column is indexed; check these two rules in your own schema as well.

Prevention: Order, Indexes, Short Transactions and RCSI

Getting deadlocks down to zero isn't always possible; the goal is to lower their frequency and retry the rest safely. We measured two measures on the key lookup deadlock; in each run the reader and the writer ran 3,000 iterations each:

/* 1: covering index */
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId) INCLUDE (Status) WITH (DROP_EXISTING = ON);

/* 2: RCSI, with the original index */
ALTER DATABASE sqls_12 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

variant                              reader_deadlocks  writer_deadlocks
baseline (IX_Orders_CustomerId)      261               0
covering index INCLUDE (Status)      0                 0
RCSI on, original index              0                 0

The covering index means the reader never needs a second resource: Status is now at the leaf level of the nonclustered index, no lookup. A reader that locks a single resource can't be part of a cycle. RCSI (Read Committed Snapshot Isolation) removes the reader's locks entirely: the reader reads the last committed version of the row from the version store. Every reader-writer deadlock disappears with that one setting; writer-writer deadlocks (patterns 1 and 2) remain.

The effect of short transactions is less visible but the most general: the shorter a lock is held, the lower the chance of a cycle. To produce the deadlock in the lab we had to put a WAITFOR in the middle; in production that gap is filled by an HTTP (Hypertext Transfer Protocol) call inside the transaction, an unnecessary SELECT, or a screen waiting for the user to confirm.

The last tool is DEADLOCK_PRIORITY. It makes the victim be chosen by priority instead of cost: LOW, NORMAL, HIGH, or a number between -10 and 10. It suits a nightly report or a background job that is easy to retry. In the opening example, adding SET DEADLOCK_PRIORITY LOW; to the cancellation session gives:

Msg 1205, Level 13, State 51, Server b8be943222fa, Line 6
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This time the cancellation got the error; even though it used more log, it was sacrificed because of its lower priority, and checkout completed both of its UPDATEs. Priority comes before cost; cost decides only when priorities are equal.

Retrying 1205 in .NET

A transaction chosen as the victim has been rolled back completely; from the database's point of view it's safe to run it again. The error message says so too: "Rerun the transaction." The key word is transaction: you must rerun the whole transaction from the start, not the statement. Let's produce the opening deadlock from .NET with two Tasks, look at SqlClient's exception and ask whether EF Core considers this error transient:

SqlException victim = await lab.RunWithoutRetryAsync();
Console.WriteLine($"Number={victim.Number} Class={victim.Class} State={victim.State}");
#pragma warning disable EF1001
Console.WriteLine($"EF Core transient: {Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerTransientExceptionDetector.ShouldRetryOn(victim)}");
#pragma warning restore EF1001

checkout: SqlException 1205: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
cancel: committed
Number=1205 Class=13 State=51
EF Core transient: True

EF Core 10's SqlServerTransientExceptionDetector treats 1205 as transient; in the source on GitHub the list also contains 1204 and the lock timeout error 1222 next to 1205. This class is an internal API (warning EF1001); we called it here only to verify. So with EnableRetryOnFailure() on, EF Core retries the deadlock by itself. But with one condition:

await using IDbContextTransaction tx = await db.Database.BeginTransactionAsync();
Order order = await db.Orders.SingleAsync(o => o.OrderId == 999001);

EF Core: The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

EF Core can't catch a transaction you opened yourself halfway and repeat it, because it doesn't know what the transaction has done since it started. The fix is to hand the whole transaction to the strategy:

IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using IDbContextTransaction tx = await db.Database.BeginTransactionAsync();
    Order order = await db.Orders.SingleAsync(o => o.OrderId == 999001);
    order.Status = "PaymentPending";
    await db.SaveChangesAsync();
    await tx.CommitAsync();
});

We saw this pattern in the transactions post of the EF Core series; here we saw why it's mandatory. If you use Dapper or plain ActiveX Data Objects for .NET (ADO.NET), you build the same thing with Polly. Polly's retry strategy catches only 1205 and puts exponential backoff and jitter between attempts:

ResiliencePipeline pipeline = new ResiliencePipelineBuilder()
    .AddRetry(new RetryStrategyOptions
    {
        ShouldHandle = new PredicateBuilder().Handle<SqlException>(ex => ex.Number == 1205),
        MaxRetryAttempts = 3,
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true,
        Delay = TimeSpan.FromMilliseconds(200),
        OnRetry = args =>
        {
            Console.WriteLine($"retry #{args.AttemptNumber + 1}: deadlock victim, rerunning the transaction");
            return default;
        }
    })
    .Build();

Task checkout = pipeline.ExecuteAsync(async ct =>
    await RunTransactionAsync("checkout", ReserveStock, markPaymentPending, meeting, ct)).AsTask();

RunTransactionAsync opens a new connection on every attempt, starts the transaction from scratch and runs the two UPDATEs. Output:

checkout: SqlException 1205: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
cancel: committed
retry #1: deadlock victim, rerunning the transaction
checkout: committed
final: Status=PaymentPending OnHand=37 Reserved=1

The retry worked and technically everything is fine. But look at the last line: the order had been cancelled and the book returned to stock, and the retried checkout moved the order to PaymentPending and reserved the stock again. The cancellation was lost. This is exactly the idempotency requirement of a retry: the transaction that runs again must assume the world changed in between and recheck the business rule. With a status check added to checkout's UPDATE:

UPDATE dbo.Orders SET Status = 'PaymentPending' WHERE OrderId = 999001 AND Status = 'Pending';
IF @@ROWCOUNT = 0 THROW 50001, 'Order 999001 is no longer Pending.', 1;

checkout: SqlException 1205: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
retry #1: deadlock victim, rerunning the transaction
cancel: committed
checkout: SqlException 50001: Order 999001 is no longer Pending.
checkout: gave up, the order was cancelled meanwhile
final: Status=Cancelled OnHand=37 Reserved=0

The retry ran again, but this time the business rule won: the order is cancelled and the reservation was rolled back. The same rule is even stricter for side effects outside the database. If you sent an e-mail or called the payment provider inside the transaction, the retry repeats those too; that's why they are done outside the transaction, with a mechanism such as an outbox.

What's Different in SQL Server 2019/2022

Deadlock detection, victim selection, DEADLOCK_PRIORITY and the structure of xml_deadlock_report are the same in SQL Server 2019 and 2022; the graphs in this post look the same on those versions. There are two differences. Time-bound Extended Events sessions with MAX_DURATION came with 2025; on older versions you stop the session yourself. In SQL Server 2025 with optimized locking on, the resources in writer-writer deadlocks are reported with an xactlock element instead of keylock; this element doesn't exist in 2019/2022. We'll see it in the next post. EF Core's transient error list doesn't depend on the server version; 1205 is retried when connecting to 2019 and 2022 as well.

Watch Out in Production

Track the number of deadlocks, but look at the trend rather than the absolute number. A few deadlocks a day are not a problem in a system with retries; the same graph repeating hundreds of times an hour is a design flaw. Grouping graphs by the index names in resource-list and the statement in inputbuf shows within minutes which pattern dominates.

Keep 1205 as a separate event in the application log. A request that succeeds after a retry shows no error to the user, but it is a symptom of a problem in the database. Polly's OnRetry or EF Core's execution strategy log is the place for it.

Limit the number of retries and use backoff. If the same two jobs restart at the same moment, they hit the same deadlock again; jitter keeps both sides from coming back at once. If you still get deadlocks after three attempts, you need a fix, not a retry.

Long transactions don't become victims, they create victims. Because SQL Server picks the one that used the least log, a big batch job is almost never the victim; the small, user-facing requests that run into it are. If users say "I clicked save and got an error", look at the big jobs running in the background.

Common Pitfalls

Retrying the statement. When 1205 arrives, the transaction has already been rolled back. If you rerun only the last statement, the change made by the first statement is lost and the data ends up inconsistent. The unit of retry is always the transaction.

"Solving" deadlocks with NOLOCK. It makes the reader-writer deadlock disappear, but the price is inconsistent reads; you get the same result with RCSI while reading correct data.

Investigating deadlocks only from application logs. The application only sees the victim; who the other side was and which index it waited on is only in the graph. Learn to read system_health.

Mixing TransactionScope with retries. Connections opened inside an ambient transaction may enlist in the same transaction in ways you don't expect during a retry, and if more than one connection is opened it can escalate to a distributed transaction. Draw the retry boundary around a single, explicitly managed transaction.

A Small Experiment

★☆☆ Run the two opening scripts with the cancellation started first. Does the victim change? Explain it by looking at the logused values.

★★☆ Write the upsert from pattern 2 with MERGE and run it in two sessions without hints. Do you get a deadlock, a primary key violation, or the right result? Then add WITH (HOLDLOCK) and try again.

★★★ Write PlaceOrderAsync with Dapper: the order row, the order items and the stock reservation in one transaction. Add a 1205 retry with Polly and guarantee with an idempotency key (for example the OrderNumber sent by the client and its unique index) that the retry doesn't create the same order twice.

In classic locking, resources stay locked for the whole transaction; both deadlocks and blocking come from that. SQL Server 2025 arrived with an option that changes this model. In the next post we turn on optimized locking, measure again how many locks the same UPDATE holds with TID (Transaction ID) locking and lock after qualification, and see where the result can change.