SQL SERVER – The NOLOCK Question – Notes from the Field #117

The NOLOCK question is really a question about which results an application can trust. This is episode 117 of Notes from the Field, featuring database expert Stuart Ainsworth. My concern in the original introduction was the widespread use of NOLOCK as a performance shortcut.

Stuart described tuning a mature application that had been developed by several people over a decade. NOLOCK had been added widely to improve throughput. Development management wanted to reduce its use. The guidance below preserves that discussion and adds a small demonstration plus current isolation-level distinctions.

What NOLOCK Gives Up

WITH (NOLOCK) allows reads under READ UNCOMMITTED rules for the referenced table. A reader can see a value that another transaction has changed but not committed. If the writer rolls back, the reader has already used a value that never became committed data.

The risk goes beyond one changed value. Concurrent changes can also cause rows to be missed or returned more than once. This can make counts and totals unreliable. NOLOCK affects result correctness; it does not by itself mean that the stored database has become physically corrupt.

The name is misleading. These queries still acquire schema stability locks during compilation and execution. A concurrent schema modification lock can block them. NOLOCK is therefore neither a promise of accurate results nor a promise of no blocking.

A Two-Session Dirty Read

Use two fresh query windows connected to the same disposable SQL Server test instance. This example creates a global temporary table named ##NolockDemo so both sessions can see it. Session A refuses to run inside an existing transaction and disables implicit transactions for the demonstration. If either guard reports an error, stop without running the later blocks. Do not reuse someone else’s table. Keep Session A connected until the final read.

-- Session A: use a fresh query window and leave it open.
IF @@TRANCOUNT <> 0
BEGIN
    RAISERROR('An existing transaction is open. Use a fresh connection.', 16, 1);
    RETURN;
END;
SET IMPLICIT_TRANSACTIONS OFF;
IF OBJECT_ID(N'tempdb..##NolockDemo') IS NOT NULL
BEGIN
    RAISERROR('Demo table already exists; stop.', 16, 1);
    RETURN;
END;
CREATE TABLE ##NolockDemo
(id int PRIMARY KEY, quantity int NOT NULL);
INSERT ##NolockDemo VALUES (1, 10);

BEGIN TRANSACTION;
UPDATE ##NolockDemo
SET quantity = 99 WHERE id = 1;

-- Session B: run while Session A's transaction is still open.
SELECT quantity
FROM ##NolockDemo WITH (NOLOCK)
WHERE id = 1;

Session B returns 99, although Session A has not committed that value. Now undo the change in Session A and read again in Session B:

-- Session A: undo the change.
ROLLBACK TRANSACTION;

-- Session B: read again after Session A has rolled back.
SELECT quantity
FROM ##NolockDemo
WHERE id = 1;

The second read returns 10. Both results were observed in a two-connection test. This small demonstration proves the dirty read; it does not attempt to reproduce every missing-row or duplicate-row case. After the test, close both connections so the global temporary table is released. If you interrupt the test, roll back the open transaction in Session A first.

When Is Approximate Information Acceptable?

Stuart’s original distinction remains useful: an approximate activity indicator has different accuracy requirements from an authoritative balance or inventory count. Even when an application accepts approximation, the owner should understand dirty, missing and repeated rows. A result that merely appears plausible is not a reliable approximation.

Address the Blocking Instead

Stuart suggested improving query and server performance, changing application architecture where appropriate, and considering row versioning. Start with inefficient queries, suitable indexes and unnecessarily long transactions. Reporting workloads or queued work may benefit from a different design, but that also changes freshness and operational requirements.

READ_COMMITTED_SNAPSHOT (RCSI) changes ordinary READ COMMITTED reads to use statement-level row versions. SNAPSHOT is separately enabled with ALLOW_SNAPSHOT_ISOLATION, then selected by the session for transaction-level consistency. These are related options with different behavior, not two names for the same switch.

Test application behavior, update conflicts where applicable, version retention and storage capacity before changing isolation. Row versioning reduces reader-writer data-lock contention; it does not remove every kind of blocking. Versions normally use tempdb when accelerated database recovery is off. With ADR enabled in SQL Server 2019 and later, the user database persistent version store is involved instead.

Stuart reported using snapshot isolation together with query tuning for that application. The original discussion also linked to a guide to implementing snapshot isolation and RCSI. The right choice depends on the application’s consistency requirements and a tested rollout.

For help investigating a SQL Server performance problem, see Fix Your SQL Server.

NOLOCK is not a general performance fix, it is a decision to accept weaker read consistency.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.