SQL Server Interview Question #253

What is READ_COMMITTED_SNAPSHOT (RCSI)?

Advanced Transactions & Concurrency Senior Advanced

Detailed Explanation

READ_COMMITTED_SNAPSHOT is a database option that changes READ COMMITTED so many reads use committed row versions instead of shared data locks.

This can significantly reduce reader-writer blocking while still preventing dirty reads. Writers still take locks, and writer-writer conflicts remain.

RCSI uses SQL Server's versioning infrastructure, so tempdb or the Persistent Version Store depending on platform/features must be monitored appropriately. The application should also be tested because concurrency behavior changes.

Code Example

ALTER DATABASE SalesDb
SET READ_COMMITTED_SNAPSHOT ON;