SQL Server Deadlocks and Snapshot Isolation

The marketplace API threw SqlException: transaction was deadlocked and chosen as the victim. The deadlock graph was a long reporting SELECT holding shared locks against a checkout UPDATE. CPU looked fine. Throughput was not.

Default Read Committed takes shared locks on reads and exclusive locks on writes. They do not mix. Under load, a dashboard scan and an order write deadlock. Read Committed Snapshot Isolation (RCSI) makes readers see the last committed version instead of waiting on the writer.

This page is that switch. Two Angular tabs overwriting one encounter is optimistic concurrency. Extra SQL round-trips are N+1. TempDB latch waits after you enable RCSI are TempDB contention.

What was blocking

Writer holds X on the order row. Reader wants S. Incompatible. Reverse it: a fat SELECT holds S across many pages, checkout wants X. One of them dies. EF surfaces it as a failed SaveChanges or a timed-out query. Retrying the same isolation level just rolls the dice again.

I confirm with the deadlock XML (extended events or system_health). If both sessions are writers on the same key, RCSI will not save you — use a rowversion. If one is a read, RCSI is the first lever.

Enable RCSI

ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;

That statement waits for connections to drain on some versions; I run it in a maintenance window on on-prem. Azure SQL Database already uses RCSI by default. If you are on Azure and still deadlock on reader/writer, look at the graph again before you “enable snapshot” a second time.

After RCSI, a reader during an UPDATE is sent to the version store in TempDB. Writers do not wait for readers. Reader/writer deadlocks of that shape drop.

Snapshot isolation as a session setting (SET TRANSACTION ISOLATION LEVEL SNAPSHOT) is a different, stricter versioning mode. I do not turn that on for every EF context. RCSI changes the database default for Read Committed, which is what EF uses unless you opted into something else.

Tradeoff: TempDB

Versions live in TempDB. Write-heavy fee imports plus RCSI plus one TempDB file on slow storage is how you swap deadlocks for allocation contention. Size TempDB, use multiple equal files, put it on fast disks. Azure hides some of this; a VM does not.

I do not enable RCSI on a misconfigured box as a Friday hero fix without watching PAGELATCH_UP on database id 2.

If checkout deadlocks against a report, contact me. The deadlock graph plus whether RCSI is on is the whole conversation.