Almost every SQL Server developer has copied WITH (NOLOCK) onto the end of a query at some point, usually because someone said it “makes queries faster” or “avoids blocking.” Both of those things are technically true. What rarely gets explained is what you’re trading away to get them.
WITH (NOLOCK) doesn’t make a query faster by optimizing it. It makes it faster by skipping a safety check: it tells SQL Server not to take shared locks, and not to honor the locks other transactions are holding. That’s the entire mechanism, and it has a real, demonstrable consequence: you can read data that was never actually committed, and never actually existed as a permanent value.
This post shows that happening, with a real, reproducible example, not a theoretical warning.
What NOLOCK Actually Tells SQL Server to Do
WITH (NOLOCK) is shorthand for READ UNCOMMITTED isolation applied to a single table in a single query. Under normal (READ COMMITTED) isolation, a SELECT takes a shared lock on the rows it reads, and it has to wait if another transaction is holding an incompatible lock on those same rows. NOLOCK removes both halves of that: it doesn’t take a shared lock itself, and it doesn’t wait for anyone else’s lock either.
That’s why it “avoids blocking”: there’s no lock to wait behind. It’s also why it can return data that was never committed: nothing stops it from reading a row in the middle of being changed by another transaction that might still roll back.
A Real Dirty Read, Reproduced
Here’s the setup: a one-row table with a balance of 500.00, and two things happening to it at once.
CREATE TABLE dbo.NolockDemo (ID INT PRIMARY KEY, Balance DECIMAL(10,2));
INSERT INTO dbo.NolockDemo (ID, Balance) VALUES (1, 500.00);
Session A starts a transaction, changes the balance, pauses for a few seconds to hold the transaction open, then decides to roll the change back entirely:
BEGIN TRAN;
UPDATE dbo.NolockDemo SET Balance = 999.00 WHERE ID = 1;
-- (transaction held open here)
ROLLBACK TRAN;
Session B, while Session A’s transaction is still open and uncommitted, reads the same row with NOLOCK:
SELECT ID, Balance FROM dbo.NolockDemo WITH (NOLOCK) WHERE ID = 1;
Real output from this lab instance, not staged:
ID Balance
-- -------
1 999.00
Session B just read 999.00. Two seconds later, Session A’s transaction rolled back, and the balance reverted to its real, committed value:
SELECT ID, Balance FROM dbo.NolockDemo WHERE ID = 1;
ID Balance
-- -------
1 500.00
999.00 was never a real value. It existed for a few seconds inside an uncommitted transaction that was always going to roll back, and NOLOCK handed it to a reader anyway, with no indication that anything was uncertain about it. That’s a dirty read: not stale data, not slightly-out-of-date data, but a number that never became true at all.
What a Plain SELECT Does Instead
Run the same scenario without NOLOCK, and the behavior is completely different:
SELECT ID, Balance FROM dbo.NolockDemo WHERE ID = 1;
Real output from the same lab instance, same concurrent update in progress:
ID Balance
-- -------
1 500.00
Elapsed: 4.8s (blocked until the other session released its lock)
The plain SELECT didn’t return early with a wrong answer. It waited, blocked behind Session A’s lock, until the transaction resolved one way or the other, then returned the real, final value. That wait is exactly the cost NOLOCK is designed to avoid, and exactly what it gives up in exchange: a guaranteed-correct answer, later, instead of an immediate answer that might be wrong.
Why This Gets Copy-Pasted Everywhere Anyway
On a busy OLTP table, blocking is a real, visible problem: dashboards feel slow, reports time out, and NOLOCK makes both of those symptoms disappear immediately. The dirty-read risk, by contrast, is invisible most of the time. Most rows aren’t being actively updated at the exact millisecond you happen to read them, so the wrong-answer scenario is genuinely rare in absolute terms on any single query.
The problem is what “rare” means at scale. A dashboard refreshed thousands of times a day, or a report run against a table with constant write activity, will eventually land inside someone else’s uncommitted transaction. When it does, nothing about the result looks wrong. There’s no error, no warning, just a number that’s confidently displayed and quietly untrue.
Where NOLOCK Is a Reasonable Choice
- Ad-hoc, one-off investigation queries where you’re the only one who’ll ever see the result, and you understand the risk
- Reporting against tables that are effectively read-only, or only updated in batches you know aren’t running right now
- Large diagnostic scans where an occasional stale or duplicate row genuinely doesn’t matter to the conclusion
Where It’s a Real Risk
- Anything involving money, quantities, or counts that someone will act on, exactly the case demonstrated above
- Reports or dashboards other people trust as ground truth without knowing NOLOCK is involved
- Any query joining multiple tables under concurrent writes, NOLOCK can also produce duplicate rows or skip rows entirely during a page split, not just dirty values
What to Use Instead
If the actual goal is reducing blocking rather than accepting dirty reads, READ COMMITTED SNAPSHOT ISOLATION (RCSI) gives readers a consistent, committed view of the data using row versioning, without taking or waiting on locks the way default READ COMMITTED does. It’s a database-level setting, not a per-query hint, and it’s the modern answer to the exact problem NOLOCK was historically reached for. It has its own cost (row-versioning storage overhead in the system temp database), but that cost is predictable and doesn’t include the possibility of reading data that never existed.
Summary
WITH (NOLOCK) isn’t a performance optimization in the usual sense, it’s a decision to skip locking entirely, and skipping locking means reading whatever’s physically in the data pages at that instant, committed or not. The example above is real and reproducible: an uncommitted, ultimately-rolled-back value was read and returned as if it were fact. That’s not a rare edge case; it’s the literal, designed behavior of the hint.
Reach for it deliberately, for genuinely read-only or low-stakes queries, not as a reflexive fix for blocking. If blocking is the actual problem, RCSI solves it without the dirty-read tradeoff.
Related
Blocking and Locking (DBA Scripts pillar), scripts for finding what’s actually blocking what, right now and historically.
Leave a Reply