Reducing Deadlocks with Row-Versioning Isolation Levels

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:

Reads never wait for writes
Writes never wait for reads

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:

No read locks
No read/write blocking
Even write-write conflicts are detected at commit time (update conflict error)

Best for:

Long-running read transactions
Reporting queries
ETL reads without blocking production

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:

SELECT reads row versions → no shared locks taken

✓ 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:

200+ deadlocks per hour
Reporting queries blocking updates
Long-running transactions from scheduled tasks

Actions taken:

Enabled RCSI
Added index on (OrderId, Status)
Broke a nightly UPDATE of 5M rows into batches
Moved reporting to SNAPSHOT isolation

Result:

Deadlocks reduced by 95%
Avg query latency reduced by 40%
Applicaton throughput increased by 30%

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.