LCK_M_SIX_ABORT_BLOCKERS Wait Type Explained

This wait type represents SQL Server's lock manager processing the ABORT_AFTER_WAIT option introduced in SQL Server 2014. When ALTER TABLE or ALTER INDEX operations specify ABORT_AFTER_WAIT = BLOCKERS, the operation waits for the specified timeout period to acquire the required SIX lock. After timeout expiration, SQL Server's lock manager initiates termination of blocking transactions.

The SIX (Shared with Intent Exclusive) lock allows concurrent readers but prevents other transactions from acquiring exclusive or schema modification locks. The lock manager maintains a blocking chain analysis, identifying transactions that prevent the DDL operation from proceeding. Once the timeout expires, the scheduler kills these blocking sessions.

This mechanism differs from KILL_BLOCKERS which immediately terminates blocking transactions, and NONE which fails immediately when encountering blocks. The abort blockers feature integrates with SQL Server's deadlock detector and uses the same transaction termination pathways.

In SQL Server 2016 and later, this wait type became more prominent as online index operations expanded. The wait statistics accumulate across all sessions experiencing this condition, making it visible in sys.dm_os_wait_stats when DDL operations use this feature.

-- Current sessions waiting for SIX locks with abort blockers
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    s.program_name,
    r.command,
    t.text as current_sql
FROM sys.dm_exec_requests r
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_SIX_ABORT_BLOCKERS';

-- Blocking chains affecting DDL operations
SELECT 
    blocked.session_id as blocked_spid,
    blocked.wait_type,
    blocking.session_id as blocking_spid,
    blocking_sql.text as blocking_query,
    blocked_sql.text as blocked_query
FROM sys.dm_exec_requests blocked
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 = 'LCK_M_SIX_ABORT_BLOCKERS';

-- Lock compatibility matrix for current SIX waits
SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,
    tl.request_session_id
FROM sys.dm_tran_locks tl
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests r 
    WHERE r.session_id = tl.request_session_id 
    AND r.wait_type = 'LCK_M_SIX_ABORT_BLOCKERS'
);

-- Wait statistics trends for abort blockers
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_SIX_ABORT_BLOCKERS';

Identify and kill specific blocking sessions

-- Kill specific sessions blocking DDL operations
-- WARNING: Test impact before running in production
DECLARE @blocking_spid INT;

SELECT @blocking_spid = blocking_session_id
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_SIX_ABORT_BLOCKERS'
AND blocking_session_id IS NOT NULL;

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

Modify DDL timeout settings

-- Increase timeout for DDL operations experiencing frequent aborts
-- Adjust WAIT_AT_LOW_PRIORITY timeout based on workload patterns
ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
WITH (
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 10 MINUTES, 
            ABORT_AFTER_WAIT = BLOCKERS
        )
    )
);

Switch to different wait strategy

-- Change to KILL_BLOCKERS for immediate termination
-- Use when blocking transactions are acceptable to terminate immediately
ALTER TABLE dbo.YourTable 
ADD NewColumn INT NULL
WITH (
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,
        ABORT_AFTER_WAIT = KILL_BLOCKERS
    )
);

Configure DDL operations during maintenance windows to minimize blocking scenarios. Use READ_COMMITTED_SNAPSHOT isolation level to reduce lock contention for read operations. Monitor transaction log growth and implement proper transaction boundary management to prevent long-running transactions.

Implement automated monitoring for blocking chains exceeding predefined thresholds. Use Extended Events to capture blocking events and establish baseline patterns for DDL operation timing. Consider partitioning strategies for large tables to minimize lock scope during maintenance operations.

Establish consistent DDL deployment patterns using ABORT_AFTER_WAIT with appropriate timeout values based on application transaction patterns. Document maximum acceptable blocking durations for different operation types. Use online operations with low priority settings during business hours and switch to offline operations during maintenance windows when possible.

Configure Query Store to identify problematic query patterns that create extended blocking scenarios. Implement connection pooling limits to prevent excessive concurrent sessions from amplifying blocking conditions.