SQL Server Deadlock, Slow-Cooked: A Recipe That Serves Two Sessions

A SQL Server deadlock, written up the way a recipe card is written. Ingredients, prep, method, and what to do when the sauce splits. It reproduces on SQL Server 2025 every time, and I have the timestamps and the deadlock graph to prove it.

Prep for a SQL Server deadlock

CREATE TABLE dbo.Pantry (id INT PRIMARY KEY, item VARCHAR(20));
CREATE TABLE dbo.Fridge (id INT PRIMARY KEY, item VARCHAR(20));
INSERT dbo.Pantry VALUES (1, 'flour'), (2, 'sugar');
INSERT dbo.Fridge VALUES (1, 'butter'), (2, 'eggs');

That’s it. Nothing exotic. This dish is made from things every kitchen has.

Method

Session A. Begin a transaction. Update the pantry. Wait five seconds, which stands in for “do something else in the same transaction”. Then update the fridge.

BEGIN TRANSACTION;
UPDATE dbo.Pantry SET item = 'flour (A)' WHERE id = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.Fridge SET item = 'butter (A)' WHERE id = 1;
COMMIT;

Session B. Start about a second later. Same thing, the other way round. Fridge first, then pantry.

BEGIN TRANSACTION;
UPDATE dbo.Fridge SET item = 'butter (B)' WHERE id = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.Pantry SET item = 'flour (B)' WHERE id = 1;
COMMIT;

Now wait. Each session holds one lock and wants the other’s. Neither can finish. Neither can give up.

What happened, to the millisecond

Both sessions wrote to a log table as they went. Here’s the log, straight from the lab.

09:57:01.324  A  begin
09:57:01.324  A  holds Pantry, waiting 5 s
09:57:02.744  B  begin
09:57:06.332  A  asking for Fridge
09:57:09.766  B  ERROR 1205: Transaction (Process ID 71) was deadlocked
                 on lock resources with another process and has been
                 chosen as the deadlock victim. Rerun the transaction.
09:57:09.767  A  committed

Notice what’s missing. B’s line “holds Fridge, waiting 5 s” was written, and it isn’t there.

That’s because B wrote it inside its transaction, and B’s transaction was rolled back. The log entry died with it. Even the evidence gets undone.

The graph

SQL Server records every deadlock in the system_health session. I read it back from the event file.

victim         the process whose transaction started at 09:57:02.740 (B)
processes      2
lock 1         MagLab.dbo.Pantry   KEY lock
lock 2         MagLab.dbo.Fridge   KEY lock
p1 waits for   KEY: 15:72057594134003712
p2 waits for   KEY: 15:72057594134069248

Two processes, two key locks, each waiting for the other’s. The monitor chose B. This summary of the graph does not record why, so I’m not going to guess.

A’s first attempt to touch the fridge was at 06.332. The kill came at 09.766. That’s 3.4 seconds of A waiting, which is what a deadlock tastes like from the application. B’s own wait sits inside that, starting the moment it asked for the pantry.

The finished dish

Pantry, id 1:  flour (A)
Fridge, id 1:  butter (A)

A got everything. B got error 1205 and nothing else. If B’s application didn’t retry, B’s work is gone. Nobody was told except the one connection that raised the error.

If the sauce splits: how to send it back

Same order, every time. The recipe works only because A goes pantry then fridge and B goes fridge then pantry. Make every code path take the tables in the same order and the circle can’t form. This is the fix that costs nothing.

Shorter transactions. The five-second wait is the “other work” between the two updates. Cut it and the window closes. Never hold a transaction open across a network call or a user prompt.

Retry on 1205. The error message says “Rerun the transaction” and means it. A victim that retries once usually succeeds, because the other side has finished by then.

Read the graph before you guess. The system_health session has it. Two locks, two statements, one victim. You don’t need a theory when you have the receipt.

Cook’s notes

You can reheat this dish whenever you like. I ran it three times while writing this and it deadlocked on every attempt, with B the victim each time.

It also scales. Replace the two small tables with two large ones and the WAITFOR with a slow report. You have the version that happens at 2 a.m.

If your system serves this dish weekly, I’ll find which two dishes are being cooked in opposite order.

A deadlock is not bad luck, it is a recipe two people followed from opposite ends.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.