SQL Server NOLOCK: Faster Reads, Wrong Answers, and What to Use Instead

WITH (NOLOCK) looks like a performance fix and works like a correctness trade. It tells SQL Server to stop waiting for other transactions to finish before reading. Your report stops hanging. It also starts reading data that may never have been committed, can count the same row twice, and occasionally dies with an error you've never seen.

SQL NOLOCK hints tend to arrive one blocking incident at a time, and each one quietly changes what that query is allowed to return. This guide covers what the hint really does, the four ways it goes wrong, how the SQL Server isolation levels compare, and the usual alternative: read committed snapshot isolation (RCSI). It's current for SQL Server 2016 through SQL Server 2025 and Azure SQL, checked against Microsoft's documentation in October 2026.

The short version

• NOLOCK is the same as the READUNCOMMITTED table hint. A query using it reads uncommitted changes (dirty reads), can skip rows or read them twice, and can fail with error 601.
• It isn't "no locks." Every query, NOLOCK included, takes a schema stability lock, so it still waits behind schema changes such as an offline index rebuild or ALTER TABLE.
• On the target table of an UPDATE or DELETE, SQL Server ignores NOLOCK, and Microsoft plans to remove support for that usage.
• RCSI stops readers and writers from blocking each other without dirty reads. It costs tempdb space (or space in the database, with accelerated database recovery) and changes behavior for code that relied on blocking. Test it, then turn it on.
• Azure SQL Database already runs RCSI by default. Ordinary reads there already avoid writer blocking, while NOLOCK keeps the dirty-read risk.
• SQL Server 2025 adds optimized locking, which cuts writer-on-writer blocking too. It's off by default.

What NOLOCK actually does

Under SQL Server's default isolation level, READ COMMITTED, a query takes a shared lock on each row or page as it reads it. If another transaction has changed that row and hasn't committed yet, it holds an exclusive lock, and your query waits. That wait is "blocking."

NOLOCK skips that step for one table in one query. No shared locks are requested, and exclusive locks held by other transactions don't stop the read. Microsoft's table hints page defines NOLOCK as equivalent to READUNCOMMITTED, and the session-level equivalent is SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, which has the same effect as putting NOLOCK on every table in every SELECT in the transaction.

The hint goes on each table, not on the query:

-- NOLOCK applies only to the table it follows.
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM dbo.Orders AS o WITH (NOLOCK)
JOIN dbo.Customers AS c WITH (NOLOCK)
    ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2026-10-01';

-- Session-wide equivalent: every table in every SELECT reads uncommitted data.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

You'll also see FROM dbo.Orders (NOLOCK), without WITH. SQL Server still accepts that form for a single hint, but omitting WITH is a deprecated feature. Write WITH (NOLOCK).

Most of the speed people notice comes from not waiting. NOLOCK also skips the work of taking shared locks, so it can shave some time off big scans, but how much depends on the workload. Measure before you credit the hint.

Four ways NOLOCK gives you the wrong answer

1. Dirty reads: data that never existed

Here's a two-session test you can run on any dev server. Use two fresh query windows, so no earlier SET TRANSACTION ISOLATION LEVEL carries over:

-- Setup
CREATE TABLE dbo.Accounts (AccountID int PRIMARY KEY, Balance decimal(12,2) NOT NULL);
INSERT dbo.Accounts (AccountID, Balance) VALUES (1, 1000.00);

-- Session 1: start a transfer, don't commit yet
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1;

-- Session 2: read with NOLOCK
SELECT Balance FROM dbo.Accounts WITH (NOLOCK) WHERE AccountID = 1;   -- returns 500.00

-- Session 1: the transfer fails
ROLLBACK;

Session 2 reported a balance of 500.00 that never existed in committed data. Run the same SELECT without the hint and with RCSI off, and session 2 waits for session 1 to finish, then returns 1000.00. Run it without the hint with RCSI on, and it returns 1000.00 immediately, without waiting. That last case is the whole argument of this guide.

2. Rows skipped or read twice

This one surprises people because no transaction has to roll back. Microsoft documents two ways it happens:

• Index key changes during a scan. If another session updates a row's index key while you're scanning that index, the row can move ahead of your scan and be read again, or move behind it and be missed. This happens under READ UNCOMMITTED and under plain locking READ COMMITTED too, because shared locks are released as soon as each row is read. Microsoft's fix is SERIALIZABLE or row versioning (RCSI or SNAPSHOT).
• Allocation-order scans. Under READ UNCOMMITTED, SQL Server can read pages in allocation order. If another transaction splits a page during that scan, you can miss rows. Microsoft notes this doesn't happen under READ COMMITTED.

On a busy table, that means a COUNT(*) or a SUM that's off by a few rows, with no error and no way to tell from the result.

3. Error 601: the query just dies

Msg 601: Could not continue scan with NOLOCK due to data movement.

The query hit data that another transaction moved or deleted mid-scan. Microsoft's advice is to treat it like a deadlock (error 1205) and retry, or stop using NOLOCK. If your application doesn't retry, a user gets an error from a report that "can't block."

4. It still waits behind schema changes

NOLOCK only skips data locks. Every query, including NOLOCK queries, takes a schema stability (Sch-S) lock while it compiles and runs. A schema change such as ALTER TABLE or an offline index rebuild needs a schema modification (Sch-M) lock, and the two don't mix. Your NOLOCK report waits behind the DDL, and a long NOLOCK report makes the DDL wait, which in turn queues up everything behind it.

And it does nothing on UPDATE and DELETE targets

People write UPDATE o SET ... FROM dbo.Orders AS o WITH (NOLOCK) expecting a non-blocking update. SQL Server ignores NOLOCK and READUNCOMMITTED on the target table of an UPDATE or DELETE: the rows a statement changes stay protected against other writers, whatever the isolation level. Microsoft also plans to remove support for that usage, NOLOCK applied to the UPDATE or DELETE target through the FROM clause, in a future version. So when someone asks "is NOLOCK deprecated?", the answer is: not for SELECT, yes on an UPDATE/DELETE target, and yes for the no-WITH syntax.

SQL Server isolation levels compared

An isolation level decides two things: which concurrency side effects a reader can see, and whether readers and writers block each other. The first three columns are Microsoft's. The last two are what you feel in production, for ordinary data reads against concurrent inserts, updates and deletes, with no overriding hints. Schema locks can still block at every level.

Three things the table doesn't show:

• Conflicting writes still block. Whatever the isolation level, a transaction protects the rows it changes until it commits. Without optimized locking (SQL Server 2025, covered below), that means exclusive row or page locks held to the end of the transaction. RCSI and SNAPSHOT fix reader-writer blocking, not two sessions updating the same rows.
• RCSI is statement-level; SNAPSHOT is transaction-level. Under RCSI each statement sees data as of the moment that statement started. Under SNAPSHOT every statement sees data as of the transaction's first data access (plus the transaction's own changes).
• Defaults differ by platform. SQL Server and Azure SQL Managed Instance default to locking READ COMMITTED, with RCSI and snapshot isolation off. Azure SQL Database turns both on by default.

RCSI: stop blocking without dirty reads

Read committed snapshot isolation changes how READ COMMITTED works for the whole database. Instead of taking shared locks, each statement reads the last committed version of every row as of the moment the statement started. Readers stop waiting for writers, writers stop waiting for readers, and nobody reads uncommitted data. Your application code doesn't change: sessions keep using the default READ COMMITTED.

Check where each database stands:

SELECT name,
       is_read_committed_snapshot_on,        -- 1 = RCSI on
       snapshot_isolation_state_desc,        -- ON / OFF / IN_TRANSITION_TO_ON / IN_TRANSITION_TO_OFF
       is_accelerated_database_recovery_on   -- 1 = ADR on (SQL Server 2019 and later)
FROM sys.databases
ORDER BY name;

On SQL Server 2016 or 2017, delete the last column and the comma before it.

On SQL Server and Azure SQL Managed Instance, turning it on is one statement, but it needs the database to itself (Azure SQL Database already has it on):

-- Run in a maintenance window. RCSI can only change when no other connection is
-- active in the database; ROLLBACK IMMEDIATE rolls back every open transaction to get there.
ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

What it costs, before you flip it:

• A version store. Every update and delete keeps the previous version of the row so readers can see it. The version store lives in tempdb, or inside the database itself (the persistent version store) if accelerated database recovery (ADR, SQL Server 2019 and later) is on. If a tempdb version store runs out of space, writes keep working but reads that need an old version can fail (error 3958). With ADR, the versions take space in the database's own data files instead, so plan that space and monitor it (sys.dm_tran_persistent_version_store_stats). Size whichever store you'll use first. Without ADR, our tempdb best practices and tempdb and VM scaling posts cover the tempdb side. With ADR, see Microsoft's guide to managing accelerated database recovery for the persistent version store.
• 14 bytes per row. Each row gains up to 14 bytes of versioning information the first time it's modified with RCSI on. Microsoft's example: a table with 100-byte rows can grow by up to 14%, and the extra bytes can cause page splits.
• Long transactions pin versions. Versions stay until no active transaction can need them. A long-running transaction that reads under row versioning, or generates versions itself, stops cleanup while every other session keeps adding versions.
• Updates still look for rows the old way. Without optimized locking (more below), an UPDATE under RCSI still takes update locks to find its rows, so two writers aimed at the same rows still queue.

And what it changes in behavior:

• Readers stop waiting for in-flight writes. Code that relied on a read blocking until another session's transaction finished, then seeing the new value, now sees the old committed value immediately. Review read-then-write logic, such as "check stock, then decrement" or "grab the next job from a queue table." The safest fix is a single atomic statement (UPDATE ... SET Stock = Stock - 1 WHERE ProductID = @id AND Stock >= 1); otherwise read with UPDLOCK inside the same transaction as the write. READCOMMITTEDLOCK brings back the old locking read for one statement, but on its own it doesn't make a multi-statement operation safe. Microsoft's general guidance is not to assume strict ordering between transactions under row versioning without locking hints.
• One rare gotcha: memory-optimized tables created with DURABILITY = SCHEMA_ONLY lose their data when READ_COMMITTED_SNAPSHOT is changed with ALTER DATABASE.

None of these is a reason to skip RCSI. They're the reasons to test it on a copy of production with a real workload first. Watch the version store while you do:

-- Version store space in tempdb, per database (SQL Server 2016 SP2 and later).
-- With ADR on, versions live in each database's persistent version store instead:
-- see sys.dm_tran_persistent_version_store_stats.
SELECT DB_NAME(database_id)         AS database_name,
       reserved_space_kb / 1024.0   AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_space_kb DESC;

After RCSI is on, remove the NOLOCK hints. A table hint overrides the database setting, so a query that says WITH (NOLOCK) keeps reading uncommitted data even with RCSI on. Turning on RCSI and leaving 400 NOLOCK hints in place gets you the version store cost and keeps the wrong answers.

SNAPSHOT isolation: when a whole transaction must agree

Some work needs every query in a transaction to see the same moment in time: a month-end report that reads order headers and then order lines, for example. Under RCSI, rows committed between those two statements can make the totals disagree. SNAPSHOT isolation fixes that.

-- Once per database. Waits for transactions already running in the database to finish.
ALTER DATABASE [Sales] SET ALLOW_SNAPSHOT_ISOLATION ON;

-- In the report's session, connected to Sales, before the transaction starts:
USE [Sales];
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    SELECT COUNT(*)         AS order_count
    FROM dbo.Orders
    WHERE OrderDate >= '2026-10-01' AND OrderDate < '2026-11-01';

    SELECT SUM(ol.LineTotal) AS order_total
    FROM dbo.OrderLines AS ol
    JOIN dbo.Orders     AS o ON o.OrderID = ol.OrderID
    WHERE o.OrderDate >= '2026-10-01' AND o.OrderDate < '2026-11-01';
COMMIT;

-- COMMIT doesn't reset the isolation level; set it back if the session continues.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Unlike RCSI, SNAPSHOT is opt-in per session, so existing code keeps behaving as before. Turning on ALLOW_SNAPSHOT_ISOLATION does start row versioning for every write in the database, so the version store costs above still apply.

The catch is writes. If a SNAPSHOT transaction tries to change a row that another transaction modified and committed after the snapshot began, SQL Server ends the transaction with error 3960 ("Snapshot isolation transaction aborted due to update conflict"). Your code has to catch it and retry. Read-only reports never hit 3960, but they're not immune to the second problem: if someone runs DDL, such as ALTER TABLE or an index rebuild, on an object the transaction uses after it started, the SNAPSHOT transaction fails with error 3961, because SQL Server doesn't version metadata.

SQL Server 2025, Azure SQL and readable secondaries

Optimized locking (SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance) attacks the blocking RCSI leaves behind: writers blocking writers.

• Transaction ID locking. A writer releases each row and page lock as soon as the row is changed (and in some cases skips them entirely). Only one lock, on its transaction ID, is held until commit. Lock memory drops and lock escalation becomes much less likely.
• Lock after qualification (LAQ). With RCSI on and the session at READ COMMITTED, an eligible UPDATE or DELETE checks its WHERE clause against the last committed version of each row without taking a lock, and only locks rows that qualify. Two sessions updating different rows of the same table stop blocking each other. LAQ doesn't apply everywhere: locking hints such as UPDLOCK, tables with a columnstore index, MERGE, and some OUTPUT and variable-assignment statements fall back to the old behavior, and SQL Server can switch LAQ off when retries get expensive.

Availability matters here:

-- SQL Server 2025: 1 = on, 0 = off, NULL = not available
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_on;

LAQ can change results for code that depends on transaction ordering. Microsoft's own example: one session sets b = 2 while another runs UPDATE ... WHERE b = 2. Without LAQ, the second update waits, then changes the row. With LAQ, it evaluates the predicate against the last committed value (b = 1), skips the row and finishes without blocking. If your code depends on that kind of ordering, Microsoft recommends REPEATABLE READ or SERIALIZABLE for it.

Azure SQL Database turns on RCSI, snapshot isolation and optimized locking by default. Ordinary reads already avoid writer blocking. Any further speed difference from NOLOCK depends on the workload, and the dirty-read risk stays.

Readable secondaries in an Always On availability group map every query to SNAPSHOT isolation and ignore all locking hints, NOLOCK included. If reports run on a readable secondary, the hints in them do nothing there. More in our Always On availability groups guide.

When NOLOCK is fine, and when to use READPAST instead

NOLOCK is a tool, not a crime. It's reasonable when a wrong or approximate answer for a moment costs nothing:

• Diagnostic queries against DMVs and system catalog views, like the ones in our DMV queries for slow SQL Server queries, where an inexact answer is fine. (Metadata access has its own locking rules, so the hint isn't a guarantee these never wait.)
• Rough row counts and monitoring dashboards that a person glances at and nobody acts on automatically.
• One-off investigation queries on production, where you knowingly accept uncommitted values, missing or doubled rows, and the occasional error 601 in exchange for not joining a blocking chain.

Keep it away from:

• Money, inventory, entitlements, anything a customer sees as a fact.
• Any read whose result feeds a write or a decision: "if the balance is enough, then...".
• ETL and reconciliation jobs that copy data into another system, where a skipped or doubled row becomes permanent.

If what you really want is "don't wait for rows someone else is working on," look at READPAST. It skips rows that other transactions hold row locks on, instead of reading their uncommitted values. (It doesn't skip page or table locks; those it still waits for.) Microsoft's example: if a table holds 1, 2, 3, 4, 5 and another transaction is changing 3, SELECT * FROM T1 WITH (READPAST) returns 1, 2, 4, 5. Microsoft names work-queue tables as the main use: several workers each skip past items another worker has locked.

-- Lists ready jobs, skipping any another worker has locked.
-- It does NOT claim them: two workers can still read the same job IDs.
SELECT TOP (10) JobID
FROM dbo.JobQueue WITH (READPAST)
WHERE Status = 'Ready'
ORDER BY JobID;

To actually hand a job to one worker, select and mark it as claimed in one atomic statement, or hold update locks from the read through the status change in a single transaction. READPAST works only under locking READ COMMITTED or REPEATABLE READ. If the database has RCSI on and the session uses READ COMMITTED, Microsoft says to add the READCOMMITTEDLOCK hint (and drop any READCOMMITTED hint).

Find NOLOCK and blocking on your own server

Four queries to see where you stand. They're written for SQL Server and Azure SQL Managed Instance. To see other sessions, the DMV queries need VIEW SERVER STATE (SQL Server 2019 and earlier) or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later). On Azure SQL Database, DMV permissions and visibility are database-scoped and work differently, so an empty result there doesn't prove nothing is blocked.

Where is NOLOCK in your code? Run this in each user database. It's a first-pass text search of the stored procedures, views, functions and DML triggers you have permission to see. It also matches comments, skips encrypted modules and database-level DDL triggers, and can't see SQL that your application sends directly; search the application's repository for that. On a case-sensitive collation, also search for lowercase nolock.

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS object_name,
       o.type_desc
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%NOLOCK%'
   OR m.definition LIKE N'%READUNCOMMITTED%'
   OR m.definition LIKE N'%READ UNCOMMITTED%'
ORDER BY schema_name, object_name;

Which sessions run READ UNCOMMITTED? This shows each session's isolation setting. Table-level hints such as WITH (NOLOCK) don't appear here, only SET TRANSACTION ISOLATION LEVEL choices.

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       CASE s.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'READ UNCOMMITTED'
            WHEN 2 THEN 'READ COMMITTED'
            WHEN 3 THEN 'REPEATABLE READ'
            WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT'
       END AS isolation_level
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
ORDER BY s.transaction_isolation_level, s.session_id;

Who is blocked right now, and on what?

SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time              AS wait_time_ms,
       r.wait_resource,
       DB_NAME(r.database_id)   AS database_name,
       SUBSTRING(t.text,
                 (r.statement_start_offset / 2) + 1,
                 ((CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(t.text)
                        ELSE r.statement_end_offset
                   END - r.statement_start_offset) / 2) + 1) AS blocked_statement
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

Lock waits show up as LCK_M_* wait types (LCK_M_S is a reader waiting for a shared lock). Our wait stats guide explains how to read the rest.

Who is at the head of the chain? The session that causes the pile-up often isn't running anything. It opened a transaction, changed some rows, and never committed.

-- Sessions that block others but aren't blocked themselves.
-- status = 'sleeping' with open_transaction_count > 0 is the classic
-- "application opened a transaction and walked away" pattern.
SELECT s.session_id,
       s.status,
       s.login_name,
       s.host_name,
       s.program_name,
       s.open_transaction_count,
       s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r
              WHERE r.blocking_session_id = s.session_id)
  AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r2
                  WHERE r2.session_id = s.session_id
                    AND r2.blocking_session_id > 0);

These are snapshots: they show who is blocked by whom right now and the likely heads of each chain. (A negative blocking_session_id, such as an orphaned distributed transaction, needs separate investigation.) Before you blame a session, look at what it's running, how long its transaction has been open, and which locks it holds. Most of the time the head is a writer that holds its locks too long, and that's where the real fix lives. NOLOCK on the readers only hides the symptom.

A safe path off NOLOCK

1. Measure first. Capture blocked requests and LCK_M_* waits during your busiest hour, and time the key queries with and without their hints on a test copy under realistic load. That tells you what the hints are actually buying.
2. Fix the writers. Find the head blockers. Usually it's a transaction that stays open across application round trips, or an UPDATE or DELETE that scans a whole table because its WHERE clause has no supporting index, so it locks far more rows than it changes. Shorter transactions, the right index, and batching big deletes shrink the blocking at its source. Our query optimization tips cover reducing how many rows an update blocks, and for how long.
3. Check version-store headroom: tempdb if ADR is off, the database's own data files if ADR is on. Then turn on RCSI on a production-sized test copy and run a real workload. Watch the version store (tempdb or the persistent version store) and any code that reads and then writes.
4. Review blocking-dependent code: queue tables, "check then update" logic, sequence or counter tables. For changes to existing rows, use an atomic conditional UPDATE, or read with UPDLOCK in the same transaction as the write. If correctness depends on a row staying absent or a range staying unchanged until the write, use SERIALIZABLE/HOLDLOCK protection inside the transaction. If you only need to prevent duplicate keys ("insert if it doesn't exist"), a unique constraint plus handling of the duplicate-key error does that.
5. Enable RCSI in a maintenance window. Drain connections first, or use WITH ROLLBACK IMMEDIATE and accept that open transactions roll back.
6. Remove NOLOCK hints, starting with queries that touch money, inventory and anything fed into another system.
7. On SQL Server 2025, test ADR plus optimized locking next, for the writer-on-writer blocking RCSI doesn't touch.

If deadlocks are part of the picture too, our post on reading deadlocks from the system_health session shows how to see them without new tooling, and our guide to SQL Server timeouts covers application query timeouts, which blocking often causes (many clients default to a 30-second command timeout).

When to call in help

A codebase full of NOLOCK hints with users still complaining about blocking is fixable. Our SQL Server performance tuning is led by senior DBAs with 15+ years of experience. Every change has full rollback capability and documented rollback procedures, and you get before-and-after metrics showing what improved.

Not sure blocking is your problem? Our read-only SQL Server Health Check reviews top wait stats, performance bottlenecks and TempDB configuration.