NOLOCK, RCSI or SNAPSHOT? SQL Server Isolation Levels in Practice

That is the familiar part. Less familiar: even without NOLOCK, under plain READ COMMITTED, we saw rows read twice or missed entirely. And if you switch to read committed snapshot isolation (RCSI) because of that, you solve these problems but change what triggers and validation logic take to be true. This page uses lab code to show which isolation level allows which wrong results and how to make the switch safely.

In 60 seconds

• Write locks apply at every isolation level until commit. The isolation level only governs how reads work.
• NOLOCK does not just read uncommitted data. It can also skip or duplicate rows that were committed years ago.
• READ COMMITTED is not a point-in-time view. If rows move within an index during a scan, they are missing or appear twice.
• RCSI gives every statement the committed state as of its start. Readers no longer wait. Code that reads, checks and then writes needs a closer look for that.
• SNAPSHOT is a decision of its own: consistent across the whole transaction, with an abort on update conflicts (error 3960).

What NOLOCK really risks

It is well known that NOLOCK reads data that is later rolled back. Something else weighs more heavily: without locks, SQL Server may read a table in the order of storage allocation instead of key order (an allocation-order scan). If a page splits during the read, rows move to a new page. If that page lies behind the read position, the scan reads the rows a second time. If it lies ahead, the scan misses them.

Lubor Kollar of the SQL Server Customer Advisory Team described this in 2007, and Paul White explained it in 2015. We reproduced it with our own code on all current versions: 20,000 stable rows that are never changed, on full pages. A writer inserts new rows in between. Every reader must see exactly 20,000 stable rows.

-- Abridged from the lab codeCREATE TABLE dbo.N1 (    id     int       NOT NULL CONSTRAINT PK_N1 PRIMARY KEY CLUSTERED,    stabil bit       NOT NULL,   -- stabil = stable    pad    char(700) NOT NULL CONSTRAINT DF_N1_pad DEFAULT 'x');-- 20,000 stable rows with id 10, 20, 30, …, then REBUILD with FILLFACTOR = 100 -- Writer, in autocommit in a loop: a new row between two stable onesINSERT dbo.N1 (id, stabil) VALUES (@id, 0); -- ReaderSELECT id INTO #r FROM dbo.N1 WITH (NOLOCK) WHERE stabil = 1;SELECT COUNT(*) AS gelesen, COUNT(DISTINCT id) AS verschieden FROM #r;   -- gelesen = read, verschieden = distinct; expected: 20,000 and 20,000

Two cross-checks stayed error-free in all roughly 2,700 reads: the same NOLOCK scan with a range predicate on the key (SQL Server then reads in key order) and reading under READ COMMITTED with locks. New compared with the older sources: the duplicates arose from inserts alone, with no UPDATE at all.

How often will this hit you? Our load was extreme: tens of thousands of inserts per run, nearly every one splitting a page. In production, the frequency depends on how many pages split while a scan is running. On tables that do not change while they are being read, nothing happens. But for reports on live transactional data, “rarely” is not a good argument, and nobody notices the error.

Error 601 (“Could not continue scan with NOLOCK due to data movement”) was not triggered in roughly 102,000 NOLOCK scans with growing and deleted rows. The counts were still wrong in 8 to 19 percent of the reads. The sources describe 601 as rare; that does not rule it out.

READ COMMITTED is not a point-in-time view either

Under classic READ COMMITTED, a scan briefly takes a read lock for each row and releases it immediately. If another transaction changes the index key of a row the scan has already read and moves it forward into the not-yet-read range, the scan reads it a second time. If a not-yet-read row moves backward, it is missing. Paul White described this in 2014; we found no runnable script anywhere.

So we wrote one:

-- Abridged from the lab codeCREATE TABLE dbo.K3 (    schluessel int       NOT NULL CONSTRAINT PK_K3 PRIMARY KEY CLUSTERED,   -- schluessel = key    kontonr    int       NOT NULL CONSTRAINT UQ_K3_kontonr UNIQUE NONCLUSTERED,   -- kontonr = account number    pad        char(400) NOT NULL CONSTRAINT DF_K3_pad DEFAULT 'k');-- 50,000 accounts, keys 2, 4, 6, … -- Writer, in autocommit in a loop: one account gets a new keyUPDATE dbo.K3 SET schluessel = @neu WHERE kontonr = @konto; -- Reader, READ COMMITTED with locks, scan in key orderINSERT #r (id) SELECT kontonr FROM dbo.K3 WITH (INDEX(PK_K3)) OPTION (MAXDOP 1);-- Expected: 50,000 distinct accounts, each exactly once

No account was added, and none dropped out. Even so, READ COMMITTED regularly returned wrong result sets on all four versions. On 2022 and 2025, the same writer managed only about one-twentieth of the moves; even then, an account was missing in nearly one of every three reads. We did not establish why the writer was slower.

• REPEATABLE READ keeps the rows it has read locked until the end. The duplicates disappear; rows that move behind the read position are still missing.
• SERIALIZABLE always returned exactly 50,000; in exchange, the writer became a deadlock victim 15 to 49 times per version.
• RCSI always returned exactly 50,000, without making anyone wait.

The rate of change was artificially high; in applications, keys rarely change. The conclusion stands: anyone who takes READ COMMITTED for a point-in-time view is mistaken, with and without NOLOCK.

RCSI: readers no longer wait

With READ_COMMITTED_SNAPSHOT ON, every statement under READ COMMITTED reads the state that was committed when it began. Changed rows leave a version for this, depending on the configuration in tempdb or, with ADR, in the database's persistent version store. Readers no longer take read locks and do not wait for writers. On the overview you can see the effect: a reader with a lock timeout after 503.53 ms versus 1.64 ms with RCSI.

Writers remain writers: UPDATE and DELETE still look for their rows with update locks on the current state and wait for each other. Only optimized locking in SQL Server 2025 changes that; see below.

Where RCSI changes results

Under classic READ COMMITTED, a reader waits until another transaction's change is committed or rolled back, and then sees the result. Under RCSI, it does not wait but sees the last committed state. For code that reads, checks a condition and then writes, that makes a difference. We reproduced three patterns with two sessions each: session A makes a change and keeps its transaction open for two seconds, and session B starts 0.7 seconds later. All results were identical on 2017, 2019, 2022 and 2025 and the same in every repetition.

Pattern 1: Validation logic in a trigger

CREATE TRIGGER dbo.trg_Kind_Pruefung ON dbo.Kind AFTER INSERT ASBEGIN    SET NOCOUNT ON;    IF EXISTS (SELECT 1 FROM inserted AS i               WHERE NOT EXISTS (SELECT 1 FROM dbo.Eltern AS e WHERE e.id = i.eltern_id))    BEGIN        ROLLBACK TRANSACTION;        THROW 50001, 'Kind ohne Eltern abgewiesen', 1;    END;END;

Here dbo.Eltern is the parent table and dbo.Kind the child table; the error message means “child without parent rejected”. Session A deletes parent 1 (its own trigger finds no children yet), and session B inserts a child for parent 1.

• READ COMMITTED with locks: B waits for A (LCK_M_S), sees after the commit that the parent is gone, and rejects the child.
• RCSI: B does not wait, still sees the parent in the committed state and inserts the child. After both commits, there is a child without a parent.
• Remedy: FROM dbo.Eltern AS e WITH (READCOMMITTEDLOCK) in the trigger. Then B waits again and rejects. Where it fits the data model, a foreign key is better: according to Microsoft and Paul White, it validates with locks even under RCSI (not re-measured).

Pattern 2: UPDATE with a subquery

UPDATE dbo.PlatzSET gast = 'B', nr = (SELECT COALESCE(MAX(p.nr), 0) FROM dbo.Platz AS p) + 1WHERE platz = 2;

(dbo.Platz is a table of seats; gast is the guest, nr a sequence number.) A assigns the next sequence number for seat 1 and holds its transaction open; B does the same for seat 2. Under locks, B waits and gets number 2. Under RCSI, the subquery reads the committed state, in which A's number is still missing: both get number 1. With WITH (UPDLOCK) or WITH (READCOMMITTEDLOCK) on the subquery, B waits and calculates correctly.

Whether an UPDATE with a subquery is affected depends on the plan. The familiar seat-assignment variant

UPDATE dbo.Platz SET gast = 'B'WHERE platz = (SELECT MIN(p.platz) FROM dbo.Platz AS p WHERE p.gast IS NULL);

stayed correct in the lab on all versions. Here the optimizer merges the subquery and the target into a single scan with update locks; B waits and gets the next free seat. With the sequence number, there are still two separate accesses, and the subquery reads a version. Do not rely on the plan; it can change.

Pattern 3: Read first, then write

SELECT @saldo = saldo FROM dbo.Konto WHERE id = 1;-- … check in the application or procedure …UPDATE dbo.Konto SET saldo = @saldo - @betrag WHERE id = 1;

(dbo.Konto is the account table, saldo the balance.) The balance is 100. A debits 30 and keeps its transaction open; B wants to debit 50.

• READ COMMITTED with locks: B already waits at the read, reads 70 and writes 20. Correct.
• RCSI: B reads 100, waits only at the UPDATE, and then writes 50. A's debit is lost.

Here the locks protect only by chance: in the same constellation, where both read first and then write, it also ended at 50 under READ COMMITTED with locks. So the pattern was already faulty before; RCSI just makes the error more frequent. There are two robust ways:

-- One statement: reading and writing coincideUPDATE dbo.Konto SET saldo = saldo - @betrag WHERE id = 1; -- Or declare the intent to change already when readingSELECT @saldo = saldo FROM dbo.Konto WITH (UPDLOCK) WHERE id = 1;

With UPDLOCK, B waited at the read in all configurations and wrote 20.

In short: The remedy is not to forgo RCSI but to write code that expresses its intent: one statement instead of two, UPDLOCK where you read in order to write, READCOMMITTEDLOCK or a constraint where you validate.

SNAPSHOT is a different decision

Under SNAPSHOT, a transaction sees the same committed state throughout, from its first read statement on. That is good for reports made up of several queries that have to fit together. In return, SNAPSHOT aborts when a transaction wants to change a row that another transaction has changed since it began. Error 3960, message text in the original from the lab:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Konto' directly or indirectly in database 'a5_si' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

In the lab, 3960 also occurred when the other transaction had changed only a different column of the same row, and even with SET saldo = saldo without a new value. If the other transaction is still open, SNAPSHOT waits first and aborts after its commit; if it rolls back, SNAPSHOT runs through. Applications under SNAPSHOT therefore need retry logic.

Write skew: Two doctors are on duty, and the rule is “at least one on duty”. Both check COUNT(*) >= 2 at the same time and sign themselves off. Under SNAPSHOT, nobody was on duty afterward. That is the textbook example, but in the lab it is not a SNAPSHOT-specific risk: under READ COMMITTED with locks and under RCSI, it ended the same way. Only SERIALIZABLE (one session becomes a deadlock victim with error 1205 and has to retry) and UPDLOCK on the count (under SNAPSHOT, the second session then waits and aborts with 3960) prevented it. Rules that span several rows need a lock or a constraint, not an isolation level alone.

What RCSI costs

14 bytes per row, but only at the first change. Turning it on does not change a single page. Only when a row is changed for the first time does it get the versioning information. On full pages, that no longer fits:

400,000 rows, fill factor 100, one UPDATE across all rows, byte-identical on all versions. Every page split exactly once, and the table doubled in size. Each further wave of changes also wrote more log with RCSI than without (55.9 instead of 41.2 MB). The documentation's “up to 14 percent more” became 100 percent more space here, for half-full pages. With free space on the pages, the effect is much smaller; we did not measure that. If you change large tables all at once after the switch, plan space and log for it.

Who holds versions. A version may go only when no statement can need it anymore. After three waves of changes across 400,000 rows, a single long-running read statement held about 200 MB in the version store in tempdb (with ADR about 153 MB in the persistent version store), for 65 seconds, until it ended. On 2022 and 2025, an idle open transaction did the same: one BEGIN TRAN, one SELECT, then nothing. Under RCSI, none of its later statements needs these versions, yet they were held anyway. A forgotten transaction in the connection pool thus acts like a long-running one. How to find such holders is explained on the page Version Store and Transaction Log in SQL Server.

Enabling RCSI safely

1. Read the code before you switch

Which procedures, triggers and functions look like the patterns above can be found with a text search over the database's modules. It does not replace reading, but it tells you where to start.

Query: RCSI suspect list

/* PSG Stack · SQL Server Locking · Query 3   Suspect list before enabling READ_COMMITTED_SNAPSHOT:   Which modules (triggers, procedures, functions, views) should you read before switching to RCSI?    !!! WARNING — please read before running !!!   - Read-only. The query changes no data, no setting and no code.   - A suspect list, not a verdict. It only looks for text patterns in module text (sys.sql_modules).     Matches in comments or string literals show up as well (false positives). It cannot see code     outside the database (application, ORM, ad hoc SQL, Agent jobs, other databases).     An empty result does not mean "RCSI is safe".   - Current database only. Run it in each application database.   - Large code bases: the text of every module is read and searched once. This costs some CPU but     takes no locks on application tables. Do not run it in a loop.   - Permissions: user in the database plus VIEW DEFINITION (database) or VIEW ANY DEFINITION.     Without them, modules are missing or their text is empty; the summary at the end shows this.     Encrypted modules (WITH ENCRYPTION) appear as "text not readable".   - MAXDOP 1: runs serially and occupies no additional cores.   - SQL Server 2017 and later. Tested in the PSG lab on 2017, 2019, 2022 and 2025 (10 Nov 2026).    Columns with 1 = pattern found:     trigger_with_check     Trigger with EXISTS or COUNT: if it checks rows of other transactions, under                            RCSI it sees the last committed state instead of waiting.     read_then_write        Variable from SELECT followed by INSERT/UPDATE/DELETE in the same module: under                            RCSI the value read may already be outdated when it is written.     update_with_subquery   UPDATE/DELETE with (SELECT ...) (coarse search): the subquery reads row                            versions under RCSI.     function_reads_tables  Scalar or table-valued function with FROM: may see a later state than the                            calling statement.     nolock                 NOLOCK, READUNCOMMITTED, READ UNCOMMITTED: review regardless of RCSI.     readcommittedlock      forces locking despite RCSI (often intended, a sign of deliberate checks).     locking_hint           UPDLOCK, XLOCK, HOLDLOCK, TABLOCK, SERIALIZABLE, REPEATABLEREAD.     isolation_level        SET TRANSACTION ISOLATION LEVEL inside the module.*/SET NOCOUNT ON; WITH m AS (    SELECT o.object_id,           s.name                                    AS schema_name,           o.name                                    AS object_name,           o.type                                    AS obj_type,           o.type_desc                               AS type_text,           OBJECT_SCHEMA_NAME(tr.parent_id) + N'.' + OBJECT_NAME(tr.parent_id) AS trigger_table,           UPPER(REPLACE(REPLACE(REPLACE(sm.definition, NCHAR(13), N' '), NCHAR(10), N' '), NCHAR(9), N' ')) AS d    FROM sys.sql_modules AS sm    JOIN sys.objects AS o ON o.object_id = sm.object_id    JOIN sys.schemas AS s ON s.schema_id = o.schema_id    LEFT JOIN sys.triggers AS tr ON tr.object_id = sm.object_id    WHERE o.is_ms_shipped = 0),f AS (    SELECT m.*,           CASE WHEN obj_type = 'TR' AND (d LIKE N'%EXISTS%' OR d LIKE N'%COUNT(%' OR d LIKE N'%COUNT (%') THEN 1 ELSE 0 END AS trigger_with_check,           CASE WHEN (d LIKE N'%SELECT @%' OR d LIKE N'%= (SELECT%' OR d LIKE N'%=(SELECT%')                 AND (d LIKE N'%UPDATE %' OR d LIKE N'%DELETE %' OR d LIKE N'%INSERT %') THEN 1 ELSE 0 END AS read_then_write,           CASE WHEN d LIKE N'%UPDATE %(SELECT%' OR d LIKE N'%UPDATE %( SELECT%'                  OR d LIKE N'%DELETE %(SELECT%' OR d LIKE N'%DELETE %( SELECT%' THEN 1 ELSE 0 END AS update_with_subquery,           CASE WHEN obj_type IN ('FN', 'IF', 'TF') AND d LIKE N'%FROM %' THEN 1 ELSE 0 END AS function_reads_tables,           CASE WHEN d LIKE N'%NOLOCK%' OR d LIKE N'%READUNCOMMITTED%' OR d LIKE N'%READ UNCOMMITTED%' THEN 1 ELSE 0 END AS nolock,           CASE WHEN d LIKE N'%READCOMMITTEDLOCK%' THEN 1 ELSE 0 END AS readcommittedlock,           CASE WHEN d LIKE N'%UPDLOCK%' OR d LIKE N'%XLOCK%' OR d LIKE N'%HOLDLOCK%' OR d LIKE N'%TABLOCK%'                  OR d LIKE N'%(SERIALIZABLE%' OR d LIKE N'%, SERIALIZABLE%' OR d LIKE N'%REPEATABLEREAD%' THEN 1 ELSE 0 END AS locking_hint,           CASE WHEN d LIKE N'%ISOLATION LEVEL%' THEN 1 ELSE 0 END AS isolation_level    FROM m)SELECT DB_NAME()               AS database_name,       schema_name             AS [schema],       object_name,       type_text               AS object_type,       trigger_table,       CASE WHEN d IS NULL THEN N'text not readable (encrypted or missing permission)'            WHEN trigger_with_check = 1 OR read_then_write = 1 THEN N'1 read first'            WHEN update_with_subquery = 1 OR function_reads_tables = 1 THEN N'2 read'            ELSE N'3 note' END AS priority,       trigger_with_check, read_then_write, update_with_subquery, function_reads_tables,       nolock, readcommittedlock, locking_hint, isolation_levelFROM fWHERE d IS NULL   OR trigger_with_check + read_then_write + update_with_subquery + function_reads_tables      + nolock + readcommittedlock + locking_hint + isolation_level > 0ORDER BY priority, [schema], object_nameOPTION (MAXDOP 1); /* Summary: how much code was visible? If the permission says "no" or many texts are empty,   the list above is incomplete. */SELECT DB_NAME()                                                  AS database_name,       CASE d.is_read_committed_snapshot_on WHEN 1 THEN N'on' ELSE N'off' END AS rcsi_now,       (SELECT COUNT(*) FROM sys.sql_modules AS sm JOIN sys.objects AS o ON o.object_id = sm.object_id         WHERE o.is_ms_shipped = 0)                               AS modules_visible,       (SELECT COUNT(*) FROM sys.sql_modules AS sm JOIN sys.objects AS o ON o.object_id = sm.object_id         WHERE o.is_ms_shipped = 0 AND sm.definition IS NULL)     AS text_not_readable,       CASE HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', N'VIEW DEFINITION') WHEN 1 THEN N'yes' ELSE N'no' END AS has_view_definitionFROM sys.databases AS dWHERE d.database_id = DB_ID()OPTION (MAXDOP 1);

In the lab, the query found all 13 deliberately created suspect cases on all four versions and none of the harmless validation cases. But it also reports false positives, for example the safe seat-assignment procedure from pattern 2 as “read then write”; a text search cannot see the difference in the plan. With only db_datareader, the modules appear without text, and the summary shows no for the permission. VIEW DEFINITION on the database is sufficient.

2. Test

You can reproduce the patterns above in a test with two sessions: one holds its transaction open, the other runs against it. If you test code with only one session, you see no difference between the isolation levels.

3. Switch in a maintenance window

ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Without the added clause, ALTER DATABASE waits for every other connection in the database, even an idle one with no transaction, and even beyond the commit of an open transaction, until the connection is closed. According to the documentation, the statement fails immediately with WITH NO_WAIT if it cannot finish right away. In the lab, it did not do so on any version: it waited silently just as long as without the clause. With WITH ROLLBACK IMMEDIATE, the switch was flipped after about three seconds; open transactions were rolled back, the connections were closed, and the session making the change reported:

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

For ADR and optimized locking, ALTER DATABASE behaved the same way in the lab. We did not test ALLOW_SNAPSHOT_ISOLATION; according to the documentation, this option waits only for running transactions. The way back is the same command with OFF.

New since SQL Server 2025

With optimized locking and RCSI, UPDATE and DELETE check their condition against the last committed state without locking (lock after qualification). Whether the switch is worthwhile for your load is covered in Enable optimized locking. For this page, it means:

• The three patterns stay as they are under RCSI. Orphaned child, duplicate sequence number and lost update occurred in the same way with optimized locking. The remedies still work; the sessions just wait on LCK_M_S_XACT_READ or LCK_M_S_XACT_MODIFY now.
• No new error under RCSI. Microsoft writes that update conflicts under RCSI are retried automatically with optimized locking. In the lab, that means: B waits for A's transaction, checks again afterward and returns the same result as without optimized locking. Error 3960 occurred under RCSI neither with nor without optimized locking.
• SNAPSHOT stays SNAPSHOT. Error 3960 occurred unchanged with optimized locking.
• One order of execution changes. The documentation's example (UPDATE t4 SET b = 3 WHERE b = 2 while another transaction sets b from 1 to 2) ended at b = 2 with optimized locking and RCSI and at b = 3 without optimized locking. Explanation in the optimized locking deep dive.

Method and limits

All measurements come from our lab of Nov 10, 2026: SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU10 (17.0.5035.5), Developer Edition, Linux containers with four CPUs on a test VM with a SATA SSD, synthetic tables, our own code. The two-session patterns each ran twice with a fixed time offset, the load tests over several rounds of eight to twelve seconds. Whatever we did not measure ourselves comes from Microsoft Learn and the articles named below. Limits:

• The frequencies in the NOLOCK and READ COMMITTED tests apply to a deliberately extreme rate of change. They show that it happens, not how often it happens for you.
• We did not trigger error 601; other triggers (heaps with forwarded records, ghost cleanup, TRUNCATE) were not checked.
• Pattern 2 depends on the plan. We showed two forms; other plans can turn out differently.
• We did not reproduce scalar and multi-statement functions under RCSI, foreign keys or indexed views.
• Why NO_WAIT did not fail, and why ROLLBACK IMMEDIATE consistently took about three seconds, we did not investigate. Only the case of an idle or sleeping connection was checked, not that of a running query.
• Why the writer in the READ COMMITTED test was so much slower on 2022 and 2025 is open.
• That an idle open transaction holds versions, we measured on 2022 and 2025; we did not check it on 2019. An early preliminary test with a smaller table did not show it; we did not resolve the contradiction.
• We did not measure Azure SQL Database, availability groups or Windows.

Further reading

• Microsoft Learn: SET TRANSACTION ISOLATION LEVEL; Transaction Locking and Row Versioning Guide; ALTER DATABASE SET options; Optimized locking.
• Paul White: the “SQL Server Isolation Levels” series on SQLPerformance.com (2014/2015), especially “The Read Committed Isolation Level”, “Read Committed Snapshot Isolation” and “The SNAPSHOT Isolation Level”.
• Lubor Kollar (SQLCAT): Previously committed rows might be missed if NOLOCK hint is used (2007).
• Brent Ozar: Using NOLOCK? Here’s how you’ll get the wrong query results (2018).
• Erik Darling: A Little More About Isolation Levels In SQL Server (2024).
• At PSG: Optimized locking explained, Enable optimized locking, Version Store and Transaction Log in SQL Server, SQL Server Deadlock: Read the Graph, Understand Error 1205.

Thinking about turning on RCSI?

Or do your reports count differently from run to run? In a performance analysis, we read the candidates from query 3 with you, reproduce the critical patterns on your code and plan the switch. The SQL Server Health Check gives the overall view of the instance, and the PTS training teaches the fundamentals.

More locking topics