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:

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

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:

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)

Enable:

ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

Effects:

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:

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:

8. When NOT to Use RCSI

RCSI is excellent—but not always perfect.

Avoid or evaluate carefully when:

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:

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:

  1. Enabled RCSI

  2. Added index on (OrderId, Status)

  3. Broke a nightly UPDATE of 5M rows into batches

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

For most OLTP databases with frequent deadlocks, enabling RCSI is one of the highest-impact, lowest-effort optimizations available.