1. Introduction
Deadlocks are one of the most painful performance problems in OLTP systems. They occur when two (or more) sessions block each other in a circular wait—each holding locks the other needs.
Row-versioning–based isolation levels in SQL Server (like READ COMMITTED SNAPSHOT (RCSI) and SNAPSHOT) dramatically reduce deadlocks by removing many reader–writer conflicts without lowering consistency guarantees.
This article is a fully practical guide covering:
Why deadlocks occur
What row versioning is
RCSI vs SNAPSHOT
How they reduce deadlocks
How to enable them safely
Real-world patterns, scripts, and troubleshooting
2. Why Deadlocks Happen (Quick Refresher)
2.1 A typical deadlock pattern
Example:
Session A:
UPDATE Orders SET Status = 'Paid' WHERE Id = 1;
Session B:
SELECT * FROM Orders WHERE Id = 1;
UPDATE Payments SET Confirmed = 1 WHERE OrderId = 1;
Session A holds a write lock, B holds a read lock, and next each wants the other’s lock.
SQL Server detects the cycle and kills one.
2.2 Common causes
Long-running read queries blocking writes
Writes blocking reads
Poor indexing → forced table scans → many locks acquired
Serializable isolation used incorrectly
Application patterns causing lock escalation
Wide updates touching many rows
Most OLTP deadlocks are caused by reader–writer blocking, which row versioning eliminates.
3. What is Row Versioning?
SQL Server stores old versions of modified rows in TempDB.
Readers can access these consistent snapshots without blocking writers.
3.1 How versioning works
When a row is updated:
Old version goes to TempDB version store
Writers continue normally
Readers (in RCSI/SNAPSHOT modes) read the previous version, not the locked row
Thus:
This eliminates 60–90% of deadlocks in real-world systems.
4. Row-Versioning Isolation Levels
There are two major options:
4.1 READ COMMITTED SNAPSHOT (RCSI)
Makes standard READ COMMITTED behave like snapshot reads
Automatically uses row versions for all SELECT statements
Does NOT require explicit BEGIN TRAN…SNAPSHOT
Enable:
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
Effects:
No reader/writer blocking
Writers still block writers
No application change needed
Minimal behavior shift from default RC
Most recommended for OLTP systems.
4.2 SNAPSHOT Isolation
Explicit snapshot transactions:
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON;
Application must request SNAPSHOT:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT ...;
UPDATE ...;
COMMIT;
Effects:
Best for:
5. How Row Versioning Reduces Deadlocks
✓ Eliminates reader–writer deadlocks
Under normal READ COMMITTED:
SELECT acquires share locks
UPDATE acquires exclusive locks
If both access same row(s), deadlock risk is high
Under RCSI/SNAPSHOT:
✓ Prevents cascading deadlock chains
Long-running reports scanning many rows often cause deadlocks under RC.
With RCSI/SNAPSHOT, they always read from consistent row versions.
✓ Improves throughput → fewer lock waits → fewer cycles
More concurrency = smaller locking footprint = fewer deadlock chains.
✓ Reduces lock escalation
Heavy scan queries no longer hold S locks on millions of rows.
6. Enabling RCSI Safely (Step-by-Step)
Step 1 – Check if versioning is already enabled
SELECT
name,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDB';
Step 2 – Enable RCSI
This requires no downtime—but cannot run inside a transaction.
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
Step 3 – Monitor TempDB growth
Row versioning uses TempDB heavily.
Monitor version store usage:
SELECT * FROM sys.dm_tran_version_store_space_usage;
7. Testing Deadlock Reduction
Before RCSI
Open two sessions:
Session 1:
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 1;
WAITFOR DELAY '00:00:10';
COMMIT;
Session 2:
SELECT * FROM Accounts WHERE Id = 1; -- blocks
UPDATE Accounts SET LastUpdated = GETDATE() WHERE Id = 1;
This often deadlocks.
After enabling RCSI
Run the same script again.
Now:
SELECT reads row version
No shared lock taken
UPDATE is never blocked
No deadlock
8. When NOT to Use RCSI
RCSI is excellent—but not always perfect.
Avoid or evaluate carefully when:
You depend on lock-based consistency (rare)
You use triggers that rely on row lock behavior
TempDB is already extremely stressed
You use third-party apps that expect blocking semantics
You have massive write bursts (many versions generated)
TempDB should be on SSD and sized properly.
9. Combining Versioning with Good Practices
Row versioning is not a magic bullet—pair it with:
9.1 Short transactions
Never keep user-facing transactions open.
9.2 Proper indexing
Index all lookup columns:
Foreign keys
WHERE conditions
JOINS
Filtering columns
9.3 Avoid serializable unless absolutely required
Serializable = deadlock magnet.
9.4 Use optimistic concurrency
Add rowversion/timestamp columns.
9.5 Avoid large batch updates
Rewrite:
UPDATE bigtable SET ...
Into chunks:
UPDATE TOP (1000) bigtable SET ...
10. Real-World Case Study
A retail ERP system had:
Actions taken:
Enabled RCSI
Added index on (OrderId, Status)
Broke a nightly UPDATE of 5M rows into batches
Moved reporting to SNAPSHOT isolation
Result:
11. Monitoring Deadlocks After Migration
Query deadlock history
SELECT *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_DEADLOCK';
Or enable extended events:
CREATE EVENT SESSION Deadlock_Monitor
ON SERVER
ADD EVENT sqlserver.lock_deadlock
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION Deadlock_Monitor ON SERVER STATE = START;
12. Summary
Row-versioning isolation levels—especially READ COMMITTED SNAPSHOT (RCSI)—are one of the most effective ways to reduce deadlocks in high-concurrency SQL Server systems.
They:
Prevent reader–writer conflicts
Avoid shared locks
Improve throughput
Remove many circular lock sequences
Require zero code changes (RCSI variant)
For most OLTP databases with frequent deadlocks, enabling RCSI is one of the highest-impact, lowest-effort optimizations available.