Optimized Locking in SQL Server 2025: Where Did My Locks Go?

It is unsettling when locks that you have used for diagnosis for years are missing. Yet optimized locking is not a revolution out of nowhere but the third step of a line that began with SQL Server 2019. Once you know the steps, you understand why the locks are missing and where they are still there.

In 60 seconds

• Optimized locking has two parts: TID locking and lock after qualification (LAQ). It acts on the locks of INSERT, UPDATE, DELETE and MERGE.
• TID locking: A writing transaction holds only an X lock on its own transaction ID until commit. It releases row and page locks right after the change.
• LAQ: An UPDATE or DELETE checks its WHERE condition against the last committed version of a row without locking. Only what actually qualifies is locked. LAQ works only with read committed snapshot isolation (RCSI).
• The prerequisite is accelerated database recovery (ADR), explained in the ADR deep dive. In SQL Server 2025, optimized locking is off by default; in Azure SQL Database it is on.
• What it is not: not a replacement for RCSI, not a free pass against deadlocks, and not the end of lock escalation. And it is one of the rare performance features that can change the result in edge cases.

Why it feels unfamiliar

Three separate options work together: ADR, RCSI and optimized locking. Many explanations treat them as one. On top of that, you see less after turning it on than before. And the default depends on the platform: a demo in Azure SQL Database shows something different from the same code on a freshly installed SQL Server 2025. The line from 2017 to 2025 is on the overview; this page is about the two building blocks of 2025.

Building block 1: TID locking

Every row carries the ID of the transaction that last changed it (the transaction ID, or TID). Optimized locking uses this as follows:

1. The writing transaction takes an X lock on its own TID and holds it until commit.
2. For each row, it briefly takes the usual row lock, changes the row and releases the row lock immediately.
3. If another transaction wants to change that row, it sees the TID in the row. If that transaction is still running, it waits with an S lock on the TID, not on the row.

The protection stays the same, because nobody overwrites an uncommitted change. But only one lock is held instead of thousands. Lock memory drops, and the escalation threshold is reached far less often. It still exists, though.

In the lab, from 1 to 50,000 changed rows, it stayed at the XACT lock and one intent lock on the table, as long as the rows had been changed before. Without optimized locking, 51,391 held locks took up about 7.8 MB of lock memory, roughly 150 bytes per lock. The threshold counts per index: an UPDATE of an indexed column over 2,000 rows held 6,000 key locks without escalating. In LAB-10, an UPDATE over 1,000 rows held 1,060 locks with ADR and RCSI, and 3 with optimized locking.

The exception: pages that split within the transaction. If a transaction changes rows on full pages for the first time, then under ADR the record grows by the version information (from 234 to 255 bytes in the lab), the page splits, and for every page split in this transaction, a page lock (PAGE IX) remains until commit. In the lab, after loading, there were exactly as many page locks as new pages: 59 for 2,000 rows, 1,839 for a scan over the whole table, 56 after an index rebuild. On the second run over the same rows: zero. Compilation, plan cache, session, Query Store or a restart of the instance change nothing about this; only fresh rows trigger it. For skip index locks, Microsoft names rows on pages that were split in the same transaction as an exception; that the page lock then remains until the end of the transaction was measured, not documented. Without RCSI, the effect occurred in the lab only with the scan.

This exception has a consequence: lock escalation still exists with optimized locking. An UPDATE over 100,000 rows on a freshly loaded table split practically every page in the lab (from 2,870 to 5,715 pages), held page locks as a result, and escalated to an exclusive table lock in 18 of 18 first runs on two builds, even with ROWLOCK. The 36 follow-up runs on the same rows never escalated. LOCK_ESCALATION = DISABLE prevents the escalation; in that case, 2,857 page locks remain until commit. This is typical after a load or after a rebuild with a fill factor of 100. We did not measure whether free space on the pages reduces the effect.

With optimized locking v2 (SQL Server 2025, Azure SQL), SQL Server can skip the short row and page locks entirely for certain INSERT and UPDATE cases (skip index locks). This applies only as long as no query using REPEATABLE READ, SERIALIZABLE or equivalent hints reads along on the page, and only with RCSI.

Building block 2: Lock after qualification

Without LAQ, an UPDATE … WHERE works like this: it reads every candidate row with a U lock, checks the condition and converts the lock to X on a match. If a row is locked, it waits, even if that row does not meet the condition at all. This also applies under RCSI: RCSI protects readers, but writers there still qualify rows through U locks on the current row.

With LAQ, the UPDATE checks the condition without a lock against the last committed version. Only rows that match are locked and changed. If a row changes between the check and the lock, it is checked again. If necessary, SQL Server restarts the statement internally, visible as the extended event lock_after_qual_stmt_abort.

The typical result: two UPDATEs that hit different rows through a scan no longer block each other. In the lab, session A changes row 50,000 and keeps its transaction open. Session B changes a completely different row through a scan, with no suitable index:

Of 16 configurations across four versions, only the last lets session B through. In LAB-10, the scan writer waited a median of 1,296.94 ms with ADR and RCSI and 8.04 ms with optimized locking; under load, throughput rose from 52.89 to 117.06 operations/s.

LAQ does not apply in these cases:

• without RCSI or under an isolation level other than READ COMMITTED;
• with UPDLOCK, XLOCK, HOLDLOCK or READCOMMITTEDLOCK;
• with MERGE, variable assignment or OUTPUT as a result set. Microsoft also lists OUTPUT … INTO a table variable as an exclusion; in our tests on SQL Server 2025 CU9 and CU10, however, only OUTPUT to the client blocked (LCK_M_S_XACT_MODIFY). OUTPUT … INTO a table variable, a temporary table or a permanent table passed the locked record in 3 of 3 runs per build. We do not know whether the documentation is imprecise or the behavior has changed;
• on tables with a columnstore index;
• for plans with more than one seek or scan operator for the rows to be changed;
• when the LAQ heuristic detects too much wasted work. Since v2, LAQ is then turned off per plan and this is recorded in Query Store; before that, it was turned off for the whole database.

The one change in behavior

A table without an index contains one row with a = 1, b = 1.

• Without LAQ, session 2 waits for session 1. After its commit, it sees b = 2, the condition matches, and the result is b = 3.
• With LAQ, session 2 checks against the last committed version, b = 1. The row does not match, session 2 does not wait and changes nothing. The result is b = 2.

Microsoft documents this case itself and puts it in perspective: under row-versioning-based isolation levels, applications may not assume a fixed execution order without lock hints anyway. If you need one, use REPEATABLE READ or SERIALIZABLE. Reproduced exactly in the lab: in all configurations without LAQ, including optimized locking without RCSI, it ends with b = 3; with LAQ, it ends with b = 2, in three of three runs.

This is the most important reason not to turn on optimized locking without testing. It is not a reason to forgo it.

What stays as it was

• Readers under classic READ COMMITTED. Optimized locking affects DML locks. Readers not waiting for writers is still RCSI's job. In the lab, a SELECT under READ COMMITTED with optimized locking waited for the changed row, with the new wait type LCK_M_S_XACT_READ, and read the new value after the commit. Under RCSI, it did not wait and read the old one. The documentation does not describe this explicitly.
• Conflicts on the same row. Anyone who wants to change a row that an open transaction has changed still waits, now on that transaction's TID (LCK_M_S_XACT_MODIFY). The result is correct afterward in all configurations.
• Deadlocks. Certain patterns, especially those with U locks, disappear. But two transactions that change each other's rows crosswise still run into a deadlock; what the graph then looks like is shown in SQL Server Deadlock: Read the Graph, Understand Error 1205.
• Lock hints. UPDLOCK, READCOMMITTEDLOCK or REPEATABLE READ still block, just with the new wait types.
• Lock escalation. The thresholds are unchanged; they are just rarely reached. Under REPEATABLE READ, SERIALIZABLE and with lock hints, transactions still hold row and page locks until the end.
• Schema locks and other object locks stay as they are.
• tempdb and temporary tables do not use optimized locking, nor do readable secondaries.

The option matrix

All cells were measured (lab E1 to E4, E11).

What you now see in diagnostics

• sys.dm_tran_locks: resource type XACT. The owner holds an X lock with resource_associated_entity_id = 0. A waiter shows the XACT resource in resource_description, followed by the affected KEY or RID resource. The snapshot query on the overview page summarizes this per session.
• Wait types: LCK_M_S_XACT_MODIFY (waiting with intent to modify), LCK_M_S_XACT_READ (with intent to read), rarely LCK_M_S_XACT. If you filter blocking dashboards only on LCK_M_U, LCK_M_X and LCK_M_S, you overlook these waits.
• wait_resource in sys.dm_exec_requests, for example XACT: 23:2476:0 KEY: 23:72057594049593344 (…).
• Deadlock report: The <xactlock> element contains an <UnderlyingResource>. Older parsers may not know it.
• Extended events: lock_after_qual_stmt_abort shows LAQ restarts. locking_stats regularly reports, per database, the status, escalations and reasons for queries without LAQ. locking_stats2 provides statistics on v2.
• Query Store: sys.query_store_plan_feedback with feature_desc = 'LAQ Feedback'.
• Blocked process report: It shows waitresource="XACT: … KEY: …" and lockMode="S", but no wait type. Whether a reader or a writer is waiting, you can only tell from the DMVs. Be careful when filtering: an extended events filter on the database name suppresses the report entirely, because a system task in the context of master produces it.

Enabling, checking, rolling back

-- 1. ADR (prerequisite), see the ADR deep diveALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON; -- 2. Optional, but required for LAQ: RCSIALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT = ON; -- 3. Optimized lockingALTER DATABASE [MyDatabase] SET OPTIMIZED_LOCKING = ON; -- CheckSELECT name, is_accelerated_database_recovery_on,       is_read_committed_snapshot_on, is_optimized_locking_onFROM sys.databases WHERE name = N'MyDatabase';

• Each of these steps needs a moment in which no other connections are active in the database. So you need a maintenance window.
• To roll back, go in reverse order: first turn optimized locking off, then ADR. If you try it the other way around, you get Msg 12134 (“Optimized Locking is enabled for this database. To disable Accelerated Database Recovery, disable Optimized Locking and try again.”). Optimized locking without ADR fails with Msg 12133. SQL Server 2022 and earlier do not know the option and report a syntax error (Msg 102).
• Editions: Optimized locking is available in SQL Server 2025 Enterprise and Standard, not in Express. ADR has been available since 2019 in Enterprise, Standard and Web.
• Costs: They arise almost entirely from ADR: space for the persistent version store, log volume, delayed cleanup with long transactions. Details and measurements are in the ADR deep dive.
• Test before turning it on: Code with lock hints gains little. Code that relies on an execution order under RCSI can return different results (see t4). Monitoring and deadlock analysis must know XACT.

Six misconceptions

Method and limits

We re-measured the behavior on Oct 2, 2026: SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9 (Developer Edition, Linux container), each version alone on a test VM with 8 GB and 8 CPUs, a synthetic table with 100,000 rows, each measurement three times; LAB-10 on Oct 5, 2026 on SQL Server 2025 with 90 lock and rollback measurements, 126 conflict cases and 36 load runs; the cross-checks on page splits, OUTPUT and escalation on Nov 8, 2026 on SQL Server 2025 CU9 and CU10 (17.0.5035.5), three runs each on fresh databases. Everything beyond that comes from Microsoft Learn, the research paper on ADR and the articles named. Limits:

• Microsoft does not publish the thresholds of the LAQ heuristic.
• For the v2 features, Microsoft gives no exact build.
• How readers behave under classic READ COMMITTED with optimized locking active is not described in the documentation. Our result (LCK_M_S_XACT_READ) comes from one build.
• That page locks for pages split within the transaction remain until commit, and how SQL Server counts locks toward the escalation threshold (the extended event reported 5,000 to 7,488 with at most about 2,500 simultaneously visible locks), was measured but is not described by Microsoft.
• The behavior of OUTPUT … INTO under LAQ deviates from the documentation; two builds, one host.
• We could only trigger lock_after_qual_stmt_abort with TOP (1) … ORDER BY, and only on the first run. After that, LAQ feedback in Query Store turns LAQ off for that plan.
• Timings come from small VMs with SATA SSDs. They show orders of magnitude, not a forecast for your system.
• We did not measure Azure SQL Database.

Further reading

• Microsoft Learn: Optimized locking; Accelerated database recovery; Transaction Locking and Row Versioning Guide; Deadlocks Guide.
• Antonopoulos et al.: Constant Time Recovery in Azure SQL Database (VLDB 2019), the scientific background of ADR.
• Dimitri Furman (Microsoft): Introducing optimized locking v2 (Nov 18, 2025).
• Paul White: Data Modifications under Read Committed Snapshot Isolation.
• At PSG: Lock Escalation from a SELECT in SQL Server, SQL Server Deadlock: Read the Graph, Understand Error 1205.

Thinking about turning it on?

Or has your blocking monitoring been showing puzzling things since the upgrade? In our performance analysis, we measure on your load where LAQ takes effect, where hints prevent it and which queries behave differently. We teach the mechanics behind it in the PTS training.

More locking topics