RCSI and snapshot isolation

In one line: under the default isolation level, readers wait for writers. NOLOCK “fixes” that by reading data that may never have existed. Read Committed Snapshot Isolation (RCSI) fixes it properly by reading the last committed version of the row. SNAPSHOT goes further. Both cost row space and tempdb.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/rcsi-snapshot-isolation on GitHub.

What you’ll see

  • Readers blocked on LCK_M_S behind a writer. In the demo, a simple read waited 1,966 ms for an uncommitted update.
  • WITH (NOLOCK) added everywhere to make the blocking go away.
  • Reports that don’t add up because each query in them saw different data.

Why it happens

Under locking READ COMMITTED, a reader needs a shared lock, and a writer’s exclusive lock blocks it until commit. NOLOCK skips the lock and reads whatever is on the page, including changes that will be rolled back. In the demo, it read a balance of 1,100 that was then rolled back and never existed.

With RCSI on, READ COMMITTED reads row versions kept in tempdb instead of taking shared locks. The same reader didn’t wait at all and read the last committed value, 1,000.

What to do

  • Turn on RCSI (ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON) when readers block on writers. It’s what people usually want when they reach for NOLOCK. Test first: code that relied on readers blocking can behave differently.
  • Use SNAPSHOT isolation for a transaction that must see one consistent point in time across several queries. Under RCSI each statement sees the latest committed data. Under SNAPSHOT the whole transaction does.
  • Catch Msg 3960 and retry under SNAPSHOT. If the row you’re updating was changed after your transaction started, SQL Server refuses the update rather than overwrite a change you never saw.

What doesn’t help

  • NOLOCK. It’s not a performance hint. It means dirty reads.
  • Expecting RCSI to stop writers blocking writers. A second writer still waited 1,962 ms in the demo.
  • Ignoring the cost:
    • every row modified while versioning is on gains a 14-byte tag
    • on full pages that means page splits: 99.6% full went to 68.7%
    • every update copies the old row into tempdb’s version store, which took 19 MB for a 200,000-row update
    • a long-running transaction stops the version store being cleaned up

Check your server

-- Which databases use row versioning, and how much version store each is holding.
SELECT d.name,
       d.is_read_committed_snapshot_on,
       d.snapshot_isolation_state_desc,
       VersionStoreMB = CAST(ISNULL(v.reserved_space_kb, 0) / 1024.0 AS decimal(10, 1))
FROM sys.databases AS d
LEFT JOIN sys.dm_tran_version_store_space_usage AS v ON v.database_id = d.database_id
ORDER BY VersionStoreMB DESC;

See it happen

The rcsi-snapshot-isolation demo stages a reader and a writer on the same row from one unattended script. It covers locking READ COMMITTED, NOLOCK, RCSI, SNAPSHOT and an update conflict, then measures the row-size and version-store cost.

Related: blocking chains · deadlock patterns · Accelerated Database Recovery · optimized locking (SQL Server 2025)

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights