Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

“Read committed” sounds safe: you only ever see committed data. But it says nothing about when the data was committed. A single query can read some rows before another transaction’s change and some rows after it. The row-versioning options give you a definite point in time instead, without readers blocking writers.

The problem with plain read committed

Under read committed with locking (the SQL Server default), a shared lock is held only while each row or page is read, then released. A long query moving through a table can:

• read a row, which is then moved by an update and read again further on (counted twice);
• miss a row that is moved behind the point it has already read;
• add up balances where half reflect a transfer and half do not, so the total is wrong even though every value was committed.

This is called inconsistent analysis. It is not dirty reading: every value was committed, just at different moments. Readers also block behind writers’ exclusive locks, which is the blocking most systems complain about.

Two kinds of consistency

• Statement-level read consistency: every row a statement reads is as it was when that statement started. This is what READ_COMMITTED_SNAPSHOT (RCSI) gives.
• Transaction-level read consistency: every statement in the transaction sees the data as it was when the transaction first read data. This is what SNAPSHOT isolation gives.

Both read older committed versions of rows from the version store in tempdb instead of waiting for locks, so readers do not block writers and writers do not block readers. Writers still block other writers.

Turning them on

-- RCSI: changes the meaning of read committed for every session in the database.
-- Needs a moment with no other connections, so use ROLLBACK IMMEDIATE in a change window.
ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- SNAPSHOT: allowed in the database, but only used by sessions that ask for it.
ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION ON;

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'YourDB';

A session then uses snapshot isolation with SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Seeing the difference

Session 1 changes a row and leaves the transaction open:

BEGIN TRAN;
UPDATE dbo.Account SET Balance = 999 WHERE ID = 1;
-- leave this open

Session 2, with RCSI off, waits on LCK_M_S until session 1 finishes. With RCSI on, it returns straight away with the last committed balance:

SELECT Balance FROM dbo.Account WHERE ID = 1;

To see transaction-level consistency, session 2 uses SNAPSHOT and reads twice:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT Balance FROM dbo.Account WHERE ID = 1;   -- the committed value
    -- session 1 now commits its change
    SELECT Balance FROM dbo.Account WHERE ID = 1;   -- still the same value under SNAPSHOT
COMMIT;

Under RCSI the second SELECT would return the new value, because each statement takes a fresh snapshot.

Update conflicts under SNAPSHOT

If a snapshot transaction tries to change a row that another transaction changed and committed after the snapshot started, it fails with error 3960 and is rolled back. Code that uses SNAPSHOT for writes must catch error 3960 and retry. RCSI does not raise this error: an update under RCSI reads the current committed row, not the old version.

Which to use

• RCSI suits most OLTP systems and removes most reader/writer blocking without code changes. It is the default in Azure SQL Database.
• SNAPSHOT suits reports and reconciliations that run several queries and need them to agree with each other.
• Test before switching on RCSI. Code that relies on readers blocking, such as “read the next number, then update it” patterns without UPDLOCK, can give different results.

The cost: the version store

Every update and delete keeps the old row version in tempdb while any transaction might need it, and changed rows grow by 14 bytes for the version pointer. A long-running transaction stops the version store from being cleaned up, and tempdb can fill.

-- Version store size per database (SQL Server 2017 onwards)
SELECT DB_NAME(database_id) AS database_name,
       reserved_page_count * 8 / 1024 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_page_count DESC;

-- The oldest snapshot transactions holding versions
SELECT TOP (5) session_id, transaction_id, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

From my SQL Server DBA handbook.