LCK_M_UIX_ABORT_BLOCKERS Wait Type Explained

This wait type emerged in SQL Server 2014 with the introduction of low priority wait options for ALTER operations. When you specify ABORT_AFTER_WAIT=BLOCKERS, the lock manager implements a two-phase acquisition strategy. First, it attempts normal lock acquisition with the specified timeout period. If blocked, the lock manager transitions to abort mode, where it identifies and terminates sessions holding incompatible locks.

The lock manager maintains a list of blocking sessions during the wait period. When the timeout expires, it kills these sessions in lock hierarchy order, starting with the least privileged locks. The UIX (Update with Intent Exclusive) lock provides escalation path control, allowing shared locks on lower-level resources while preventing conflicting modifications at the table level.

In SQL Server 2016 and later, the lock manager improved the blocking session identification algorithm to reduce unnecessary terminations. SQL Server 2019 enhanced the timeout precision and added better integration with Query Store for tracking killed sessions. SQL Server 2022 introduced more granular control over which session types can be terminated.

The wait statistic increments only when the abort mechanism activates. If the lock acquires normally within the timeout period, you see standard LCK_M_UIX waits instead. This makes LCK_M_UIX_ABORT_BLOCKERS a confirmation that sessions were actually killed to complete the operation.

-- Check currently waiting tasks with abort blocker waits
SELECT 
    session_id,
    wait_type,
    wait_time_ms,
    blocking_session_id,
    resource_description,
    sql_text.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) sql_text
WHERE wait_type = 'LCK_M_UIX_ABORT_BLOCKERS';

-- Identify recent ALTER operations using abort blockers
SELECT 
    start_time,
    command,
    percent_complete,
    estimated_completion_time,
    session_id,
    blocking_session_id,
    wait_type,
    sql_text.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) sql_text
WHERE command IN ('ALTER TABLE', 'ALTER INDEX')
    AND wait_type LIKE 'LCK_M%ABORT_BLOCKERS';

-- Check lock waits and blocking chains
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocking_sql.text AS blocking_query,
    blocked_sql.text AS blocked_query,
    locks.resource_type,
    locks.resource_description,
    locks.request_mode,
    locks.request_status
FROM sys.dm_tran_locks locks
JOIN sys.dm_exec_sessions blocking ON locks.request_session_id = blocking.session_id
JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_sql
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
WHERE locks.request_status = 'WAIT'
    AND blocked.wait_type = 'LCK_M_UIX_ABORT_BLOCKERS';

-- Monitor historical abort blocker activity from wait stats
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 = 'LCK_M_UIX_ABORT_BLOCKERS';

-- Check Extended Events for killed sessions
SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/data[@name="session_id"]/value)[1]', 'int') AS killed_session_id,
    event_data.value('(event/data[@name="abort_reason"]/text)[1]', 'varchar(50)') AS abort_reason,
    event_data.value('(event/data[@name="object_name"]/value)[1]', 'sysname') AS object_name
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions xs
    JOIN sys.dm_xe_session_targets xt ON xs.address = xt.event_session_address
    WHERE xs.name = 'system_health'
) AS xml_data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="lock_abort_blocker"]') AS n(event_data);

Reduce ALTER operation timeout to minimize blocking window:

-- Modify existing ALTER operations to use shorter timeout
-- TEST IN DEVELOPMENT FIRST - this will kill blocking sessions more aggressively
ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));

This reduces the window where sessions can be killed by shortening the wait period before abort activation. Test with your typical workload patterns because shorter timeouts increase the likelihood of killing legitimate long-running queries.

Switch to SELF termination to avoid killing other sessions:

-- Use SELF option instead of BLOCKERS to fail gracefully
-- This prevents killing other sessions but may require retry logic
ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF));

SELF termination cancels the ALTER operation instead of killing blockers. Implement retry logic in your deployment scripts since operations may need multiple attempts during busy periods.

Implement pre-ALTER blocking session cleanup:

-- Kill known problematic sessions before starting ALTER
-- DANGEROUS - verify session IDs before executing
DECLARE @BlockingSession INT = 85; -- Replace with actual session ID
IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE session_id = @BlockingSession AND is_user_process = 1)
BEGIN
    KILL @BlockingSession;
    WAITFOR DELAY '00:00:05'; -- Allow cleanup time
END;

Use this only when you can identify specific sessions that should be terminated. Never kill system processes or sessions running critical business operations.

Schedule ALTERs during maintenance windows:

-- Create job to run ALTERs during low activity periods
-- Reduces probability of blocking conflicts
EXEC msdb.dbo.sp_add_job
    @job_name = 'Index_Maintenance_With_Abort_Blockers',
    @enabled = 1;

EXEC msdb.dbo.sp_add_jobstep
    @job_name = 'Index_Maintenance_With_Abort_Blockers',
    @step_name = 'Rebuild_Critical_Indexes',
    @command = 'ALTER INDEX ALL ON dbo.YourTable REBUILD WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 30 MINUTES, ABORT_AFTER_WAIT = BLOCKERS))';

EXEC msdb.dbo.sp_add_schedule
    @schedule_name = 'Weekly_Maintenance_Window',
    @freq_type = 8, -- Weekly
    @freq_interval = 1, -- Sunday
    @active_start_time = 020000; -- 2 AM

Configure maintenance operations during established maintenance windows when application activity is minimal. This reduces the probability of encountering blocking sessions that require termination.

Implement application connection timeouts shorter than your ALTER operation timeout values. If your ALTER uses MAX_DURATION = 10 MINUTES, ensure application queries timeout within 8-9 minutes to prevent them from being killed by the abort mechanism.

Use Query Store to identify long-running queries that frequently block DDL operations. Optimize these queries or schedule them to avoid maintenance windows. Queries holding locks for extended periods are prime candidates for termination by abort blocker logic.

Monitor sys.dm_tran_locks regularly to understand your application's locking patterns. Applications with poor transaction management create more opportunities for abort blocker conflicts. Focus on reducing transaction scope and duration rather than relying on abort mechanisms.

Consider using resume-able index operations in SQL Server 2017+ for large tables. These operations can pause and resume, reducing the need for abort blocker logic since they can work around blocking sessions naturally.

Set up Extended Events to capture lock_abort_blocker events. This provides forensic data about which sessions are being killed and why, helping you identify patterns in application behavior that lead to blocking conflicts.