NOLOCK: Fast Answers Can Still Be Wrong

A report that completes in two seconds can be less useful than one that waits for five seconds if its total never existed in committed data. NOLOCK changes what a reader is allowed to observe. It does not simply make the same query cheaper. This distinction matters when a number drives billing, inventory replenishment, or an operational escalation.

Reproduce the correctness problem

Use a disposable database with two query windows connected to that same database. Run the setup once. In window A, start the transaction and leave it open. In window B, execute the NOLOCK query before returning to A and rolling back. The deliberately uncommitted balance is visible to B even though it never becomes a committed business event.

CREATE TABLE dbo.NolockDemo (Id int PRIMARY KEY, Balance int NOT NULL);
INSERT dbo.NolockDemo VALUES (1, 100);

BEGIN TRAN;
UPDATE dbo.NolockDemo SET Balance = 900 WHERE Id = 1;
-- Run the reader in window B, then execute:
-- ROLLBACK;

SELECT Balance FROM dbo.NolockDemo WITH (NOLOCK) WHERE Id = 1;

The reported value of 900 is a dirty read. After rollback, the committed balance remains 100. A normal locking read under READ COMMITTED may wait during this experiment; with READ_COMMITTED_SNAPSHOT enabled it can read the previously committed version instead. Check the database setting before interpreting the observed blocking. An immediate result does not prove that a locking hint was required.

The same problem becomes harder to recognize in a report joining several tables. A customer balance might come from one point in a transaction while related entries come from another. NOLOCK can also encounter missing or repeated rows during concurrent physical changes. A quick second execution that returns the expected total does not invalidate the first incorrect result. Intermittent errors are exactly what makes this pattern difficult to investigate.

Diagnose the wait that prompted the hint

NOLOCK still needs schema stability locks during compilation and execution. A schema modification can therefore block a NOLOCK query, and a long-running NOLOCK query can delay a schema change. Nor does the hint remove CPU work, sort spills, network transfer, or storage reads. Treat a remaining wait according to its actual wait type.

Capture the reader, the head blocker, their statements, and transaction age before changing isolation. A transaction left open by application code needs a transaction-lifetime fix. A scan touching millions of irrelevant rows needs an access-path review. A dashboard requesting every historical record may need a narrower contract or a separately refreshed summary. These changes address different causes; they should not be bundled into one unexplained database setting change.

For each affected report, specify the consistency requirement. Is a committed view of each individual statement sufficient? Must several statements agree with one another? Is an explicitly stale snapshot acceptable? Statement-level consistency from READ_COMMITTED_SNAPSHOT and transaction-level consistency from SNAPSHOT are different contracts. A read-only replica can also be behind the writer and does not make arbitrary multi-statement workflows automatically consistent.

Replace the hint deliberately

Start with one report whose expected results can be checked. Remove its hint in a representative test environment, capture duration and logical reads, and run a concurrent writer. If considering row versioning, evaluate version-store capacity, long-running readers, and the behavior of existing write transactions. Enabling a database-wide isolation option affects more than the single report that triggered the investigation.

A useful acceptance test repeats the balance experiment and verifies that the report never publishes the uncommitted value. Add a multi-table transaction matching the application's real write sequence. Test the business invariant, such as matching invoice headers and line totals, rather than merely requiring HTTP 200 from the reporting endpoint.

Record both latency and correctness outcomes. A successful replacement produces an acceptable committed view under concurrent activity and meets the reporting deadline. Keep the test fixture separate from production, close the demonstration transaction with ROLLBACK, and remove the practice table when finished. NOLOCK should be a consciously accepted relaxation for a specific use case, never an invisible default pasted into every query.

Technical references: Microsoft Learn: Table hints · Microsoft Learn: Transaction isolation.