What is Read Committed Snapshot Isolation (RCSI)?
Quick Answer
RCSI is a database-level option that changes the default Read Committed isolation to use row versioning instead of shared read locks. Readers see the last committed version of each row as of the statement start, so readers don't block writers and writers don't block readers — dramatically reducing blocking, at the cost of extra tempdb usage for the version store.
RCSI switches the default Read Committed level to use row versioning. Instead of taking shared locks, a reader just reads the most recently committed version of each row, which SQL Server keeps in a version store in tempdb. The effect is that readers and writers stop blocking each other, so a lot of contention just disappears. The costs are more tempdb activity, and you have to keep an eye on long transactions because they force old versions to be retained. It's on by default in Azure SQL and it's usually the right move for a read-heavy on-prem OLTP database.
- Enabled with
ALTER DATABASE db SET READ_COMMITTED_SNAPSHOT ON;(needs no active connections briefly). - Versions of changed rows are kept in the tempdb version store until no transaction needs them — size tempdb accordingly and watch long-running transactions that hold versions.
- RCSI vs SNAPSHOT isolation: RCSI is statement-level consistency (each statement sees a fresh snapshot); SNAPSHOT isolation is transaction-level (the whole transaction sees one snapshot and can hit update conflicts).
- It's the default in Azure SQL Database and a very common recommendation for read-heavy OLTP on-prem.