Optimized Locking in SQL Server 2025: What Changes for Blocking

A large update can hold many row locks and block nearby work. Optimized locking in SQL Server 2025 changes that pattern by retaining a transaction ID lock and qualifying rows before some locks are taken.

Understand the Two Parts

Optimized locking includes transaction ID locking and lock after qualification, called LAQ. Transaction ID locking lets a transaction retain a compact TID lock while row locks are released sooner. LAQ can evaluate predicates against committed row versions before taking a lock on qualifying rows. I explain both because enabling the feature and enabling RCSI are separate decisions. LAQ requires read committed snapshot isolation. Without RCSI, the transaction ID benefit can still exist, but the predicate-before-lock behavior does not apply.

SQL Server 2025 ships the feature for user databases, switched off by default. Azure SQL Database has its own defaults. Check the platform rather than copying a script between services.

Check Prerequisites and Current State

Accelerated database recovery must be on before optimized locking can be enabled. On a new test database without ADR, SQL Server refused the change with error 12133. RCSI is recommended for the full benefit and changes read behavior, so plan it as an application compatibility decision. Query sys.databases for the three settings. I record the current isolation assumptions and any code that depends on blocking reads. A change to RCSI can expose application logic that assumed a strict execution sequence without explicit locks.

SELECT name, is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on, is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();

Enable Optimized Locking in a Rehearsal Database

In an isolated copy, enable ADR, then RCSI if the application test permits it, then the feature itself. RCSI transitions need exclusive access and should be scheduled carefully on a real database. Do not run these commands against production from a blog post. Verify each state after the change. I test the workload before deciding whether the setting belongs in a release. The benefit depends on query shape and concurrency, not on a checkbox alone.

ALTER DATABASE CURRENT SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE CURRENT SET OPTIMIZED_LOCKING = ON;
SELECT name, is_optimized_locking_on
FROM sys.databases WHERE name = DB_NAME();

Compare Locks Before and After Optimized Locking

Run the same concurrent update workload before and after on identical starting data. Query sys.dm_tran_locks for request_session_id, resource_type, request_mode, and request_status during an open transaction. Run the query below from a second window in the same database while the test transaction is still open. Count locks by session and resource type, and record blocking chains through sys.dm_exec_requests. I also measure transaction duration and user-facing latency. A lower lock count is useful, but correctness and throughput decide the result. Do not publish a claimed numeric improvement until an actual test produces it.

SELECT request_session_id, resource_type, request_mode,
       request_status, COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND request_session_id <> @@SPID
GROUP BY request_session_id, resource_type, request_mode, request_status
ORDER BY request_session_id, lock_count DESC;

In my test, an open update of a thousand rows held one XACT lock and no KEY locks with the feature on. With it off, the same update held one exclusive KEY lock per row until the rollback. With RCSI off and the feature on, the XACT lock still replaced the KEY locks, which matches the transaction ID part working alone.

Know When LAQ Steps Aside

LAQ does not apply to every statement. Conflicting hints such as UPDLOCK or HOLDLOCK, other isolation levels, certain columnstore or multi-access plans, and other documented shapes can prevent it. I inspect actual waits and plans rather than attributing every improvement to LAQ. If application logic requires strict ordering between concurrent updates, make that rule explicit with an appropriate isolation level or locking hint and test it. RCSI and LAQ can change which row qualifies during a race.

Ask which blocking pattern you are trying to reduce. Two updates of the same row still need coordination. The feature helps most when transactions touch many rows but do not truly conflict on the same data.

Test Correctness Under Concurrency

This feature changes when some locks are taken and how long row locks remain. That can reveal application assumptions about read and write ordering. Build a two-session test for the business rule that matters, such as reserving one item or updating a status in sequence. Compare outcomes before and after with RCSI enabled. I do not accept lower blocking if a workflow now misses an intended update. Use a constraint or explicit locking when the business invariant requires one. The feature reduces unnecessary conflict; it does not invent a concurrency contract for the application.

Read committed snapshot isolation lets readers see committed versions instead of waiting on many writes. LAQ uses committed row versions to test predicates. The precise interaction deserves a test on the actual query shape. Hints such as UPDLOCK can restore blocking semantics for a narrow critical path while letting the rest of the workload benefit.

Separate the Three Switches

ADR, RCSI, and optimized locking have separate effects and prerequisites. Enable and measure them in stages on a restored copy. ADR changes recovery behavior and persistent version storage. RCSI changes read committed behavior. The third switch changes write-lock retention, with LAQ depending on RCSI. I capture a baseline for each stage rather than flipping all three and assigning the entire result to the last one. Also watch version-store space and long-running transactions under ADR and row versioning.

What if the lock count falls but transaction duration rises? Examine plans, waits, and application retries before calling it a win. I compare throughput and business outcomes during concurrent work. The feature is compelling when many rows are modified without true conflicts, but same-row writers still serialize. A lock count query is evidence of mechanism; a workload test is evidence of value.

Count locks during an active test transaction, not after it commits. The DMV is a live view, so a query run after completion can show almost nothing and falsely suggest success. I collect before and after samples under comparable transaction size and concurrency, then check blocking and user latency. The timing of the observation is part of the method.

Keep a Rollback Plan for Optimized Locking

Compare lock memory, lock counts, blocking duration, deadlocks, and query correctness under representative concurrency. Include the application's write tests, not just a synthetic update. If behavior changes unexpectedly, disable the feature on the test database and analyze the difference before production. I keep ADR and RCSI decisions separate in the rollback plan because each has its own operational effects.

The useful outcome is fewer unnecessary conflicts while business rules remain intact. A feature name does not prove that outcome. The same workload, measured before and after, does.

Related reading on this blog: Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices and Blocking Tree: Identifying Blocking Chain Using SQL Scripts.

A lower lock count is not zero contention, it is a different concurrency pattern to test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.