SQL Server 2025 Added Lock Modes to Extended Events

2026-10-26 · by Hannah Vernon · in Internals

A lock event in Extended Events is only useful if I can decode the map values. A numeric mode value of 5 is not a diagnosis. The map tells me that 5 means X. Then I can connect the event back to the lock evidence I already know how to read.^[1]

A woman DBA marks one of two glowing new padlock symbols at the end of a large lock map spread across a table.

This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.

This post follows the SQL Server 2025 LCK_M_SCH_A and LCK_M_SCH_C post. That post surfaced the new SCH_A and SCH_C names. Here I queried the Extended Events (XE) maps and the optimized locking events for lock after qualification (LAQ). XE is SQL Server’s event tracing system. LAQ is the optimized-locking feature that evaluates a row before taking the lock used to modify it.

The inventory files told me that the lock_mode map exists. They did not store the individual map values, so I queried sys.dm_xe_map_values directly on the 2019 and 2025 local instances. I did not query the 2022 instance. For 2022, I used only the inventory rows that were already captured in hv2022.tsv, so the 2022 comparison in this post covers XE event names and descriptions, not the map values.

The map change

I used this read-only map query on the 2019 and 2025 instances:

SELECT
      [name]
    , [map_key]
    , [map_value]
FROM
    [sys].[dm_xe_map_values]
WHERE
    [name] IN
    (
          N'lock_mode'
        , N'lock_resource_type'
        , N'lock_owner_type'
    )
    OR
    (
        [name] = N'wait_types'
        AND
        (
            [map_value] LIKE N'LCK[_]M[_]%'
            OR [map_value] IN
            (
                  N'LOCK_UPDATE'
                , N'LOCK_SAFEMODE'
                , N'POPULATE_LOCK_ORDINALS'
                , N'ORDLOCK_POPULATE_SYNC'
                , N'PLOCK_DAEMON'
            )
        )
    )
ORDER BY
      [name]
    , [map_key];

SELECT

[name]

, [map_key]

, [map_value]

FROM

[sys].[dm_xe_map_values]

WHERE

[name] IN

(

N'lock_mode'

, N'lock_resource_type'

, N'lock_owner_type'

)

(

[name] = N'wait_types'

AND

(

[map_value] LIKE N'LCK[_]M[_]%'

OR [map_value] IN

(

N'LOCK_UPDATE'

, N'LOCK_SAFEMODE'

, N'POPULATE_LOCK_ORDINALS'

, N'ORDLOCK_POPULATE_SYNC'

, N'PLOCK_DAEMON'

)

ORDER BY

[name]

, [map_key];

The 2019 lock_mode map ended with RX_U at key 20 and LAST_MODE at key 21. The 2025 map no longer shows LAST_MODE and ended this way:

name       map_key  map_value
---------  -------  ---------
lock_mode  20       RX_U
lock_mode  21       RX_X
lock_mode  22       SCH_A
lock_mode  23       SCH_C

name map_key map_value

--------- ------- ---------

lock_mode 20 RX_U

lock_mode 21 RX_X

lock_mode 22 SCH_A

lock_mode 23 SCH_C

The 2025 lock_owner_type map also added key 8, Node, and moved LastLockInfoOwner from key 8 to key 9. The lock_resource_type map did not change in this comparison. It still includes XACT at key 16.

The wait_types map lines up with the wait-type post. The 2025 map adds LCK_M_SCH_A, LCK_M_SCH_C, their low-priority variants, and their abort-blockers variants. It also includes LOCK_SAFEMODE, LOCK_UPDATE, POPULATE_LOCK_ORDINALS, PLOCK_DAEMON, and ORDLOCK_POPULATE_SYNC in the lock-text slice I queried.

The map shows that XE has names for these modes. It is not a definition of what SCH_A or SCH_C means. Microsoft documents Sch-S and Sch-M as schema stability and schema modification request modes in sys.dm_tran_locks, and the transaction locking guide discusses schema stability and schema modification locks.^[2]^[3] I did not find SCH_A or SCH_C in the Microsoft documentation repositories when I searched the exact names, first whole-word and then case-insensitive.^[4]

The definitions turned up elsewhere. A 2025 paper by the Azure SQL Database team defines SCH-A as Schema Access, a shared lock that conflicts only with Sch-M, and SCH-C as Schema Create, an exclusive lock that conflicts with every mode except SCH-A. Snapshot queries and query compilation take SCH-A, and schema changes take SCH-C, as part of a feature called MD-MVCC.^[5] The LCK_M_SCH_A and LCK_M_SCH_C post has the full compatibility matrix.

The 2025 XE event changes

The existing lock events are still there in 2025: lock_acquired, lock_released, lock_escalation, lock_timeout, and locks_lock_waits. Their event descriptions matched between the 2022 inventory and the 2025 inventory.

The new lock-related rows were these four:

Event	2025 description
`sqlserver.lock_after_qual_stmt_abort`	Fired when a statement is internally aborted and retried due to lock after qualification.
`sqlserver.laq_feedback_stats`	Lock after qualification query-level feedback statistics.
`sqlserver.lock_report_info`	Reports lock type info of a lock taken.
`sqlserver.locking_stats2`	Additional database-level locking statistics

Microsoft’s optimized locking article documents three related diagnostic additions. It says the lock_after_qual_stmt_abort event fires when a statement is internally reprocessed because of a conflict with another transaction. It says locking_stats fires for every database every several minutes and reports aggregate locking statistics. It says locking_stats2 fires for SQL Server and Azure SQL Managed Instance, and provides skip index locks and LAQ heuristics statistics.^[6]

I used this query to inspect the XE metadata:

SELECT
      [event_name]  = xo.[name]
    , [column_name] = oc.[name]
    , oc.[column_type]
    , oc.[type_name]
    , oc.[description]
FROM
    [sys].[dm_xe_objects] AS xo
JOIN
    [sys].[dm_xe_packages] AS xp
    ON xp.[guid] = xo.[package_guid]
JOIN
    [sys].[dm_xe_object_columns] AS oc
    ON oc.[object_package_guid] = xo.[package_guid]
    AND oc.[object_name] = xo.[name]
WHERE
    xp.[name] = N'sqlserver'
    AND xo.[object_type] = N'event'
    AND xo.[name] IN
    (
          N'lock_acquired'
        , N'lock_released'
        , N'lock_after_qual_stmt_abort'
        , N'laq_feedback_stats'
        , N'locking_stats'
        , N'locking_stats2'
        , N'lock_report_info'
    )
    AND oc.[column_type] <> N'readonly'
ORDER BY
      xo.[name]
    , oc.[column_id];

SELECT

[event_name] = xo.[name]

, [column_name] = oc.[name]

, oc.[column_type]

, oc.[type_name]

, oc.[description]

FROM

[sys].[dm_xe_objects] AS xo

JOIN

[sys].[dm_xe_packages] AS xp

ON xp.[guid] = xo.[package_guid]

JOIN

[sys].[dm_xe_object_columns] AS oc

ON oc.[object_package_guid] = xo.[package_guid]

AND oc.[object_name] = xo.[name]

WHERE

xp.[name] = N'sqlserver'

AND xo.[object_type] = N'event'

AND xo.[name] IN

(

N'lock_acquired'

, N'lock_released'

, N'lock_after_qual_stmt_abort'

, N'laq_feedback_stats'

, N'locking_stats'

, N'locking_stats2'

, N'lock_report_info'

)

AND oc.[column_type] <> N'readonly'

ORDER BY

xo.[name]

, oc.[column_id];

lock_acquired, lock_released, lock_escalation, and lock_timeout expose resource_type as lock_resource_type and mode as lock_mode. lock_after_qual_stmt_abort exposes database_id, logical_reads_before_retry, and query_plan_hash_binary. laq_feedback_stats exposes query-level totals for executions, conflicts, eligible logical reads, wasted logical reads, and executions with LAQ. locking_stats2 adds counters for optimized locking, skipped index locks, heap and index updates, and LAQ query-plan heuristics.

A scratch optimized-locking test

Microsoft says optimized locking is available in SQL Server 2025, is disabled by default, and can be enabled per database. It also says accelerated database recovery (ADR) must be enabled before optimized locking can be enabled. For LAQ, it says read committed snapshot isolation (RCSI) must be enabled.^[7]

I used a scratch database named undoc_spinoff_locks. This script created the database if needed, enabled ADR, RCSI, and optimized locking, and created the test table:

USE [master];
IF DB_ID(N'undoc_spinoff_locks') IS NULL
BEGIN
    EXEC (N'CREATE DATABASE [undoc_spinoff_locks]');
END;
GO
ALTER DATABASE [undoc_spinoff_locks] SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE [undoc_spinoff_locks] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [undoc_spinoff_locks] SET OPTIMIZED_LOCKING = ON;
GO
USE [undoc_spinoff_locks];
IF OBJECT_ID(N'dbo.lock_probe', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.lock_probe
    (
          id int NOT NULL CONSTRAINT PK_lock_probe PRIMARY KEY
        , group_id int NOT NULL
        , payload char(200) NOT NULL CONSTRAINT DF_lock_probe_payload DEFAULT ('x')
        , touch_count int NOT NULL CONSTRAINT DF_lock_probe_touch_count DEFAULT (0)
    );
END;

IF NOT EXISTS (SELECT 1 FROM dbo.lock_probe)
BEGIN
    INSERT INTO dbo.lock_probe (id, group_id)
    SELECT TOP (200)
          [id]       = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
        , [group_id] = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10)
    FROM sys.all_columns;
END;

USE [master];

IF DB_ID(N'undoc_spinoff_locks') IS NULL

BEGIN

EXEC (N'CREATE DATABASE [undoc_spinoff_locks]');

END;

ALTER DATABASE [undoc_spinoff_locks] SET ACCELERATED_DATABASE_RECOVERY = ON;

ALTER DATABASE [undoc_spinoff_locks] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [undoc_spinoff_locks] SET OPTIMIZED_LOCKING = ON;

USE [undoc_spinoff_locks];

IF OBJECT_ID(N'dbo.lock_probe', N'U') IS NULL

BEGIN

CREATE TABLE dbo.lock_probe

(

id int NOT NULL CONSTRAINT PK_lock_probe PRIMARY KEY

, group_id int NOT NULL

, payload char(200) NOT NULL CONSTRAINT DF_lock_probe_payload DEFAULT ('x')

, touch_count int NOT NULL CONSTRAINT DF_lock_probe_touch_count DEFAULT (0)

);

END;

IF NOT EXISTS (SELECT 1 FROM dbo.lock_probe)

BEGIN

INSERT INTO dbo.lock_probe (id, group_id)

SELECT TOP (200)

[id] = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))

, [group_id] = CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10)

FROM sys.all_columns;

END;

The database accepted all three options. sys.databases returned 1 for is_accelerated_database_recovery_on, is_read_committed_snapshot_on, and is_optimized_locking_on.

I created a server-level event session named undoc_spinoff_locks, scoped to that database ID. I started it, ran two sessions that updated rows in dbo.lock_probe, queried the ring buffer, and then stopped the session. I did not drop the event session.

DECLARE @db nvarchar(10) = CONVERT(nvarchar(10), DB_ID(N'undoc_spinoff_locks'));
DECLARE @sql nvarchar(max) = N'
CREATE EVENT SESSION [undoc_spinoff_locks] ON SERVER
ADD EVENT sqlserver.lock_acquired
(
    SET collect_database_name = (1), collect_resource_description = (0)
    WHERE ([database_id] = ' + @db + N')
),
ADD EVENT sqlserver.lock_released
(
    SET collect_database_name = (1), collect_resource_description = (0)
    WHERE ([database_id] = ' + @db + N')
),
ADD EVENT sqlserver.lock_after_qual_stmt_abort
(
    WHERE ([database_id] = ' + @db + N')
),
ADD EVENT sqlserver.locking_stats
(
    WHERE ([database_id] = ' + @db + N')
),
ADD EVENT sqlserver.locking_stats2
(
    WHERE ([database_id] = ' + @db + N')
)
ADD TARGET package0.ring_buffer
(
    SET max_events_limit = (1000), max_memory = (1024)
);';

EXEC sys.sp_executesql @sql;
ALTER EVENT SESSION [undoc_spinoff_locks] ON SERVER STATE = START;

DECLARE @db nvarchar(10) = CONVERT(nvarchar(10), DB_ID(N'undoc_spinoff_locks'));

DECLARE @sql nvarchar(max) = N'

CREATE EVENT SESSION [undoc_spinoff_locks] ON SERVER

ADD EVENT sqlserver.lock_acquired

(

SET collect_database_name = (1), collect_resource_description = (0)