NOLOCK Returned a Balance That Never Existed

WITH (NOLOCK) gets added to queries for one reason: something was slow, or something was blocked, and the hint made the query run.

It does make blocking stop. What it gives up in exchange is arguably far more important.

A balance that was never real

Two connections against the same instance. An account table with one row, holding 100.00.

Connection A opens a transaction and changes the balance, then stops without committing:

Connection B reads the same row two ways:

Identical on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2).

The NOLOCK read returned 999,999.00. Connection A then rolled back, so that value was never committed and never became the balance. It was an uncommitted value inside another transaction, and the query returned it without qualification.

The read without the hint waited, which is the behaviour the hint is usually added to solve. That wait is the isolation level doing its job.

It is not “no locking”

NOLOCK is a synonym for READUNCOMMITTED, and it sets the isolation level for that table reference to read uncommitted.^[1]

The name causes real confusion. The query still takes a schema stability lock, so it is not lock-free. What it stops doing is taking shared locks on rows and pages, and, more importantly, it stops respecting the exclusive locks other transactions hold. That second part is what produced the 999,999.00.

It also does nothing whatsoever to reduce the locking done by writers. A NOLOCK reader cannot make an UPDATE faster or less blocking. If the blocking you are trying to fix is write-versus-write, the hint is aimed at the wrong thing.

The failures that are not dirty reads

The dirty read is the famous one and the easiest to demonstrate. The other two are worse, because they do not require anyone to roll anything back.

Under read uncommitted, a scan can miss rows that were committed before the query started, and it can return the same row more than once.^[2] This happens when concurrent writes cause pages to split and rows to move while the scan is walking the index. A row that moves ahead of the scan position gets read twice; one that moves behind it is never read.^[4]

I have not reproduced those two here, and I do not need to, because other people already have and their demonstrations are better than one I would write in an afternoon.

Aaron Bertrand builds a 100,000 row table, runs a reader loop comparing COUNT(*) under NOLOCK against a known baseline, and runs a second session that updates clustered key values to force page splits.^[5] The counts come back both high and low. The detail that makes it convincing is that the concurrent workload only updates existing rows: nothing is inserted, nothing is deleted, and nothing is rolled back, so the wrong answers cannot be explained away as dirty reads.

Lubor Kollar, then at Microsoft, published the case for the first behaviour in 2007, with scripts.^[6] His summary is the part worth keeping:

It is much less obvious that even rows committed a long time before my NOLOCK transaction started might be skipped in the scan.

Kollar demonstrated the missing rows and said plainly that he had no repro for the duplicate case, inviting readers to supply one. Uri Munitz and Danny Ravid did, in the comments, and he accepted it. Worth knowing if you go looking, because the duplicate-row script on that page is not his.

Paul White draws the line I keep wanting to draw:^[7]

the allocation-ordered scan can miss some committed data completely, or encounter other committed data more than once. The emphasis there is on missing or double-counting committed data (not reading uncommitted data) so it is not a case of “dirty reads” as such.

They matter because of what they do to a count. A dirty read at least corresponds to a value that some transaction intended. A row counted twice, or skipped, produces a total that never existed anywhere, from data that nobody rolled back, with no error and no repeatable test case.

Where the habit comes from

NOLOCK spread because it works, in the narrow sense that the symptom goes away immediately and the cost is invisible in testing.

Dirty reads need concurrency to appear. On a development instance with one user, NOLOCK and no hint return the same answer every time. The hint gets added, the tests pass, the blocking stops in production, and the wrong answers arrive later as an occasional reconciliation discrepancy that nobody traces back to a query hint added two years earlier.

That asymmetry, immediate visible benefit against delayed invisible cost, is why it ends up applied by reflex across whole codebases rather than considered per query.

What to use instead

The problem NOLOCK is usually solving is that readers block behind writers. There is a mechanism built for that which does not trade away correctness: row versioning.

Under snapshot isolation, readers do not take shared locks and do not wait for writers. They read the version of each row as it was when their transaction started, which is a committed state. No blocking, and no dirty reads.^[3]

It is not free. Row versions are kept in tempdb, which needs the space and the throughput to hold them, and it changes how write conflicts surface. It also has to be enabled at the database level before it will do anything, which is the subject of The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951.

The choice was never between NOLOCK and blocking.

The rule

Do not use WITH (NOLOCK). Prefer SET TRANSACTION ISOLATION LEVEL SNAPSHOT where readers must not block.

There is a narrow set of cases where read uncommitted is genuinely acceptable: a rough progress estimate, a size check, a diagnostic query where you already know the number is approximate and nothing downstream treats it as exact. If a human reads the number, shrugs at a small discrepancy, and moves on, the hint is not doing harm.

The trouble is that queries do not stay in that category. The diagnostic query gets copied into a procedure, the procedure feeds a report, and the report becomes the number somebody reconciles against. The hint travels with it, and nothing reports that the guarantees changed.

This is one of a series on the T-SQL conventions I actually use and why.

Do you have a NOLOCK you would defend? I am interested in the cases where it is the right call. Bluesky or LinkedIn.

References

1. Table Hints (Transact-SQL) - Microsoft Learn. NOLOCK is equivalent to READUNCOMMITTED, and the note that it does not eliminate all locking. ↩
2. SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - Microsoft Learn. Describes read uncommitted, including missing and duplicated rows during scans, alongside the other isolation levels. ↩
3. Transaction Locking and Row Versioning Guide - Microsoft Learn. How row versioning serves readers without shared locks, and what it costs in tempdb. ↩
4. Transaction Locking and Row Versioning Guide - Microsoft Learn, under “Missing and double reads caused by row updates”. States that a row whose index key changes may be read again if it moves ahead of the scan, or missed if it moves behind it. ↩
5. SQL Server NOLOCK Anomalies, Issues and Inconsistencies - Aaron Bertrand, MSSQLTips, 18 June 2019. Runnable demonstration of both skipped and double-counted rows using a concurrent workload that only updates existing rows. ↩
6. Previously committed rows might be missed if NOLOCK hint is used - Lubor Kollar, SQL Server Development, 1 February 2007. Scripts demonstrating that a NOLOCK scan can miss rows committed long before it began. The duplicate-row repro in the comments is by Uri Munitz and Danny Ravid, not by Kollar. ↩
7. The Read Uncommitted Isolation Level - Paul White, SQLPerformance.com, 23 April 2015. Explains allocation-order scans and separates these anomalies from dirty reads. ↩