LCK_M_RX_X_ABORT_BLOCKERS Wait Type Explained

This wait type emerges from SQL Server's low priority lock mechanism introduced in SQL Server 2014 for online index operations and ALTER TABLE statements. When you specify WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT = BLOCKERS, the operation initially requests locks normally but switches to abort blocker mode after the specified timeout.

The lock manager creates two distinct lock requests: an exclusive (X) lock on the current key value and an exclusive range (RX) lock between the current and previous key values. The "ABORT_BLOCKERS" suffix indicates these locks will terminate blocking sessions rather than wait indefinitely. This mechanism prevents schema operations from being indefinitely blocked by long-running transactions.

The range lock component (RX) protects against phantom insertions during the schema modification, while the exclusive key lock ensures no concurrent modifications occur on existing rows. SQL Server's lock manager tracks these special locks separately from normal exclusive locks because they have different escalation and timeout behaviors.

In SQL Server 2016 and later, the lock manager improved handling of these abort blocker locks by better coordinating with the transaction manager to minimize false positives in deadlock detection. SQL Server 2019 enhanced the wait statistics collection to provide more granular timing information for these operations.

-- Check current blocking chains involving abort blocker waits
SELECT 
    s.session_id,
    s.login_name,
    s.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS current_sql,
    s.program_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS'
ORDER BY r.wait_time DESC;

-- Identify the blocking transactions and their lock types
SELECT 
    l.resource_database_id,
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_type,
    l.resource_description,
    l.request_mode,
    l.request_type,
    l.request_session_id,
    s.login_name,
    t.text AS blocking_sql
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests req 
    WHERE req.wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS'
    AND req.blocking_session_id = l.request_session_id
);

-- Check for active ALTER operations with low priority settings
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.wait_type,
    r.wait_time,
    t.text AS alter_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE 'ALTER%'
AND (r.wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS' 
     OR t.text LIKE '%WAIT_AT_LOW_PRIORITY%');

-- Monitor wait statistics for abort blocker patterns
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%ABORT_BLOCKERS%'
ORDER BY wait_time_ms DESC;

Kill blocking sessions when abort timeout is reached

-- CAUTION: This will terminate user sessions. Verify business impact first.
-- Use only when ALTER operations are critical and blocking sessions are non-essential
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'KILL ' + CAST(r.blocking_session_id AS VARCHAR(10)) + ';' + CHAR(10)
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS'
AND r.blocking_session_id > 0
AND r.wait_time > 300000; -- Only after 5 minutes of waiting

-- Review the generated KILL statements before executing
PRINT @sql;
-- EXEC sp_executesql @sql; -- Uncomment after review

Modify ALTER operation to use different low priority settings

-- Cancel current operation and restart with more aggressive settings
-- First, find and cancel the current ALTER operation
SELECT 
    'KILL ' + CAST(session_id AS VARCHAR(10)) AS cancel_command,
    session_id,
    command
FROM sys.dm_exec_requests 
WHERE command LIKE 'ALTER%' 
AND wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS';

-- Then restart with shorter timeout and SELF abort option
-- Example for index rebuild (adjust for your specific ALTER operation):
-- ALTER INDEX [your_index] ON [your_table] REBUILD 
-- WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));

Force immediate lock escalation for blocking transactions

-- Escalate table locks for sessions blocking the ALTER operation
-- This may cause blocking but can help abort blocker mechanism work faster
DECLARE @blocking_spid INT;
SELECT @blocking_spid = blocking_session_id
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_RX_X_ABORT_BLOCKERS'
AND blocking_session_id > 0;

IF @blocking_spid > 0
BEGIN
    -- Send attention signal to blocking session to encourage commit/rollback
    DECLARE @sql NVARCHAR(100) = 'DBCC INPUTBUFFER(' + CAST(@blocking_spid AS VARCHAR(10)) + ')';
    EXEC sp_executesql @sql;
    
    -- Consider setting lock timeout for the session if appropriate
    -- This is session-specific and won't affect other operations
END;

Configure ALTER operations with appropriate WAIT_AT_LOW_PRIORITY settings based on your maintenance windows. Use MAX_DURATION values that align with your typical transaction lengths, typically 1-5 minutes for OLTP systems. Set ABORT_AFTER_WAIT = BLOCKERS only when you can afford to terminate blocking sessions.

Monitor long-running transactions during maintenance windows using extended events or custom monitoring scripts. Implement transaction timeout policies for application connections to prevent indefinite locks during schema changes. Consider scheduling heavy ALTER operations during periods of minimal concurrent activity.

Establish clear policies for handling concurrent workloads during online index operations. Use Resource Governor to limit the impact of schema modification operations on production workloads. Create alerting for ALTER operations that exceed expected duration thresholds.

Design batch processing operations to commit frequently rather than holding locks for extended periods. Consider using ABORT_AFTER_WAIT = SELF for less critical operations to avoid terminating user sessions. Test low priority settings thoroughly in staging environments that mirror production concurrency patterns.