Understanding SQL Server Deadlocks Through the Prisoner's Dilemma

A deadlock isn’t a bug in SQL Server, it’s SQL Server noticing that two transactions have each locked what the other needs and choosing one to kill so the other can finish. Nothing else would ever resolve it.

Everyone has written about deadlocks, so wanted to try something different.

The Prisoner’s Dilemma

Two suspects are arrested and held in separate rooms. Each is offered a deal to testify against the other and go free while the other serves the full sentence. If both stay silent, both serve a short sentence. If both testify, both serve a long one.

Neither can communicate with each other, so neither knows what the other will do.

The rational move for each suspect is to testify. So both testify, and both lose.

Your Transactions Are the Prisoners

Transaction A locks SalesOrderHeader and wants ProductInventory. Transaction B locks ProductInventory and wants SalesOrderHeader. Neither releases what it holds, so both wait.

SQL Server solves this by killing one of them. The victim’s work is rolled back, and the other transaction completes.

Producing a Deadlock

Here’s a way to produce a deadlock in AdventureWorks2022.

Open two separate query windows in SSMS (File > New Query, twice), both connected to AdventureWorks2022, and each window is its own connection. Run each statement one at a time, in the order shown left to right.

After Step 2, both sessions hold one lock each. After Step 3, Session 1 is blocked waiting on Session 2’s lock.

Step 4 creates whats called a “circular wait”: Session 1 is waiting on Session 2, and now Session 2 is waiting on Session 1. Neither can make progress because each holds what the other needs. SQL Server detects this cycle and immediately kills one session with error 1205.

Additionally, you can see the deadlocks in the system_health Extended Events deadlock graph:

This graph is the prisoner’s dilemma drawn by SQL Server. Just two nodes with arrows pointing at each other. The victim is the one SQL Server chose to kill.

The Cooperative Strategy

In the prisoner’s dilemma, if the prisoners could agree on a strategy beforehand, staying silent becomes the best choice. In SQL Server, that agreement is call “consistent lock ordering”. The repro deadlocked because Session 1 locked the tables in one order and Session 2 locked them in the opposite order. If both sessions always lock SalesOrderHeader before ProductInventory, the circular wait can’t form.

No circular wait so both complete.

That’s one fix, but if you can’t control the lock order, or deadlocking is still a recurring problem, there are a few other options.

Read Committed Snapshot Isolation

If you can’t control lock ordering, say the queries come from an ORM or a vendor application, consider enabling Read Committed Snapshot Isolation (RCSI) at the database level.

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

With RCSI enabled, reads stop taking the row and page locks they used to and instead read the last committed version of the row. I used to describe this as reads no longer taking shared locks, which is close but not quite it. The locking and row versioning guide says “Read operations require only the schema stability (Sch-S) table level locks and no page or row locks,” so there’s still a lock, it’s just not one that a writer is going to collide with. That removes the most common class of reader-writer deadlocks without changing any query code. Writers still block writers.

Those row versions get kept in tempdb, and the tempdb documentation lists version stores among the things tempdb holds, including “row versions that are generated by data modification transactions in a database that uses row versioning-based READ COMMITTED or SNAPSHOT isolation transactions.” So this moves work into tempdb rather than removing it, and on an instance where tempdb is already the bottleneck I’d want to know that before turning it on.

Optimized Locking (SQL Server 2025+ / Azure SQL)

If you’re on Azure SQL Database or SQL Server 2025, Optimized Locking changes how the engine holds row locks. Normally a transaction acquires a lock on every row it touches and holds all of them until commit. With Optimized Locking, each row lock is released as soon as the row is written, and the transaction instead holds a single lightweight lock on its Transaction ID (TID). Fewer locks held for less time means fewer opportunities for a deadlock cycle to form.

It requires two things to be enabled before it can be turned on:

-- Accelerated Database Recovery is a prerequisite
ALTER DATABASE AdventureWorks2022 SET ACCELERATED_DATABASE_RECOVERY = ON;

-- RCSI is what gives you the full benefit (Lock After Qualification)
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Then enable Optimized Locking:

ALTER DATABASE AdventureWorks2022 SET OPTIMIZED_LOCKING = ON;

On Azure SQL it’s already on by default. On SQL Server 2022 and older it isn’t available.

Is it the best option? If you’re on a version that supports it and you already have RCSI on, as far as I can tell, enabling it is low-risk and worth doing. Writer-writer deadlocks on the same rows can still happen. Lock ordering fixes the structural cause. Optimized Locking reduces how often that structure becomes a problem.

Retry Logic in the Application

I actually find this solution to be perfectly fine for most systems, and I see this out in the wild more often than RCSI.

Since the 1205 error message is something the application can read it’s easy to retry. SQL Server rolls back the victim and leaves the other transaction intact. A simple retry loop in the application handles it.

If you’re hitting deadlocks often enough that retries matter, the actual problem is still there and worth addressing. It just depends on how you define “often” and “matter”. If it’s a rare occurrence and the cost of retries is low, this might be good enough.

Controlling the Victim

I had these two rules in the wrong order for a long time. I thought SQL Server picked whichever transaction was cheapest to roll back, and that SET DEADLOCK_PRIORITY adjusted that calculation, but the priority is checked first and the cost only settles ties. The documentation says “If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim,” and when the priorities match, “the cost is determined by comparing the number of log bytes written to that point in each transaction.”

The range is -10 to 10 and NORMAL is the default, which sits at 0. So if nobody has set a priority, every session is at 0 and they’re all being compared on log bytes, which is the situation I’m usually looking at, and it’s why the victim seems arbitrary until you read the “Log Used” value in the deadlock graph.

-- Make this session the preferred deadlock victim
SET DEADLOCK_PRIORITY LOW;   -- equivalent to -5

-- Protect this session from being chosen
SET DEADLOCK_PRIORITY HIGH;  -- equivalent to 5

-- Set a specific numeric value
SET DEADLOCK_PRIORITY -3;

This is useful when one transaction is cheap to retry and another is expensive. Set the cheap one to LOW and SQL Server will consistently sacrifice it, keeping the expensive one alive. It’s an interesting solution but I’ve actually never seen it actually used in production. That’s not to say it’s bad, just that it’s not common.

The thing I keep coming back to is that two transactions acquiring the same resources in opposite order will produce a deadlock eventually, and everything else is working around that rather than fixing it. Retry logic, deadlock priority, shorter transactions, all of it makes the cycle less likely or less expensive without making it impossible. Fixing the order is the only change that removes it.

In practice I’ve spent more time on retry logic than on ordering, because the ordering lives in application code somebody else owns and the retry lives where I can reach it. I don’t think that’s the right trade, it’s just the one that was available.