LCK_M_RX_U_ABORT_BLOCKERS Wait Type Explained

This wait type exists solely within SQL Server's low priority lock infrastructure introduced in SQL Server 2014. When ALTER TABLE or ALTER INDEX operations specify WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT = BLOCKERS, the lock manager implements a specialized abortion mechanism that terminates blocking sessions rather than the ALTER operation itself.

The RX_U component indicates a range exclusive lock with update intentions, specifically targeting key ranges in indexes. The ABORT_BLOCKERS suffix signals that SQL Server's lock manager has activated the blocker termination protocol. During this phase, the lock manager identifies sessions holding incompatible locks, marks them for termination, and waits for their cleanup before proceeding.

The lock manager maintains separate wait queues for low priority operations. When ABORT_BLOCKERS activates, it enumerates sessions in sys.dm_tran_locks holding conflicting locks, issues kill commands for eligible sessions, then waits for lock cleanup. This wait type captures the interval between issuing kill commands and acquiring the necessary locks.

SQL Server 2016 enhanced this mechanism with improved blocker identification algorithms. SQL Server 2019 added better integration with Query Store for tracking aborted sessions. SQL Server 2022 introduced optimizations that reduce the duration of this wait type by accelerating lock cleanup after session termination.

-- Identify current low priority operations and their blocking chains
SELECT 
    s.session_id,
    s.blocking_session_id,
    r.wait_type,
    r.wait_time_ms,
    r.wait_resource,
    t.text AS current_sql,
    s.program_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_RX_U_ABORT_BLOCKERS'
ORDER BY r.wait_time_ms DESC;

-- Show sessions marked for termination by ABORT_BLOCKERS
SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,
    s.session_id,
    s.is_user_process,
    s.status,
    r.blocking_session_id
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE tl.resource_type IN ('KEY', 'RID', 'PAGE', 'OBJECT')
    AND EXISTS (
        SELECT 1 FROM sys.dm_exec_requests dr
        WHERE dr.wait_type = 'LCK_M_RX_U_ABORT_BLOCKERS'
        AND dr.wait_resource LIKE '%' + CAST(tl.resource_database_id AS VARCHAR) + '%'
    );

-- Monitor ALTER operations with low priority settings
SELECT 
    der.session_id,
    der.command,
    der.percent_complete,
    der.estimated_completion_time,
    der.wait_type,
    der.wait_time_ms,
    SUBSTRING(qt.text, der.statement_start_offset/2 + 1,
        (CASE WHEN der.statement_end_offset = -1 
         THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
         ELSE der.statement_end_offset END - der.statement_start_offset)/2) AS statement_text
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) qt
WHERE der.command LIKE 'ALTER%'
    AND (der.wait_type LIKE 'LCK_M_%ABORT_BLOCKERS%' OR qt.text LIKE '%WAIT_AT_LOW_PRIORITY%');

-- Check lock escalation and partitioning impact
SELECT 
    p.object_id,
    OBJECT_NAME(p.object_id) AS table_name,
    p.partition_number,
    p.rows,
    i.name AS index_name,
    i.type_desc,
    ps.lock_escalation_desc
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.tables ps ON p.object_id = ps.object_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests r
    WHERE r.wait_type = 'LCK_M_RX_U_ABORT_BLOCKERS'
);

Cancel the blocking low priority operation

-- Identify and terminate the ALTER operation causing the wait
-- WARNING: This will roll back the ALTER operation
DECLARE @SessionID INT;
SELECT @SessionID = session_id 
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_RX_U_ABORT_BLOCKERS';

IF @SessionID IS NOT NULL
BEGIN
    PRINT 'Killing session ' + CAST(@SessionID AS VARCHAR(10));
    EXEC('KILL ' + @SessionID);
END

This terminates the ALTER operation but preserves existing user sessions. The ALTER will need to be restarted with different timing or priority settings.

Force completion by killing blocking sessions manually

-- Manually terminate sessions blocking the low priority ALTER
-- CAUTION: This affects user sessions and should be used carefully
DECLARE @BlockingSession INT;
DECLARE blocking_cursor CURSOR FOR
SELECT DISTINCT blocking_session_id
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_RX_U_ABORT_BLOCKERS'
    AND blocking_session_id > 0;

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @BlockingSession;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Terminating blocking session: ' + CAST(@BlockingSession AS VARCHAR(10));
    EXEC('KILL ' + @BlockingSession);
    FETCH NEXT FROM blocking_cursor INTO @BlockingSession;
END

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Use this only when ABORT_BLOCKERS mechanism fails to terminate sessions automatically. May impact application users.

Restart ALTER with different priority settings

-- Template for restarting with modified wait settings
-- Adjust MAX_DURATION and action based on maintenance window
/*
ALTER INDEX [your_index_name] ON [your_table_name] 
REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,  -- Reduced from original
        ABORT_AFTER_WAIT = SELF    -- Changed from BLOCKERS
    )
);
*/

Change ABORT_AFTER_WAIT to SELF to terminate the ALTER instead of blocking sessions, or reduce MAX_DURATION to minimize blocking impact.

Configure ALTER operations during maintenance windows when concurrent activity is minimal. Use shorter MAX_DURATION values (1-5 minutes) with ABORT_AFTER_WAIT = SELF to prevent prolonged blocking scenarios. Monitor sys.dm_exec_requests for operations with WAIT_AT_LOW_PRIORITY to identify timing conflicts.

Implement table partitioning for large tables requiring frequent schema changes. Partitioned tables allow ALTER operations to target specific partitions, reducing lock scope and contention. Enable lock escalation only when necessary, as partition-level locks reduce the effectiveness of low priority operations.

Schedule resource-intensive queries during off-peak hours to avoid conflicts with maintenance operations. Use Application Intent=ReadOnly connections for reporting workloads to reduce lock conflicts. Monitor query duration patterns to identify optimal maintenance windows through sys.dm_exec_query_stats analysis.

Configure alerts on wait times exceeding 300 seconds for this wait type. Excessive wait times indicate either poor timing of maintenance operations or insufficient ABORT_BLOCKERS timeout configuration. Review application connection pooling settings to ensure prompt session cleanup when termination occurs.