Quick summary
Summarize this blog with AI
A SQL Server report shows revenue of 100, then 900, then 100 again. Nobody changed its date filter or business definition. The second result included an update that another session later rolled back. A fast query can still produce an unreliable total.
WITH (NOLOCK) permits this dirty read. For reports used to reconcile orders, approve payments, or compare totals, choose an explicit consistency requirement before choosing an isolation level. This guide shows the failure in a disposable sandbox and explains how to keep concurrent activity from changing the meaning of your report.
What NOLOCK allows
NOLOCK is SQL Server's table hint for READUNCOMMITTED. It can read uncommitted changes, and scans can miss rows or read them more than once. It also takes schema stability locks, so its name does not promise that a query will never wait. These behaviors are documented in Microsoft's table hints reference.
For an aggregate, a dirty value becomes an ordinary-looking number. A successful export proves that the query completed; it does not prove that every contributing value was committed. Removing duplicates with DISTINCT cannot repair an amount that never became real.
Reproduce a dirty total with two sessions
Run this exercise only in a disposable SQL Server database named ContentGapSandbox. Ask your administrator to provide it, then connect two separate query windows to it. Do not use a production database. The exercise creates one shared table because a local temporary table is private to its session.
1. Create the fixture in Session A
The guards refuse another database, an existing transaction, or an existing demo table. Start with fresh connections; the examples use autocommit mode outside their explicit transactions.
IF DB_NAME() <> N'ContentGapSandbox'
THROW 51000, 'Use the disposable ContentGapSandbox database.', 1;
IF @@TRANCOUNT <> 0
THROW 51001, 'Use a fresh session without an open transaction.', 1;
IF OBJECT_ID(N'dbo.NolockReportDemo', N'U') IS NOT NULL
THROW 51002, 'Demo table already exists; inspect before proceeding.', 1;
SET IMPLICIT_TRANSACTIONS OFF;
SET IMPLICIT_TRANSACTIONS OFF;
CREATE TABLE dbo.NolockReportDemo (
order_id int NOT NULL PRIMARY KEY,
amount decimal(12,2) NOT NULL
);
INSERT INTO dbo.NolockReportDemo (order_id, amount) VALUES (1, 100.00);2. Read the baseline in Session B
SET IMPLICIT_TRANSACTIONS OFF;
SELECT SUM(amount) AS report_total
FROM dbo.NolockReportDemo WITH (NOLOCK);
-- Expected before the writer starts: 100.003. Hold an uncommitted update in Session A
Run the entire block. When the message appears, you have 45 seconds to repeat Session B's query. The writer rolls back automatically.
IF DB_NAME() <> N'ContentGapSandbox'
THROW 51000, 'Use the disposable ContentGapSandbox database.', 1;
IF @@TRANCOUNT <> 0
THROW 51001, 'Use a fresh session without an open transaction.', 1;
SET IMPLICIT_TRANSACTIONS OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.NolockReportDemo
SET amount = 900.00
WHERE order_id = 1;
RAISERROR (N'Read in Session B now: 45-second window.', 0, 1)
WITH NOWAIT;
WAITFOR DELAY '00:00:45';
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
THROW;
END CATCH;4. Compare the reader's results
Session B's NOLOCK query should return 900.00 during the waiting window. After Session A finishes, repeat it: the total returns to 100.00. The committed amount stayed 100 throughout. This demonstrates a dirty read, not a change in the metric definition.
Repeat the writer block and try the reader without the hint:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SUM(amount) AS report_total
FROM dbo.NolockReportDemo;With locking READ COMMITTED, this read waits for the writer's rollback and then returns 100.00. With READ COMMITTED SNAPSHOT already enabled, it can read the earlier committed 100.00 during the update. The database setting determines which behavior you observe; Microsoft's isolation-level reference explains that distinction.
If you cancel a batch or lose a connection, do not assume the catch block ran. Close the sandbox connection to abandon any unfinished transaction before repeating the exercise. TRY...CATCH has documented limitations, including client cancellations.
Inspect the settings without changing them
SELECT name,
is_read_committed_snapshot_on,
snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID();A value of 1 in is_read_committed_snapshot_on means RCSI is enabled. snapshot_isolation_state_desc = 'ON' means explicit SNAPSHOT transactions are allowed. They are separate options; a transitional state is not ready. See the sys.databases column definitions.
Choose the consistency boundary your report needs
| Mode | Report consequence |
|---|---|
| READ COMMITTED, RCSI off | Prevents dirty reads using locks. A later query may see newer committed data; it does not promise a single point-in-time snapshot. |
| READ COMMITTED, RCSI on | Each statement reads a committed snapshot. Separate statements can observe different committed states. |
| SNAPSHOT transaction | Related statements share a transaction snapshot, useful when a report runs several dependent queries. |
A report with two queries, “count orders” and “sum revenue,” needs more thought than two clean reads. A new committed order between them can make the outputs describe different populations. A single statement under RCSI has a statement boundary; wrapping separate RCSI statements in BEGIN TRANSACTION does not make them share one snapshot. See Microsoft's row-versioning guide.
Keep related reads together with SNAPSHOT
If the sandbox administrator has already enabled SNAPSHOT, this example reads the same total twice within one transaction. Its snapshot is established by the first data access. Replace the two reads with your report's related queries only after agreeing its required boundary.
IF DB_NAME() <> N'ContentGapSandbox'
THROW 51000, 'Use the disposable ContentGapSandbox database.', 1;
IF @@TRANCOUNT <> 0
THROW 51001, 'This block must own its transaction.', 1;
SET IMPLICIT_TRANSACTIONS OFF;
IF NOT EXISTS (
SELECT 1 FROM sys.databases
WHERE database_id = DB_ID() AND snapshot_isolation_state = 1
)
THROW 51003, 'SNAPSHOT must already be enabled by the administrator.', 1;
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT SUM(amount) AS first_total FROM dbo.NolockReportDemo;
SELECT SUM(amount) AS second_total FROM dbo.NolockReportDemo;
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
THROW;
END CATCH;The explicit reset matters because isolation settings belong to the connection. This example assumes a dedicated reporting connection whose agreed baseline is READ COMMITTED. Applications with another baseline should restore that baseline on success and failure. Microsoft's snapshot isolation documentation describes connection scope.
Make the production change with the database owner
Removing NOLOCK can expose waiting that the hint previously bypassed. Test report accuracy and completion time under representative concurrent writes, then agree an acceptable delivery window. Record the isolation mode alongside the report's parameters so the consistency promise is reviewable.
Database-level row-versioning settings belong to the DBA's change process. Version stores consume storage and resources; long transactions can retain old versions. Depending on database configuration, versions reside in tempdb or the database's persistent version store. Discuss capacity and monitoring before enabling anything. Microsoft's resource-usage guidance covers those costs.
RCSI does not remove every cause of blocking or deadlocks. Competing writers can still contend, and schema changes can still affect readers. Investigate the actual workload with the database owner rather than assuming that a reader setting fixes a writer problem. Microsoft's optimized-locking documentation explains how behavior also varies with enabled features.
Keep the report transaction limited to database reads. Commit before waiting for a person, formatting a spreadsheet, or sending an export. For historical reproducibility, retain the approved inputs or output; a transaction snapshot does not preserve yesterday's report indefinitely.
Once both sandbox sessions have finished, remove only the fixture created for this exercise:
IF DB_NAME() <> N'ContentGapSandbox' OR @@TRANCOUNT <> 0
THROW 51004, 'Finish the sandbox transactions before cleanup.', 1;
DROP TABLE dbo.NolockReportDemo;FAQ
Can I keep NOLOCK if the numbers usually look right?
A spot check cannot prove that concurrent updates were safe. If committed totals are part of the report's contract, use an isolation mode that provides that protection.
Does SNAPSHOT fix incorrect joins?
No. A consistent snapshot can still contain a duplicated join or a wrong business filter. Validate the query logic separately.
What should I read next?
Continue with SQL Server patterns for analysts, reliable recurring SQL reports, and reading SQL execution plans.
