Read Committed Snapshot Isolation (RCSI)

Read Committed Snapshot Isolation, or RCSI, is a version-based implementation of the READ COMMITTED isolation level. In traditional READ COMMITTED, readers acquire shared locks and can be blocked by writers holding exclusive locks. With RCSI enabled, SQL Server stores previous versions of modified rows in TempDB's Version Store. Readers access the last committed version of a row rather than waiting for active transactions to finish. This eliminates most reader-writer blocking while still preventing dirty reads. Unlike Snapshot Isolation, which provides a transaction-level snapshot, RCSI provides a statement-level snapshot, meaning each statement sees the database as it existed when that statement began. RCSI is enabled at the database level and generally requires no application changes, making it a popular solution for high-concurrency OLTP systems. The tradeoff is increased TempDB usage and additional version management overhead. In production environments, RCSI is frequently used to improve concurrency, reduce blocking, and enhance application responsiveness while maintaining data consistency.

By default SQL Server is read committed. When we run it acquires Shared Locks (S) and if another session is updating it holds Exclusive Lock (X). Since S Lock  vs  X Lock are incompatible in this case Reader Waits it make blocking the process.

Instead of waiting Reader à Reads Older Version à No Blocking. This is RCSI. RCSI prevents Dirty Reads because it return the old value not uncommitted value while nolock return the uncommitted data.

Enable RCSI

We see the database details where it is enabled or not using the below query.

SELECT     name,     snapshot_isolation_state_desc,     is_read_committed_snapshot_on FROM sys.databases where name='TestDB'

 

In the TestDB it is not enabled. Now enabling it.

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON;

Now it is enabled.

Now see the example

Before  READ_COMMITTED_SNAPSHOT OFF;

In session 1 we are updating a record and not yet committed.

begin Tran  update Account set Balance=70000.00 WHERE AccountNumber='SB100001'  --commit;

Record is updated. Transaction is not yet committed.

Now open in other window and select this record.

If we use nolock then it will return uncommitted data.

In this case our process is blocking.

Now we are enabling RCSI on this database.

Updating the record on one session

begin Tran  update Account set Balance=80000.00 WHERE AccountNumber='SB100001'  --commit;

Record updated. Now running the select on other session.

See here without blocking we are getting the old value meanwhile using nolock we can see the uncommitted values as well.

No we are committed this transaction.

Now both select query will return the same value.

Benefits of RCSI

Below are the benefit of RCSI

Ø  Eliminates Reader-Writer Blocking

Ø  Better User Experience

Ø  No Code Changes Required

Ø  Prevents Dirty Reads

Ø  Improves Throughput

Ø  Reduces Lock Contention

Ø  Reduces Deadlocks

Ø  Better Reporting Performance

Ø  Excellent for Mixed Workloads

Ø  Safer Alternative to NOLOCK

The Biggest Benefit on below case

Ø  Heavy OLTP systems

Ø  Many concurrent users

Ø  Reporting queries running on OLTP database

Ø  Frequent blocking issues

Ø  Applications using default READ COMMITTED

  

Feature	READ COMMITTED	RCSI
Reader Blocks Writer	Yes	No
Writer Blocks Reader	Yes	No
Dirty Reads	No	No
Uses TempDB	No	Yes
Code Changes Required	No	No
Throughput	Lower	Higher
Concurrency	Lower	Higher

 Difference Between Read Committed and RCSI

Feature	Read Committed	RCSI
Shared Locks	Yes	Mostly No
Reader Blocks Writer	Yes	No
Writer Blocks Reader	Yes	No
Dirty Reads	No	No
Uses TempDB	No	Yes
Uses Row Versions	No	Yes