Beyond NOLOCK: row versioning, RCSI and SQL Server 2025 optimized locking

WITH (NOLOCK) is usually added so that a slow report stops waiting on writers, or stops blocking them. On the surface it works: the query no longer waits. The price is higher than it looks. Under NOLOCK, a query can return data that is later rolled back, skip rows that exist, count other rows twice, or fail with error 601 when data moves during the scan.

SQL Server has had a better answer to reader/writer blocking for a long time, and SQL Server 2025 adds another layer on top of it. This article explains what NOLOCK really does, how row versioning with RCSI and SNAPSHOT replaces it, and where optimized locking fits in.

What NOLOCK actually does

NOLOCK is the same as READUNCOMMITTED. The query takes no shared locks and isn't blocked by exclusive locks that other transactions hold.

The dirty read part is well known. You read a row that another transaction changed, that transaction rolls back, and you've shown a value that never officially existed.

The less familiar problem is structural. Microsoft's table hints documentation says this directly: allowing dirty reads can "cause users to see records twice (or not at all)". The cause is physical. Under NOLOCK, the engine can choose an allocation-order scan, which reads pages in the order they were allocated instead of in index order. If a concurrent insert or update causes a page split while you're scanning, rows that move to a new page can land behind your scan position (and be missed) or ahead of it (and be read again). Your SUM(amount) can be wrong even if every transaction in the system eventually commits.

There's also a hard failure. When data moves under a NOLOCK scan, the query can fail with error 601, "Could not continue scan with NOLOCK due to data movement." The documentation recommends handling it the way you handle a deadlock (error 1205): retry the statement. So the hint you added to avoid waiting now means you need retry logic.

Two more things the hint does not do:

• It doesn't avoid every lock. Queries with NOLOCK still take Sch-S (schema stability) locks, so they're blocked by DDL that holds a Sch-M lock, such as an index rebuild that isn't online.
• It has no effect on writes. NOLOCK can't be applied to the target of INSERT, UPDATE or DELETE. The optimizer ignores it on the target table of UPDATE/DELETE, and Microsoft has said support for using it there will be removed.

The original rationale for NOLOCK

NOLOCK comes from a time when the main alternative was locking READ COMMITTED. Under it, a long report really did block OLTP writes, or get blocked by them. Row versioning has been available since SQL Server 2005. Today it's the default behavior in Azure SQL Database, and SQL Server 2025 builds even more on it. The trade-off that justified NOLOCK no longer applies.

Row versioning with RCSI

ALTER DATABASE Sales SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'Sales';

With RCSI, every statement running under READ COMMITTED reads the last committed version of each row as of the moment the statement started. Readers don't take shared locks, so they don't block writers, and writers don't block readers. Unlike NOLOCK, you don't see uncommitted data, and you don't get the missing or duplicate rows caused by data moving during the scan.

The switch needs exclusive access to the database, so plan a maintenance window. WITH ROLLBACK IMMEDIATE ends other sessions.

The cost of RCSI

• Version store. Updated and deleted rows keep their previous versions until no active statement needs them. Without accelerated database recovery (ADR), those versions are stored in tempdb. With ADR (SQL Server 2019 and later), they go into the persistent version store inside the user database. Either way, a long-running transaction stops cleanup, so monitor it.
• Row overhead. Rows that are versioned carry extra bytes for versioning information, which can cause some page splits right after you enable it.
• Application assumptions. This is the important one. Code that reads a value and then writes based on it, assuming the read blocked until a concurrent writer finished, is now reading the previous committed version. Typical examples are checking a balance and then debiting it, or "SELECT the next free number and then INSERT it". These need explicit locking (UPDLOCK, or an atomic UPDATE ... OUTPUT). Queue tables that use READPAST will also fail under RCSI unless you add READCOMMITTEDLOCK.

SNAPSHOT isolation is a different thing

ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- every statement here sees the same point-in-time view
COMMIT;

RCSI gives each statement a consistent view. SNAPSHOT gives the whole transaction one consistent view, which is useful for a multi-query report that needs to agree with itself. Sessions have to opt in with SET TRANSACTION ISOLATION LEVEL SNAPSHOT. If a snapshot transaction tries to update a row that another transaction changed after the snapshot started, it fails with an update conflict (error 3960), so writers that use it need retry logic. My default: RCSI for the database, and SNAPSHOT only where a transaction really needs one consistent view.

SQL Server 2025: optimized locking

RCSI fixes reader/writer blocking. Writer/writer blocking and lock memory are what optimized locking addresses. Everything below is from Microsoft Learn's "Optimized locking" article.

Availability:

• SQL Server 2025 (17.x): available, off by default, enabled per database.
• Azure SQL Database, SQL database in Microsoft Fabric, and Azure SQL Managed Instance with the Always-up-to-date or SQL Server 2025 update policy: always enabled.
• SQL Server 2022 and earlier, and Managed Instance with the 2022 update policy: not available.

ALTER DATABASE Sales SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE Sales SET OPTIMIZED_LOCKING = ON;

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_on;

ADR is a prerequisite. You must enable it first, and you can't turn it off while optimized locking is on.

It has two parts:

• Transaction ID (TID) locking. Row and page locks are still taken during modification, but each one is released as soon as the row is modified. The only lock held until commit is a single X lock on the transaction's ID. Lock memory drops sharply, and lock escalation becomes much less likely.
• Lock after qualification (LAQ). Predicates are evaluated against the latest committed row version without taking U locks, and a lock is taken only on rows that qualify. Two updates touching different rows stop blocking each other. LAQ works only when RCSI is enabled. That's one more reason to do RCSI first.

Limitations and behavior changes

• Locking hints such as UPDLOCK, HOLDLOCK, XLOCK and READCOMMITTEDLOCK are honored, but they reduce the benefit. LAQ isn't used for statements that carry them.
• LAQ isn't used for MERGE, for isolation levels other than READ COMMITTED, for tables with a columnstore index, for DML with variable assignment, or for OUTPUT clauses that return a result set or insert into a table variable.
• The documentation describes a behavior change. Under RCSI with LAQ, an UPDATE ... WHERE b = 2 can evaluate the last committed value and skip a row that a concurrent, still-uncommitted transaction is about to set to 2. Without LAQ, the update would wait and then modify that row. If your workload depends on strict ordering between concurrent transactions, Microsoft recommends REPEATABLE READ or SERIALIZABLE for those transactions.
• It isn't used for tempdb or temporary tables, or on read-only replicas.

Pitfalls

• Removing NOLOCK without enabling RCSI. You'll get the blocking back. Enable RCSI first, test, then remove the hints.
• Ignoring long transactions. One forgotten open transaction can keep the version store growing for hours.
• Hints hidden in views. A NOLOCK written inside a view applies to every query that uses the view, and a lock hint placed on a view is propagated to the tables underneath it. Search the database objects, not just the application code.
• Assuming optimized locking is on. On SQL Server 2025 it isn't until you turn it on, and LAQ stays inactive without RCSI.

Summary and checklist

NOLOCK trades correctness for not waiting, and row versioning makes that trade unnecessary. If the problem is readers blocking writers, enable RCSI and remove the hints. Use SNAPSHOT where a transaction needs one consistent view. On SQL Server 2025, add optimized locking to reduce blocking between writers as well.

1. Find every NOLOCK and READUNCOMMITTED in code, views and procedures.
2. Review read-then-write patterns and READPAST queues for RCSI compatibility.
3. Enable READ_COMMITTED_SNAPSHOT in a maintenance window and monitor the version store.
4. Remove the hints and measure blocking before and after.
5. Use SNAPSHOT for transactions that need one consistent view across statements.
6. On SQL Server 2025: enable ADR, then OPTIMIZED_LOCKING, and keep unnecessary locking hints out of hot paths.