Locking is not a SQL Server defect. It is one of the mechanisms that keeps concurrent transactions correct. The operational problem is the amount and duration of locking: a large update can retain thousands of row or key locks, consume lock memory, escalate to a table lock, and make otherwise independent writers wait behind one another. SQL Server 2025 introduces optimized locking to change that behavior. Instead of retaining every modified row lock until commit, the engine can protect the transaction with a Transaction ID (TID) lock, release row and page locks sooner, and—when Read Committed Snapshot Isolation (RCSI) is enabled—qualify rows before acquiring update locks.

Microsoft calls the second behavior Lock After Qualification (LAQ). Those changes can materially improve concurrency, but they do not remove locking or make blocking impossible. Conflicting updates still serialize. Schema locks still exist. Locking hints and isolation levels can intentionally suppress LAQ. Some query shapes are not eligible, and RCSI plus LAQ can expose faulty application assumptions about transaction ordering.

This article uses repeatable two-session tests to show the benefits and the boundaries. Run them only in a disposable SQL Server 2025 environment.

What optimized locking actually contains

The term covers related mechanisms:

• Transaction ID locking: rows modified by a transaction are associated with its TID. A single exclusive XACT lock can protect the transaction while short-duration row and page locks are released.
• Lock After Qualification: under READ COMMITTED with RCSI, SQL Server can evaluate a DML predicate against the latest committed row version without first taking an update lock. It locks only a row that qualifies.
• Skip index locks: for supported modifications, SQL Server can sometimes avoid row and page locks altogether and use the TID infrastructure plus latching needed for the physical change.

These mechanisms solve different costs. TID locking reduces locks retained by a transaction. LAQ reduces needless blocking while a DML statement searches for rows. Skip index locks reduces locking overhead for eligible physical modifications. Optimized locking affects locks acquired by DML such as INSERT, UPDATE, DELETE, and MERGE. It does not remove database, schema, metadata, or object-level synchronization. Microsoft documents the feature and its changing limitations in Optimized Locking.

Prerequisites are part of the design

On SQL Server 2025, optimized locking is off by default and enabled per database. Accelerated Database Recovery (ADR) must be enabled first. RCSI is not required for TID locking, but it is required for LAQ and therefore for the largest concurrency benefit. Create and configure the lab:

USE master;
GO

DROP DATABASE IF EXISTS OptimizedLockingDemo;
GO

CREATE DATABASE OptimizedLockingDemo;
GO

ALTER DATABASE OptimizedLockingDemo
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

ALTER DATABASE OptimizedLockingDemo
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE OptimizedLockingDemo
SET OPTIMIZED_LOCKING = OFF;
GO

USE OptimizedLockingDemo;
GO

SELECT
    name,
    is_accelerated_database_recovery_on,
    is_read_committed_snapshot_on,
    is_optimized_locking_on
FROM sys.databases
WHERE database_id = DB_ID();
GO

Changing RCSI can terminate other sessions when WITH ROLLBACK IMMEDIATE is used. That is acceptable in this isolated lab and is not a production rollout script. In production, inventory long transactions, version-store capacity, connection behavior, and maintenance dependencies before changing database options.

Test 1: count the locks retained by a transaction

Create a table with 20,000 rows distributed across 20 work groups:

CREATE TABLE dbo.WorkQueue
(
    WorkID int NOT NULL,
    WorkGroupID int NOT NULL,
    WorkStatus char(1) NOT NULL,
    Amount decimal(12, 2) NOT NULL,
    Payload char(100) NOT NULL,
    CONSTRAINT PK_WorkQueue
        PRIMARY KEY CLUSTERED (WorkID)
);
GO

WITH
E1(N) AS
(
    SELECT 1
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS d(N)
),
E2(N) AS (SELECT 1 FROM E1 AS a CROSS JOIN E1 AS b),
E4(N) AS (SELECT 1 FROM E2 AS a CROSS JOIN E2 AS b),
Numbers AS
(
    SELECT TOP (20000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM E4 AS a CROSS JOIN E1 AS b
)
INSERT dbo.WorkQueue
    (WorkID, WorkGroupID, WorkStatus, Amount, Payload)
SELECT
    n,
    1 + CONVERT(int, (n - 1) % 20),
    'N',
    CONVERT(decimal(12, 2), 10 + (n % 10000) / 100.0),
    REPLICATE(CHAR(65 + n % 26), 100)
FROM Numbers;
GO

CREATE INDEX IX_WorkQueue_WorkGroupID
ON dbo.WorkQueue(WorkGroupID);
GO

UPDATE STATISTICS dbo.WorkQueue WITH FULLSCAN;
GO

With optimized locking still disabled, update one group but leave the transaction open:

BEGIN TRANSACTION;

UPDATE dbo.WorkQueue
SET WorkStatus = 'P'
WHERE WorkGroupID = 7;

SELECT
    resource_type,
    request_mode,
    request_status,
    COUNT_BIG(*) AS retained_locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
GROUP BY
    resource_type,
    request_mode,
    request_status
ORDER BY
    resource_type,
    request_mode;

ROLLBACK TRANSACTION;
GO

One thousand rows qualify. Depending on the plan, indexes, build, and escalation decisions, the exact lock inventory can vary. The baseline normally retains many key locks or may escalate. Record the output rather than expecting an exact count from an article.

Now, enable optimized locking and repeat the same transaction:

USE master;
GO

ALTER DATABASE OptimizedLockingDemo
SET OPTIMIZED_LOCKING = ON;
GO

USE OptimizedLockingDemo;
GO

SELECT
    name,
    is_accelerated_database_recovery_on,
    is_read_committed_snapshot_on,
    is_optimized_locking_on
FROM sys.databases
WHERE database_id = DB_ID();
GO

BEGIN TRANSACTION;

UPDATE dbo.WorkQueue
SET WorkStatus = 'P'
WHERE WorkGroupID = 7;

SELECT
    resource_type,
    request_mode,
    request_status,
    COUNT_BIG(*) AS retained_locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
GROUP BY
    resource_type,
    request_mode,
    request_status
ORDER BY
    resource_type,
    request_mode;

ROLLBACK TRANSACTION;
GO

The important new resource is XACT. Instead of retaining one exclusive key lock for every modified row, the transaction can retain an exclusive lock on its TID. Short-lived locks used while rows are physically changed may be gone before the DMV query executes. This does not mean the update is unprotected. A concurrent transaction that needs one of those uncommitted rows waits on the TID. The lock moved from thousands of keys to the transaction resource; the isolation guarantee did not disappear.

Test 2: independent writers without LAQ

The most intuitive LAQ demonstration uses a heap so the second statement must scan past a row locked by the first session:

DROP TABLE IF EXISTS dbo.LAQDemo;
GO

CREATE TABLE dbo.LAQDemo
(
    RowID int NOT NULL,
    ValueAmount int NOT NULL
);
GO

INSERT dbo.LAQDemo(RowID, ValueAmount)
VALUES (1, 10), (2, 20), (3, 30);
GO

Disable optimized locking:

USE master;
GO
ALTER DATABASE OptimizedLockingDemo
SET OPTIMIZED_LOCKING = OFF;
GO
USE OptimizedLockingDemo;
GO

Open two query windows connected to OptimizedLockingDemo. In Session 1, run:

BEGIN TRANSACTION;

UPDATE dbo.LAQDemo
SET ValueAmount += 10
WHERE RowID = 1;

-- Leave the transaction open.

In Session 2, run:

BEGIN TRANSACTION;

UPDATE dbo.LAQDemo
SET ValueAmount += 10
WHERE RowID = 2;

COMMIT TRANSACTION;

The second update can block even though it wants RowID = 2. During the scan, traditional locking attempts to take an update lock before checking each candidate. The first physical row is already locked by Session 1, so Session 2 waits before reaching the row it actually needs. Cancel Session 2 if necessary, roll back both sessions, and enable optimized locking again:

-- Session 1
ROLLBACK TRANSACTION;
GO

-- Run after Session 2 has also rolled back or disconnected.
USE master;
GO
ALTER DATABASE OptimizedLockingDemo
SET OPTIMIZED_LOCKING = ON;
GO

Repeat the two-session test. With optimized locking, RCSI, and READ COMMITTED, Session 2 can use the latest committed version to determine that RowID = 1 does not qualify. It proceeds to RowID = 2 without taking an update lock on the first row. That is Lock After Qualification: qualify first, lock only what must be changed.

Test 3: a real conflict still blocks

Leave Session 1's update of RowID = 1 uncommitted and change Session 2 to target the same row:

BEGIN TRANSACTION;

UPDATE dbo.LAQDemo
SET ValueAmount += 10
WHERE RowID = 1;

COMMIT TRANSACTION;

Session 2 must wait. Optimized locking is not optimistic concurrency that silently overwrites an uncommitted change. It waits on the transaction resource and can expose one of the SQL Server 2025 XACT lock wait types, including:

• LCK_M_S_XACT_READ
• LCK_M_S_XACT_MODIFY
• LCK_M_S_XACT

While Session 2 is blocked, inspect it from a third session:

SELECT
    session_id,
    status,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource
FROM sys.dm_exec_requests
WHERE database_id = DB_ID(N'OptimizedLockingDemo')
  AND session_id <> @@SPID;
GO

SELECT
    request_session_id,
    resource_type,
    request_mode,
    request_status,
    resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(N'OptimizedLockingDemo')
ORDER BY request_session_id, resource_type;
GO

Monitoring that recognizes only KEY, PAGE, and OBJECT resources is incomplete after optimized locking. Update blocking reports, wait dashboards, and deadlock parsers to recognize XACT resources.

The subtle behavior change under LAQ

Reduced blocking can reveal code that assumed waiting created an ordering guarantee. Reset the table:

TRUNCATE TABLE dbo.LAQDemo;
INSERT dbo.LAQDemo(RowID, ValueAmount)
VALUES (1, 1);
GO

Session 1:

BEGIN TRANSACTION;

UPDATE dbo.LAQDemo
SET ValueAmount = 2
WHERE RowID = 1;

-- Leave open.

Session 2:

BEGIN TRANSACTION;

UPDATE dbo.LAQDemo
SET ValueAmount = 3
WHERE ValueAmount = 2;

COMMIT TRANSACTION;

With LAQ, Session 2 can evaluate the latest committed version, where ValueAmount = 1. Its predicate is false, so it completes without waiting and updates zero rows. When Session 1 commits, the final value is 2. Without LAQ, Session 2 can wait. After Session 1 commits, it might see the new value, qualify the row, and set it to 3. An application that relies on that wait is relying on accidental execution ordering. RCSI never promised that separate statements would run in a business-defined sequence. If correctness requires “read this state, reserve it, then update it after the prior transaction,” encode that requirement with an appropriate isolation level, locking pattern, atomic predicate, or application concurrency token. Optimized locking is doing useful work when it exposes the missing contract.

Where the benefit is reduced

Optimized locking is a database option, but LAQ remains a per-statement decision. Microsoft documents cases in which LAQ is not used, including:

• RCSI is off or the transaction is not running under READ COMMITTED.
• Conflicting hints such as UPDLOCK, READCOMMITTEDLOCK, XLOCK, or HOLDLOCK.
• The modified table has a columnstore index.
• The DML includes variable assignment.
• An OUTPUT clause returns a result set or inserts into a table variable.
• The DML reads the target through more than one index seek or scan operator.
• MERGE statements.
• LAQ heuristics disable the optimization after costly internal reprocessing.

The exact list can change in cumulative updates, so use the documentation for the build you operate. TID locking can still reduce retained locks even when LAQ is not available; “LAQ was skipped” and “optimized locking is completely off” are not equivalent conclusions. Locking hints deserve special attention. This work-queue pattern intentionally asks SQL Server to reserve a row:

SELECT TOP (1) WorkID
FROM dbo.WorkQueue WITH (UPDLOCK, READPAST)
WHERE WorkStatus = 'N'
ORDER BY WorkID;

Removing UPDLOCK merely to maximize LAQ could introduce duplicate workers. Optimized locking honors the hint because correctness wins over concurrency. Review whether each hint is necessary, but do not remove a reservation protocol without redesigning and concurrency-testing the whole operation.

Measure more than blocked-session count

A safe before-and-after test records the following data:

• Lock count and lock-memory consumption.
• Lock escalation rate.
• Throughput and latency percentiles for concurrent writers.
• XACT, key, page, and object lock waits.
• Deadlock frequency and deadlock resource types.
• Version-store generation and cleanup after enabling RCSI and ADR.
• Transaction-log growth and long-running transaction duration.
• Statements for which LAQ was skipped or internally retried.

SQL Server 2025 adds the locking_stats and, on SQL Server and Azure SQL Managed Instance, locking_stats2 Extended Events for aggregate optimized-locking diagnostics. The lock_after_qual_stmt_abort event identifies a statement that was internally restarted when predicate requalification could not continue with LAQ. Do not expect optimized locking to repair every blocking graph. A 20-minute transaction that updates a hot account row still owns that logical conflict for 20 minutes. Shorten transactions, index search predicates, remove unnecessary user interaction from transactions, and retry deadlock victims where appropriate.

A production rollout sequence

I would use this order:

1. Patch SQL Server 2025 to an approved current cumulative update.
2. Baseline blocking, deadlocks, lock memory, throughput, and version-store usage.
3. Inventory isolation levels and locking hints.
4. Enable and test ADR, including persistent version-store capacity and long transactions.
5. Enable and test RCSI, with special attention to code that assumes readers block writers.
6. Enable optimized locking in a production-sized test environment.
7. Run concurrent writer tests, not only single-session benchmarks.
8. Update monitoring for XACT resources and new wait types.
9. Roll out per database with an explicit rollback plan.

To disable optimized locking, first end or resolve active test transactions and then run:

USE master;
GO

ALTER DATABASE OptimizedLockingDemo
SET OPTIMIZED_LOCKING = OFF;
GO

Optimized locking must be disabled before ADR can be disabled. RCSI and ADR have consequences beyond this feature, so do not treat them as temporary switches that can be flipped casually during an incident.

Closing

Traditional locking protects a modifying transaction by retaining locks on the keys or rows it changed. SQL Server 2025 can instead retain a transaction-level TID lock and release many lower-level locks sooner. With RCSI, LAQ can also prevent a writer from blocking on rows that do not satisfy its predicate. The result can be fewer retained locks, fewer escalations, lower lock memory, and better writer concurrency. The feature does not abolish conflicts. Two sessions changing the same row must still coordinate. Hints and stricter isolation levels still request stronger behavior. Unsupported query shapes can bypass LAQ. Schema locks remain, and applications that relied on blocking as an ordering mechanism need a real concurrency contract. The useful question is not “Is optimized locking enabled?” It is “Which component helped this statement, what resource protects the transaction now, and is the resulting concurrency behavior correct for the application?” The lock-count test, the two-session LAQ test, and the XACT diagnostics provide enough evidence to answer that question before the feature reaches production.