Keilord
SQL Server

Engineering SQL Server 2025 Optimized Locking for High-Concurrency Workloads

7 Mins Read
Pinterest LinkedIn Tumblr
SQL Server database engineering

Optimized locking changes the concurrency model

SQL Server 2025 introduces optimized locking for user databases. The feature addresses two persistent costs in write-intensive systems: retaining large numbers of row or key locks until transaction completion, and acquiring update locks while searching for rows that may never be modified.

This is not simply a lower-overhead lock manager. Optimized locking changes how writers protect modified rows and, when read committed snapshot isolation is enabled, how they qualify candidate rows. That can materially improve concurrency, but it also means rollout must be treated as an application compatibility exercise rather than a routine instance setting.

Optimized locking is configured per database and is disabled by default in SQL Server 2025. It requires accelerated database recovery. Its full lock-after-qualification behavior additionally requires READ_COMMITTED_SNAPSHOT.

The two mechanisms

Transaction ID locking

Traditional SQL Server write transactions can retain an exclusive lock for every modified key or row until commit. A sufficiently large modification may consume substantial lock memory or escalate to a table lock, turning an otherwise local operation into a database-wide concurrency event.

With transaction ID locking, or TID locking, each transaction holds an exclusive XACT lock representing its transaction identifier. Rows modified by the transaction are stamped internally with that identifier. Row and page locks needed during modification are generally released quickly, while the XACT lock remains until commit or rollback. A conflicting session waits on the transaction rather than on thousands of separately retained key locks.

This reduces retained lock state and makes escalation less likely. It does not eliminate logical write conflicts. Two transactions updating the same row must still serialize, schema locks still exist, and stricter isolation levels can retain conventional locks.

Lock after qualification

Lock after qualification, or LAQ, applies when optimized locking and RCSI are both active and the statement runs under READ COMMITTED. Instead of taking update locks while scanning candidate rows, SQL Server evaluates the predicate against the latest committed row version. It requests the modification lock only after a row qualifies.

If another transaction changes that row between qualification and modification, SQL Server waits where necessary and requalifies the predicate. If the execution plan cannot safely support requalification, the engine may internally restart the statement without LAQ. Some statement forms do not use LAQ at all, including MERGE, certain OUTPUT patterns, variable assignment in DML, and plans with unsupported access shapes.

Consequently, enabling the database option does not guarantee that every update uses the complete optimized path.

Enabling the feature deliberately

Start by recording the existing database state. Enabling ADR requires an exclusive database lock, while enabling RCSI may need existing sessions terminated or drained. Perform these changes through the same controlled deployment process used for compatibility-level changes.

SELECT name,
       compatibility_level,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = N'Orders';

ALTER DATABASE Orders
SET ACCELERATED_DATABASE_RECOVERY = ON;

ALTER DATABASE Orders
SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE Orders
SET OPTIMIZED_LOCKING = ON;

ADR introduces the persistent version store, while RCSI generates row versions for statement-level consistent reads. Version-store capacity is therefore an architectural dependency. The data volume of long-running transactions, update rates, cleanup progress, storage latency, and log throughput matter more than the apparent simplicity of the ALTER DATABASE statements.

Validate that backup, restore, availability group, integrity-check, and monitoring procedures recognize the new database state. Apply the setting independently through every environment; do not assume that an infrastructure template, database refresh, or migration workflow preserves the intended options.

A controlled concurrency demonstration

Create a disposable test table in a database where optimized locking is enabled:

CREATE TABLE dbo.Inventory
(
    item_id       bigint NOT NULL PRIMARY KEY,
    available_qty int NOT NULL,
    status_code   tinyint NOT NULL
);

INSERT dbo.Inventory
VALUES (1, 20, 1), (2, 20, 1), (3, 20, 1);

In session one, leave a write transaction open:

BEGIN TRANSACTION;

UPDATE dbo.Inventory
SET available_qty = available_qty - 1
WHERE item_id = 1;

SELECT resource_type,
       request_mode,
       request_status,
       resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'RID', 'PAGE', 'XACT');

Inspecting the result should reveal the XACT resource retained by the transaction rather than treating a large collection of persistent key locks as the primary protection mechanism. From session two, update the same item and inspect its wait:

UPDATE dbo.Inventory
SET available_qty = available_qty - 1
WHERE item_id = 1;

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

Optimized locking introduces XACT-oriented waits such as LCK_M_S_XACT_MODIFY and LCK_M_S_XACT_READ. Existing alerting that recognizes only KEY, PAGE, and OBJECT wait resources may misclassify these waits or fail to connect the waiter to the modifying transaction.

Application assumptions are the principal risk

Applications sometimes rely unintentionally on blocking behavior. A common pattern reads a row under READ COMMITTED, performs work outside SQL Server, and later updates it while assuming the earlier read reserved execution order. RCSI never promised that reservation, and LAQ further reduces incidental writer blocking during predicate evaluation.

Correctness-sensitive workflows should encode their invariant explicitly. Options include an atomic conditional update, an optimistic concurrency token, SERIALIZABLE isolation for a narrowly scoped transaction, or a deliberate locking hint after testing its impact.

UPDATE dbo.Inventory
SET available_qty = available_qty - @requested,
    row_version_no = row_version_no + 1
WHERE item_id = @item_id
  AND available_qty >= @requested
  AND row_version_no = @expected_version;

IF @@ROWCOUNT = 0
    THROW 50001, 'Inventory changed or quantity unavailable', 1;

This pattern states the business rule in the write itself. It is more robust than depending on the duration of locks acquired by an earlier SELECT.

Hints including UPDLOCK, HOLDLOCK, XLOCK, and READCOMMITTEDLOCK remain honored, but they suppress parts of the optimization. Before removing them, determine whether each hint is obsolete tuning residue or an intentional concurrency contract. Blind removal may introduce correctness defects; blind retention may erase the expected scalability benefit.

Realistic failure and regression modes

  • Persistent version store growth: Long transactions and sustained updates delay version cleanup. Capacity planning must include ADR and RCSI version retention, not only table growth.

  • LAQ fallback: Unsupported plans or concurrent changes can cause statement reprocessing without LAQ. A database can show optimized locking enabled while a critical statement receives little benefit.

  • Changed deadlock topology: Some key-lock deadlocks may disappear, but transaction dependencies, schema locks, application locks, and inconsistent access order can still deadlock. XACT resources will appear in deadlock graphs.

  • Long transaction amplification: One XACT lock is cheaper than thousands of key locks, but an abandoned transaction can still block every session needing its rows while retaining versions and log records.

  • Isolation-level escape: REPEATABLE READ, SERIALIZABLE, explicit locking hints, columnstore-related limitations, and unsupported DML shapes can restore conventional locking behavior.

  • Operational blind spots: Monitoring that equates fewer locks with health may overlook rising XACT wait duration, version-store pressure, or statement restarts.

Production observability

Establish a baseline before enabling the feature. Capture blocked-session duration, deadlock rate, lock memory, lock escalation, log generation, transaction age, query latency percentiles, and version-store consumption. Compare equivalent workload windows rather than relying on a short synthetic test.

SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE N'LCK_M%XACT%'
   OR wait_type IN (N'LCK_M_X', N'LCK_M_U')
ORDER BY wait_time_ms DESC;

SELECT session_id,
       transaction_id,
       transaction_begin_time,
       transaction_state
FROM sys.dm_tran_active_transactions AS t
JOIN sys.dm_tran_session_transactions AS s
  ON s.transaction_id = t.transaction_id
ORDER BY transaction_begin_time;

Use Extended Events to capture deadlock reports, lock_after_qual_stmt_abort, locking_stats, and, on supported SQL Server deployments, locking_stats2. Query Store should remain enabled so plan regressions and LAQ feedback can be correlated with application latency. Alert on transaction age as well as transaction count; one old transaction is frequently more dangerous than thousands of short ones.

Rollout and rollback engineering

Deploy first to a production-like workload replay or a low-risk database. Validate business invariants with concurrent tests, especially inventory, scheduling, queue consumption, uniqueness enforcement, and state-machine transitions. Then canary the setting on a database whose traffic can be redirected or throttled.

Rollback is straightforward only if it has been rehearsed. Optimized locking must be disabled before ADR can be disabled:

ALTER DATABASE Orders SET OPTIMIZED_LOCKING = OFF;
-- Disable RCSI only if the previous application contract requires it.
-- ALTER DATABASE Orders SET READ_COMMITTED_SNAPSHOT OFF;
-- ALTER DATABASE Orders SET ACCELERATED_DATABASE_RECOVERY = OFF;

Do not automatically remove ADR or RCSI during an optimized-locking rollback. Either feature may already be part of the recovery or isolation design, and changing several concurrency mechanisms simultaneously makes diagnosis harder.

The strongest adoption criterion is not a reduced lock count. It is stable application correctness with lower blocking time, controlled version-store growth, understandable XACT waits, and predictable behavior during long transactions and failover. Optimized locking can materially improve high-concurrency SQL Server systems, but only when the transaction model, storage capacity, observability, and application invariants are engineered together.

Write A Comment