Optimized locking (SQL Server 2025)

In one line: with optimized locking, a row changed by a transaction is protected by the transaction’s ID instead of a row lock held to the end. Under READ_COMMITTED_SNAPSHOT, an UPDATE also checks its WHERE clause before locking anything. You get fewer locks, no escalation, and far less blocking, but writers on the same row still wait.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/optimized-locking on GitHub.

What you’ll see

  • Fewer locks. An open transaction that updated 20,000 rows holds two locks (XACT X and OBJECT IX), not 20,000 key locks escalated to a table lock.
  • A new wait type, LCK_M_S_XACT_MODIFY, on a resource that reads xactlock xdesIdLow=... instead of keylock.
  • Blocking that’s gone.
    • An update on an unrelated row had waited 2.1 s behind an escalated table lock. It now waited 0 ms.
    • An update that only read a locked row had waited 2.1 s. It now waited 0 ms.

Why it happens

  • TID locking. Each row is locked only while it’s being changed, then released. One lock on the transaction ID protects all its changes until commit, so a transaction’s lock count no longer grows with its row count, and there’s nothing to escalate. This part works without RCSI.
  • Lock after qualification. Without it, an update takes a U lock on every row it reads to test the WHERE clause, even rows it won’t change. With optimized locking and RCSI, it tests against the last committed version without locking and locks only the rows that qualify. Without RCSI the same update still waited 2.1 s, now on the other transaction’s ID.

What to do

  • Turn it on together with READ_COMMITTED_SNAPSHOT. Without RCSI you get the smaller lock footprint but keep most of the blocking.
  • Turn on Accelerated Database Recovery first. Optimized locking requires it, so plan for ADR’s version store costs.
  • Teach your blocking scripts and alerts the new names: XACT resources and LCK_M_S_XACT_MODIFY. blocking_session_id still names the blocker.

What doesn’t help

  • Expecting two writers on the same row to stop waiting. The second one still waits for the first to commit: 2,154 ms in the demo. Only the wait’s name changed.
  • Optimized locking without RCSI, if your blocking comes from updates scanning rows they don’t change.

Check your server

-- The three settings optimized locking depends on, per database (NULL before SQL Server 2025).
SELECT d.name,
       OptimizedLocking = DATABASEPROPERTYEX(d.name, 'IsOptimizedLockingOn'),
       d.is_read_committed_snapshot_on,
       d.is_accelerated_database_recovery_on
FROM sys.databases AS d
WHERE d.database_id > 4;

-- Who is waiting on another transaction's ID right now.
SELECT wt.session_id, wt.blocking_session_id, wt.wait_type, wt.wait_duration_ms, wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
WHERE wt.wait_type LIKE 'LCK[_]M[_]%XACT%';

See it happen

The optimized-locking demo runs the same updates with optimized locking off, on with RCSI, and on without. It counts the locks each holds and uses a background session to see what still blocks. On 2022 and earlier it reports SKIPPED.

Related: lock escalation · blocking chains · RCSI and snapshot isolation · Accelerated Database Recovery

Measured on SQL Server 2025 RTM-CU8 (17.0.4075.5), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights