LCK_M_IS_ABORT_BLOCKERS Wait Type Explained

This wait type manifests exclusively in SQL Server 2014 and later during online DDL operations that specify the WAIT_AT_LOW_PRIORITY clause. When an ALTER INDEX REBUILD or ALTER TABLE operation encounters blocking locks, the lock manager enters a low priority wait state where it attempts to acquire Intent Shared locks while monitoring for the configured timeout period.

The internal mechanism works through the lock manager's priority queue system. Normal lock requests enter the standard queue, but WAIT_AT_LOW_PRIORITY requests enter a separate low priority queue. During the wait period, the scheduler yields CPU cycles to allow existing transactions to complete. When the timeout expires, the ABORT_AFTER_WAIT = BLOCKERS option triggers the lock manager to identify and terminate all blocking transactions through the session kill mechanism.

The lock escalation behavior differs between versions. SQL Server 2014 and 2016 show more aggressive blocking identification, while 2017 and later versions implement refined algorithms that better distinguish between true blockers and incidental shared lock holders. SQL Server 2019 introduced improved deadlock detection during the abort process, and SQL Server 2022 enhanced the priority queue mechanism to reduce unnecessary waits.

The Intent Shared lock component indicates the operation needs to examine table structure or index metadata before proceeding with the actual DDL modification. This preliminary lock acquisition becomes critical in preventing corruption during concurrent modifications.

-- Identify active low priority wait operations
SELECT 
    s.session_id,
    s.program_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text
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_IS_ABORT_BLOCKERS';

-- Find blocking transactions during low priority operations
SELECT 
    blocking.session_id as blocking_spid,
    blocked.session_id as blocked_spid,
    blocking.login_name as blocking_user,
    blocked_text.text as blocked_query,
    blocking_text.text as blocking_query,
    blocking.status,
    blocking.last_request_start_time
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_text
WHERE blocked.wait_type = 'LCK_M_IS_ABORT_BLOCKERS';

-- Check current lock details for abort blocker operations
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    s.session_id,
    s.program_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.wait_type = 'LCK_M_IS_ABORT_BLOCKERS'
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Monitor WAIT_AT_LOW_PRIORITY timeout settings in active DDL
SELECT 
    s.session_id,
    r.command,
    r.percent_complete,
    t.text,
    r.estimated_completion_time,
    DATEDIFF(second, r.start_time, GETDATE()) as elapsed_seconds
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.command IN ('ALTER INDEX', 'ALTER TABLE')
  AND t.text LIKE '%WAIT_AT_LOW_PRIORITY%';

Terminate the blocking transactions manually This script identifies and kills sessions blocking the low priority operation, achieving the same result as ABORT_AFTER_WAIT = BLOCKERS.

-- WARNING: This kills active sessions. Verify in dev first.
DECLARE @blocking_spid INT;
DECLARE blocker_cursor CURSOR FOR
SELECT DISTINCT blocking.session_id
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
WHERE blocked.wait_type = 'LCK_M_IS_ABORT_BLOCKERS'
  AND blocking.session_id <> @@SPID;

OPEN blocker_cursor;
FETCH NEXT FROM blocker_cursor INTO @blocking_spid;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session: ' + CAST(@blocking_spid AS VARCHAR(10));
    EXEC('KILL ' + @blocking_spid);
    FETCH NEXT FROM blocker_cursor INTO @blocking_spid;
END;

CLOSE blocker_cursor;
DEALLOCATE blocker_cursor;

Cancel the low priority operation and retry with different settings This approach stops the current operation and restarts with modified timeout values.

-- Step 1: Cancel the current operation
-- Find the session ID from diagnostic queries above, then:
-- KILL [session_id]

-- Step 2: Retry with longer timeout or different abort option
-- Example for index rebuild:
ALTER INDEX [your_index_name] ON [your_table]
REBUILD WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 10 MINUTES,  -- Increased from default
        ABORT_AFTER_WAIT = SELF     -- Changed from BLOCKERS
    )
);

Force blocking transaction completion This script commits or rolls back transactions that have been idle too long.

-- WARNING: Only use during maintenance windows
DECLARE @spid INT, @cmd NVARCHAR(100);
DECLARE idle_cursor CURSOR FOR
SELECT s.session_id
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.blocking_session_id IN (
    SELECT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_IS_ABORT_BLOCKERS'
)
AND DATEDIFF(minute, s.last_request_end_time, GETDATE()) > 30;

OPEN idle_cursor;
FETCH NEXT FROM idle_cursor INTO @spid;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'KILL ' + CAST(@spid AS VARCHAR(10));
    EXEC sp_executesql @cmd;
    FETCH NEXT FROM idle_cursor INTO @spid;
END;
CLOSE idle_cursor;
DEALLOCATE idle_cursor;

Schedule online index maintenance during low activity periods to minimize blocking conflicts. Configure WAIT_AT_LOW_PRIORITY with MAX_DURATION values that align with your maintenance windows, typically 5-15 minutes for most operations.

Implement transaction log monitoring to identify long-running transactions before starting DDL operations. Use sys.dm_tran_active_transactions to check for transactions older than your planned maintenance duration.

Consider using ABORT_AFTER_WAIT = SELF instead of BLOCKERS when the DDL operation can be safely restarted. This prevents disrupting user transactions at the cost of potentially longer maintenance windows.

Establish connection pooling limits and idle connection timeouts in applications to prevent abandoned transactions from blocking maintenance operations. Set idle timeouts to 10-15 minutes maximum.

Monitor index fragmentation levels and implement incremental maintenance strategies using REORGANIZE operations for moderately fragmented indexes (5-30% fragmentation) to avoid the need for online rebuilds during peak hours.

Configure Resource Governor workload groups to limit the impact of maintenance operations on production workloads, ensuring DDL operations cannot monopolize system resources while waiting for locks.