What WITH (NOLOCK) Actually Does in SQL Server

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 most of that, but not all of it: it takes no shared locks on the data, and it will not wait behind another transaction’s data locks. It still takes a schema stability (Sch-S) lock on the table, and it still waits on a schema modification (Sch-M) lock.

That is why it “avoids blocking” against other readers and writers: there is no data lock to wait behind. It is also why the promise breaks the moment someone runs an ALTER TABLE, a partition switch, or an offline index rebuild — those take Sch-M, and a NOLOCK query queues behind them exactly like any other query. People paste this hint specifically so a query can never be blocked, then sit behind a schema change wondering what is broken. 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.

One thing to check before repeating this: the lab runs locking READ COMMITTED, which is still the default for a SQL Server database. If read committed snapshot isolation is already switched on — it is the default on Azure SQL Database, and common in estates that already took the advice further down this page — that same SELECT returns 500.00 immediately and never blocks at all. The dirty read above still reproduces; this half does not.

---

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
• Anything of the shape INSERT … SELECT … WITH (NOLOCK). A dirty read on a screen is a wrong number someone might question; this path writes that wrong number down and it stays.
• Any scan under concurrent writes, not only joins. A single-table NOLOCK scan can skip rows or return the same row twice when pages move underneath it, and it can fail outright with Msg 601, could not continue scan with NOLOCK due to data movement — which is the one people actually hit. Joins add a second problem on top: each table is read at a different instant, so the result never existed as a consistent whole.

---

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.

---