Understanding SET TRANSACTION ISOLATION LEVEL READ COMMITTED When RCSI is Enabled in SQL Server
If you’ve ever dealt with concurrency issues in SQL Server—blocking, deadlocks, or frustrated users waiting on queries—you’ve probably heard about Read Committed Snapshot Isolation (RCSI). It’s a game-changer for many OLTP workloads. But what exactly happens when you run SET TRANSACTION ISOLATION LEVEL READ COMMITTED in a database where RCSI is turned on?
Let’s break it down.
What is RCSI?
Read Committed Snapshot Isolation is a database-level option in SQL Server that changes how the default READ COMMITTED isolation level behaves. You enable it with:
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON;Once enabled, SQL Server starts using row versioning for reads under the READ COMMITTED isolation level instead of the traditional locking mechanism.
What Does SET TRANSACTION ISOLATION LEVEL READ COMMITTED Do With RCSI Enabled?
When RCSI is ON:
- The session uses row versioning for SELECT statements.
- No shared locks are acquired during reads.
- Readers see a transactionally consistent snapshot of the data as it existed at the start of each individual statement (not the entire transaction).
- This eliminates dirty reads (you never see uncommitted changes).
- It still allows non-repeatable reads and phantoms—just like standard READ COMMITTED.
- Key benefit: Readers no longer block writers, and writers no longer block readers. Concurrency improves significantly.
Row versions are maintained in tempdb, so keep an eye on tempdb growth and performance.
Comparison: READ COMMITTED With RCSI OFF vs. ON
| Behavior | RCSI OFF (Default Locking) | RCSI ON (Row Versioning) |
|---|---|---|
| Locking during reads | Shared locks acquired | No shared locks |
| Blocking | Readers can block writers (and vice versa) | No read-write blocking |
| Consistency | Statement-level (committed data only) | Statement-level snapshot (committed data only) |
| Dirty reads | Prevented | Prevented |
| Non-repeatable reads/phantoms | Possible | Possible |
| Performance impact | Higher blocking, potential deadlocks | Higher concurrency, tempdb overhead |
Important Note: This Is Not Full Snapshot Isolation
Don’t confuse this with SNAPSHOT isolation level.
- RCSI affects READ COMMITTED behavior.
- True SNAPSHOT isolation (transaction-level consistency) requires:
ALTER DATABASE [YourDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;And explicitly setting:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;With RCSI, you’re getting the same logical guarantees as classic READ COMMITTED, but with far better concurrency.
When Should You Enable RCSI?
RCSI is often recommended for databases with:
- High read-write contention
- Frequent blocking issues
- Reporting queries running alongside OLTP transactions
Just monitor tempdb—version store cleanup happens automatically, but long-running transactions can cause bloat.
Conclusion
Enabling RCSI transforms SET TRANSACTION ISOLATION LEVEL READ COMMITTED (or the default isolation level) into a non-blocking, versioned read mechanism that maintains standard READ COMMITTED semantics while dramatically reducing blocking.
It’s one of the easiest ways to boost concurrency in SQL Server without changing application code.
Have you enabled RCSI in production? What’s your experience been like?