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

Level	Notes
READ UNCOMMITTED	Fastest, allows dirty reads (seeing another transaction’s uncommitted, possibly-about-to-be-rolled-back changes) — rarely appropriate; this is what the SQL-hint `WITH (NOLOCK)` effectively opts a single query into
READ COMMITTED (default)	Never reads uncommitted data — SQL Server’s out-of-the-box behavior for every connection unless explicitly changed
SERIALIZABLE	Strictest, most blocking — behaves as if transactions ran one at a time, at real concurrency cost
SNAPSHOT	Row versioning — readers never block writers, and writers never block readers, at the cost of tempdb overhead for storing row versions

-- 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.

Common mistake: Treating deadlocks as a database bug to “fix” with more indexes or a bigger server. Deadlocks are fundamentally an application logic problem — the fix lives in the order your code acquires locks, not in the database’s configuration or hardware.

Practice tip: If you have two query windows available, try reproducing the deadlock above exactly as written, pausing Session 1 right after its first UPDATE to give Session 2 time to run. Watch SQL Server pick a victim and return error 1205 to one of the two sessions — seeing the actual deadlock error is worth more than reading the theory.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

Capturing SQL Server Deadlock Graphs from system_health: A Real Diagnostic Walkthrough

You already know what a deadlock is. Here’s how to actually retrieve the deadlock graph after the fact — without having set up a trace in advance — because system_health is running by default on every SQL Server instance.

Pulling Deadlock Graphs You Never Explicitly Captured

SELECT CAST(event_data.value('(event/data/value)[1]', 'VARCHAR(MAX)') AS XML) AS deadlock_graph,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time
FROM (
    SELECT XEventData.query('.') AS event_data
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
        WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
    ) AS Data
    CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEventData)
) AS tab(event_data);

Because system_health runs continuously by default, this query can retrieve deadlocks that happened before you even knew there was a problem — no advance trace setup required.

Reading the Graph

The victim-list element tells you which process SQL Server killed. Cross-reference the surviving process’s SQL text against the killed one’s — this is exactly how you confirm whether inconsistent access order (the classic cause) is really what happened.

Beyond Theory: A Deadlock Involving a Table Scan

Not every deadlock is the classic “two transactions, opposite order” case from Course 2. A single transaction doing a large table scan can deadlock against a small, targeted UPDATE if the scan acquires and holds shared locks across a wide range while the update needs an exclusive lock inside that range. The fix here isn’t reordering — it’s often reducing the scan’s lock footprint with a better index (tying directly back to Module 2 and 3).

Enjoyed this?

Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

Tag: Deadlocks