WITH (NOLOCK) in SQL Server: what it really does, and what to use instead

WITH (NOLOCK) is probably the most copied table hint in SQL Server. It’s added to queries “to make them faster” or “to stop blocking”, often to every table in a query, sometimes to every query in a codebase. It does stop a reader from waiting for locks. It does that by reading data that isn’t committed, and in some cases data that’s in the middle of moving.

The demonstration below was run on SQL Server 2022 against the SpDevDemo database, with two sessions open side by side.

What NOLOCK means

WITH (NOLOCK) is the same as WITH (READUNCOMMITTED): for that table, the query runs at the read uncommitted isolation level. The reader takes no shared locks and ignores the locks other transactions hold. So it doesn’t wait for writers, and writers don’t wait for it.

The price is that it sees whatever is on the page at that moment, committed or not.

A dirty read, step by step

Session 1 changes an order and, for six seconds, hasn’t decided whether to keep the change:

BEGIN TRANSACTION;
UPDATE dbo.Orders SET Total = 0 WHERE OrderId = 42;
WAITFOR DELAY '00:00:06';
ROLLBACK;                -- the change never happened

Session 2, during those six seconds:

SELECT Total FROM dbo.Orders WITH (NOLOCK) WHERE OrderId = 42;

Total
-----
0.00

The same query without the hint waits for session 1. With a two-second lock timeout, to show it:

SET LOCK_TIMEOUT 2000;
SELECT Total FROM dbo.Orders WHERE OrderId = 42;

Msg 1222, Level 16, State 51
Lock request time out period exceeded.

After session 1 rolls back, the order’s total is what it always was:

Total
-------
2335.00

The NOLOCK query returned a total of 0.00 for an order whose total was never 0.00. Any report, export or decision based on that value is wrong, and there’s no trace of why.

Worse than dirty reads

Reading uncommitted values is the well-known problem. The less known ones come from how a NOLOCK scan reads pages:

• Missing rows and duplicate rows. A NOLOCK scan can read pages in allocation order. If another transaction splits a page or moves a row (for example, by updating an indexed column) while the scan runs, the scan can read the row twice, or not at all. Totals and counts come out wrong even though no transaction was rolled back.
• Error 601, Could not continue scan with NOLOCK due to data movement, when the page the scan was positioned on is removed from under it.
• Reading half of a large change. A multi-row update that’s halfway through is visible as half old and half new values.

These don’t happen often, which is why they’re dangerous: the query is right in testing and occasionally wrong in production, under exactly the load that made someone add NOLOCK.

What NOLOCK doesn’t do

• It doesn’t make the query do less work. The same pages are read; only the locking is skipped. If a query is slow because it scans 10 million rows, it’s still slow.
• It doesn’t avoid every lock. The query still takes a schema stability lock, so it waits for (and blocks) schema changes and index rebuilds.
• It can’t be used on the table being changed:

UPDATE dbo.Orders WITH (NOLOCK) SET Total = Total WHERE OrderId = 42;

Msg 1065, Level 15, State 1
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

The better fix: READ_COMMITTED_SNAPSHOT

If the real problem is readers blocked by writers, turn on read committed snapshot isolation (RCSI) for the database:

ALTER DATABASE SpDevDemo SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

With RCSI on, the default READ COMMITTED level stops using shared locks. A reader that meets a row being changed reads the last committed version of it, kept in the version store in tempdb. Repeat the experiment with RCSI on, and no hint at all:

SET LOCK_TIMEOUT 2000;
SELECT Total FROM dbo.Orders WHERE OrderId = 42;    -- while session 1 holds its update

Total
-------
2335.00

No waiting, and the value is correct: the committed total, not the uncommitted 0.00. That’s what people wanted from NOLOCK in the first place.

Things to know before switching it on:

• It changes behavior for the whole database, so test the application. Code that relied on readers blocking (for example, a “read the next job then mark it taken” pattern without UPDLOCK) needs real locking hints.
• It adds version store activity in tempdb, and 14 bytes to rows when they’re modified. On most systems the cost is small. Monitor tempdb after the change.
• ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON needs to be the only connection to the database for a moment; WITH ROLLBACK IMMEDIATE disconnects the others.
• Azure SQL Database has RCSI on by default.

Check whether it’s on:

SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

When NOLOCK is acceptable

There are honest uses: rough monitoring queries (“about how many rows are in this staging table?”), reading a table you know isn’t being written to, and ad hoc diagnostics on a busy server where an approximate answer now beats an exact one later. In each case, the person reading the result knows it may be wrong.

It doesn’t belong in application queries whose results are shown to users, saved, or used for decisions, and it doesn’t belong in financial reports.

Removing NOLOCK from a codebase

1. Turn on RCSI in a test environment and run the test suite.
2. Remove the hints, starting with queries that feed reports and exports.
3. Find what was really blocking: the long transactions holding locks. The monitoring article shows how to see who blocks whom, and the transactions article how to keep transactions short.

SpDev Pro’s profiler reports statements that waited on locks during a profiled run, and transactions held open across slow statements, which are the usual cause of the blocking that NOLOCK was added to hide.