LCK_M_RX_S_ABORT_BLOCKERS Wait Type Explained

This wait type emerges from SQL Server's low priority wait mechanism, designed to make ALTER operations less disruptive to production workloads. When you execute ALTER TABLE or ALTER INDEX with the WAIT_AT_LOW_PRIORITY option, SQL Server attempts to acquire necessary locks at a lower priority than normal operations.

The lock manager internally creates a special lock request that includes the abort blockers flag. The RX (Range Exclusive) lock type represents a range lock that prevents other transactions from inserting keys within a specific range, while the S (Shared) component indicates the lock mode on the existing key. The ABORT_BLOCKERS suffix indicates this lock request has the authority to terminate blocking sessions after the specified timeout expires.

SQL Server's lock manager queues this request behind existing lock holders but marks it with special handling logic. During the wait period, the scheduler continues to honor existing locks while monitoring the timeout threshold. Once the timeout expires, the lock manager's abort blockers mechanism systematically terminates the blocking sessions, starting with the oldest, until the ALTER operation can proceed.

This mechanism changed slightly in SQL Server 2016 with improved lock escalation handling, and again in SQL Server 2019 with better integration into the Query Store for tracking terminated sessions. SQL Server 2022 added enhanced telemetry for abort blockers events through extended events.

-- Current blocking chains affecting abort blockers operations
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time,
    blocking.program_name,
    blocking.host_name,
    blocking_sql.text AS blocking_query,
    blocked_sql.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
WHERE blocked.wait_type LIKE 'LCK_M%ABORT_BLOCKERS%';

-- Lock details for abort blockers scenarios
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_description,
    l.request_mode,
    l.request_type,
    l.request_status,
    s.session_id,
    s.program_name,
    s.host_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests r 
    WHERE r.session_id = s.session_id 
    AND r.wait_type LIKE '%ABORT_BLOCKERS%'
);

-- Recent ALTER operations with low priority waits
SELECT 
    qh.query_hash,
    qt.query_sql_text,
    rs.count_executions,
    rs.avg_duration,
    rs.last_execution_time,
    rs.max_duration
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query q ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
CROSS APPLY (SELECT CONVERT(binary(8), HASHBYTES('MD5', qt.query_sql_text))) qh(query_hash)
WHERE qt.query_sql_text LIKE '%WAIT_AT_LOW_PRIORITY%'
AND rs.last_execution_time > DATEADD(hour, -24, GETUTCDATE());

-- Extended events for abort blockers activity (setup query)
SELECT 
    event_data.value('(@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(data[@name="database_name"]/value)[1]', 'varchar(128)') AS database_name,
    event_data.value('(data[@name="object_name"]/value)[1]', 'varchar(128)') AS object_name,
    event_data.value('(data[@name="duration"]/value)[1]', 'bigint') AS duration_ms,
    event_data.value('(data[@name="blocked_process_report"]/value)[1]', 'xml') AS blocked_process_report
FROM (
    SELECT CAST(event_data AS xml) AS event_data
    FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
) AS events
WHERE event_data.value('(@name)[1]', 'varchar(60)') = 'lock_abort_blockers'
AND event_data.value('(@timestamp)[1]', 'datetime2') > DATEADD(hour, -4, GETUTCDATE());

Kill specific blocking sessions for immediate resolution

-- Identify and terminate specific blocking sessions
-- WARNING: This will terminate active user sessions, test thoroughly
DECLARE @blocking_session INT;
DECLARE @sql NVARCHAR(100);

SELECT TOP 1 @blocking_session = blocking_session_id 
FROM sys.dm_exec_requests 
WHERE wait_type LIKE '%ABORT_BLOCKERS%' 
AND blocking_session_id > 0;

IF @blocking_session IS NOT NULL
BEGIN
    SET @sql = N'KILL ' + CAST(@blocking_session AS NVARCHAR(10));
    EXEC sp_executesql @sql;
    PRINT 'Terminated session: ' + CAST(@blocking_session AS VARCHAR(10));
END;

Modify ALTER operation to use more aggressive timeout

-- Restart the ALTER operation with shorter timeout and kill blockers
-- Replace [YourTable] and [YourIndex] with actual names
-- This forces faster resolution of blocking situations
ALTER INDEX [YourIndex] ON [YourTable] REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 1 MINUTES,
        ABORT_AFTER_WAIT = BLOCKERS
    )
);

Enable deadlock monitoring for better visibility

-- Create extended event session for comprehensive lock monitoring
-- This provides detailed tracking of abort blockers scenarios
CREATE EVENT SESSION [lock_abort_blockers_monitoring] ON SERVER 
ADD EVENT sqlserver.lock_abort_blockers(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([duration],(5000000)))  -- 5 seconds
),
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
)
ADD TARGET package0.event_file(SET filename=N'C:\temp\lock_abort_blockers_monitoring.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

ALTER EVENT SESSION [lock_abort_blockers_monitoring] ON SERVER STATE = START;

Schedule ALTER operations during maintenance windows when fewer concurrent transactions are active. Use the WAIT_AT_LOW_PRIORITY option strategically with appropriate MAX_DURATION values, typically starting with 5-10 minutes and adjusting based on historical blocking patterns.

Implement connection pooling limits and query timeouts to prevent long-running transactions from holding locks indefinitely. Configure applications to use READ_COMMITTED_SNAPSHOT isolation where appropriate to reduce reader-writer blocking scenarios.

Monitor sys.dm_exec_requests regularly for sessions with high lock counts or extended durations. Create alerts on blocking chains that persist longer than your ALTER operation timeout thresholds. Establish baseline lock wait statistics using sys.dm_os_wait_stats to identify trending increases in lock-related waits.

Configure Query Store to retain execution plans and statistics for ALTER operations, enabling analysis of abort blockers patterns over time. Set up automated monitoring for the lock_abort_blockers extended event to track when sessions are terminated and identify applications that frequently cause blocking during maintenance operations.