SQL Server 2025 Optimized Locking

A practical, learner-oriented walkthrough of Optimized Locking — the new concurrency model introduced in SQL Server 2025 and rolling out across Azure SQL Database. The goal is to give DP-300 candidates a clear mental model, not a marketing tour: what changes under the hood, what stays the same, how to verify it, and what to watch out for in production.

🎯 Exam Focus

DP-300 Relevance. Optimized Locking is not a guaranteed exam objective — DP-300 currently focuses on classic locking, isolation levels, RCSI, and blocking diagnostics. Treat this page as advanced context for the "Monitor and optimize operational resources" domain: the kind of topic that helps you reason about real-world blocking issues and forward-looking exam refresh content. Know the classic model first; use Optimized Locking as the "where SQL Server is going" lens.

Why this matters

Classic SQL Server locking acquires many short-lived locks (row, key, page, sometimes object) for the duration of a transaction. Long transactions therefore hold lots of locks, which hurts:

Concurrency — other writers wait on LCK_M_X / LCK_M_U.
Lock memory — millions of row locks can trigger lock escalation to a table lock.
Blocking chains — head blockers stall whole workloads.

Optimized Locking is Microsoft's answer: keep correctness, drop the lock count to (essentially) one transaction-level lock per writer, and let row versioning carry the rest.

Oracle DBA Translation

If your background is Oracle, the easiest way to slot Optimized Locking into your mental model is this:

Oracle concept	SQL Server equivalent (classic)	SQL Server 2025 with Optimized Locking
Row-level lock stored inside the block (lock byte → ITL slot)	Row/key locks held in the lock manager (`sys.dm_tran_locks`) for the whole txn	TID stamp on the row + a single transaction-level lock; row/page locks released after the modify
`enq: TX – row lock contention`	`LCK_M_X` on KEY / RID	`LCK_M_S_XACT_*` against the writing transaction
Consistent read from UNDO for predicate evaluation	Reader either blocks or reads versioned row from tempdb version store (RCSI/SI)	Same RCSI versioning, plus Lock-After-Qualification: the predicate is evaluated on the versioned image before taking any lock
`enq: TX – allocate ITL entry` (per-block ITL exhaustion)	No direct analog — lock memory is global	Still no analog; TID stamping is per-row, not per-block
ASSM / ITL tuning	`ALLOW_PAGE_LOCKS`, `ROWLOCK` hints, lock escalation thresholds	Mostly unnecessary — fewer locks held means fewer escalations

In short: SQL Server finally moves closer to Oracle's "writers don't block readers, and writers hold one logical lock" model — but it gets there through versioning + a transaction-ID stamp instead of UNDO segments and ITL slots.

The locking models, in order

To understand what Optimized Locking adds, you have to know what came before. Each row below builds on the previous one.

#	Feature	What it does	What it does not do
1	Traditional locking (default since SQL Server 6.x)	Pessimistic. Writers take row/key/page X locks held until commit. Readers take S locks (block on writers under READ COMMITTED).	Does not version rows. Readers and writers block each other.
2	Row versioning / RCSI (2005)	`READ_COMMITTED_SNAPSHOT ON`. Readers get a consistent, statement-level snapshot from the tempdb version store. Writers still take and hold X locks.	Does not eliminate writer-writer blocking. Adds tempdb pressure.
3	Accelerated Database Recovery — ADR (2019)	Moves the version store into the user database (PVS — Persistent Version Store) and adds a logical revert mechanism. Enables instant rollback and constant-time crash recovery regardless of long transactions.	Does not change which locks are taken. ADR is a recovery + version-store change.
4	Transaction ID (TID) locking (2025 building block)	Each modified row carries the transaction ID of its last modifier (TID stamp), persisted with the row. The lock manager can then take a single lock on the transaction, not on every row.	Not a feature you toggle on its own — it is the substrate Optimized Locking uses.
5	Lock After Qualification — LAQ (2025 building block)	The query evaluates the predicate against the versioned row image first; only matching rows acquire a lock.	Requires RCSI to have versions available.
6	Optimized Locking (2025)	Combines TID stamping + LAQ + Skip Index Locks (SIL). Row/page locks are taken briefly during the modify and released before commit. Only the TID/transaction lock survives until commit.	Does not make SQL Server lock-free, does not remove write-write conflicts, and does not eliminate the need for indexing or query tuning.

Prerequisites for Optimized Locking to engage on a database:

ADR is ON — ALTER DATABASE ... SET ACCELERATED_DATABASE_RECOVERY = ON;
RCSI is ON — ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;
OPTIMIZED_LOCKING database option is ON (default in eligible Azure SQL DB and SQL Server 2025).

If any prerequisite is missing, the database silently falls back to classic behavior for that table.

Before / after mental model

A simple UPDATE against 10 000 rows in a single transaction.

BEGIN TRAN;
UPDATE dbo.Orders
   SET Status = 'Shipped'
 WHERE OrderDate < '2026-01-01';   -- 10,000 rows match
-- ... long-running business logic ...
COMMIT;

Classic locking (RCSI on, ADR optional)

Held until COMMIT:
  ├─ 10,000 × KEY (X)            ← one per row
  ├─    ~80 × PAGE (IX)          ← intent locks on touched pages
  ├─     1 × OBJECT (IX)         ← intent on the table
  └─     1 × DATABASE (S)
Memory: ~10,000 lock structures × ~96 B  ≈  ~1 MB of lock memory
Risk:   Lock escalation to TABLE (X) at ~5,000 lock threshold

Optimized Locking (ADR + RCSI + OPTIMIZED_LOCKING ON)

During the modify (microseconds per row):
  └─ row/page X taken, row's TID stamp updated, lock released

Held until COMMIT:
  ├─ 1 × XACT lock on TID = <transaction_id>
  ├─ 1 × OBJECT (IX)
  └─ 1 × DATABASE (S)
Memory: a handful of lock structures, regardless of row count
Risk:   No lock escalation triggered by row count
Conflict: Other writers touching the same row see the TID stamp,
          take LCK_M_S_XACT_* on that transaction, and wait for it.

The logical correctness is identical — write-write conflicts still serialize. What changes is the lock footprint and the chance of escalation.

What DBAs Should Check

1. Verify the prerequisites are on

SELECT
    name                                                                         AS database_name,
    snapshot_isolation_state_desc                                                AS snapshot_isolation,
    is_read_committed_snapshot_on                                                AS rcsi_on,
    is_accelerated_database_recovery_on                                          AS adr_on,
    -- New in SQL Server 2025 / current Azure SQL DB
    DATABASEPROPERTYEX(name, 'IsOptimizedLockingOn')                             AS optimized_locking_on
FROM sys.databases
WHERE database_id > 4;   -- skip system DBs

What you want to see for Optimized Locking to be active: rcsi_on = 1, adr_on = 1, optimized_locking_on = 1.

2. Inspect live locks

-- Classic: see every row/page/key lock held
SELECT
    request_session_id        AS spid,
    resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
ORDER BY spid, resource_type;

With Optimized Locking active, the same workload typically shows far fewer rows — mostly OBJECT, DATABASE, and a per-transaction XACT resource type. Long lists of KEY / RID locks are the classic signature.

3. Find current blockers

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset
          END - r.statement_start_offset)/2) + 1) AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

Wait types to recognize:

Wait type	Meaning
`LCK_M_X`, `LCK_M_U`, `LCK_M_S`	Classic row/page/key lock waits
`LCK_M_S_XACT_*`	Waiting on a transaction (TID) — the Optimized Locking signature
`WRITELOG`	Log flush, unrelated to locking but often appears in the same workload

4. Spot lock escalation (still relevant)

SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    p.partition_number,
    es.index_lock_promotion_attempt_count,
    es.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS es
JOIN sys.partitions AS p
  ON p.partition_id = es.partition_id
WHERE es.index_lock_promotion_count > 0
ORDER BY es.index_lock_promotion_count DESC;

Escalations should drop sharply on tables that benefit from Optimized Locking. If they don't, check that all three prerequisites are actually on for the database.

Limitations and Cautions

⚠️ Watch Out

Optimized Locking changes the lock footprint, not the transactional contract. Treat it as an optimization, not a workaround.

Not lock-free. Modifications still take row/page X locks briefly. Schema operations, escalated locks, and TABLOCK hints still behave as before.
Write-write conflicts are unchanged. Two transactions updating the same row still serialize — one waits on LCK_M_S_XACT_* for the other to commit or roll back. No magic.
Does not replace indexing or query tuning. A bad plan that touches 50 M rows still touches 50 M rows; you just hold fewer locks while doing it. Index design, statistics, and Query Store recommendations still drive most performance work.
Test workloads that depend on strict ordering or pessimistic semantics. Code that relies on holding row locks for application-level coordination (UPDLOCK, HOLDLOCK patterns, queue tables, "select-for-update" loops) can behave subtly differently and should be regression-tested.
Snapshot-isolation tradeoffs still apply. RCSI and ADR move version data into the user DB (PVS); monitor PVS size with sys.dm_tran_persistent_version_store_stats.
Not all workloads see a win. Short OLTP transactions touching a handful of rows already held a handful of locks; the absolute reduction is small. The big wins are on wide updates, large deletes, and long-running writers.
Feature availability. Requires SQL Server 2025 (or current-generation Azure SQL Database). Older versions, Managed Instance, and SQL Server in IaaS on prior versions do not have it.

Key Takeaways

Optimized Locking = TID stamping + Lock After Qualification + Skip Index Locks, layered on top of ADR and RCSI.
It shrinks the lock footprint to roughly one lock per transaction, but it does not change transactional semantics — write-write conflicts still serialize.
Three switches must all be on: ACCELERATED_DATABASE_RECOVERY, READ_COMMITTED_SNAPSHOT, OPTIMIZED_LOCKING.
New wait-type signature to recognize on monitoring dashboards: LCK_M_S_XACT_* (waiting on a transaction, not a row).
For DP-300, master classic locking, isolation levels, RCSI, and sys.dm_tran_locks first; treat Optimized Locking as forward-looking context that explains where Microsoft is taking the engine.

Sources

Microsoft Learn — Optimized locking (learn.microsoft.com/sql/relational-databases/performance/optimized-locking?view=sql-server-ver17)
Microsoft Learn — Accelerated database recovery
Microsoft Learn — Snapshot isolation in SQL Server (RCSI / SI)
Microsoft Learn — Lock escalation and sys.dm_tran_locks
Companion deep-dive: Oracle → SQL Server Engine Internals

Why this matters​

Oracle DBA Translation​

The locking models, in order​

Before / after mental model​

Classic locking (RCSI on, ADR optional)​

Optimized Locking (ADR + RCSI + OPTIMIZED_LOCKING ON)​

What DBAs Should Check​

1. Verify the prerequisites are on​

2. Inspect live locks​

3. Find current blockers​

4. Spot lock escalation (still relevant)​

Limitations and Cautions​

Key Takeaways​

Sources​