SQL Server Isolation Levels and Deadlocks: READ COMMITTED, SNAPSHOT, and Prevention

The “I” in ACID (previous lesson) said concurrent transactions don’t see each other’s uncommitted changes — but how much isolation, exactly, is itself a tunable setting with real tradeoffs. This lesson covers what each level actually permits, and how misunderstanding isolation is precisely how deadlocks catch people off guard.

The Isolation Levels

-- Setting isolation level for a session
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Must be enabled at the DATABASE level first, or SNAPSHOT requests are rejected
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

How a Deadlock Forms

Each transaction holds a lock the other needs — a circular wait. SQL Server automatically detects this and kills one transaction (the “deadlock victim,” chosen by lowest rollback cost by default, meaning the transaction that’s done the least work so far is usually the one sacrificed).

-- Reproducing this exact scenario needs two sessions running simultaneously:
-- Session 1:
BEGIN TRANSACTION;
UPDATE dbo.BankAccount SET balance = balance - 100 WHERE account_id = 1; -- locks account 1
-- (pause here, run Session 2's first line, then continue)
UPDATE dbo.BankAccount SET balance = balance + 100 WHERE account_id = 2; -- wants account 2, blocked

-- Session 2 (run its first line while Session 1 is paused above):
BEGIN TRANSACTION;
UPDATE dbo.BankAccount SET balance = balance - 50 WHERE account_id = 2; -- locks account 2
UPDATE dbo.BankAccount SET balance = balance + 50 WHERE account_id = 1; -- wants account 1 → deadlock

The Real Fix

Always access shared tables/rows in the same consistent order across every transaction in your application (e.g. always update the lower account_id first). If every transaction acquires locks in the same sequence, the circular-wait condition can’t form — in the reproduction above, if both sessions updated account 1 before account 2 every time, neither would ever end up waiting on a lock the other already held while itself holding something the other needed.