LCK_M_UIX Wait Type Explained

The LCK_M_UIX wait emerges from SQL Server's hierarchical locking architecture when the lock manager cannot grant an Update-Intent-Exclusive lock. UIX is a rare combined lock mode that occurs during lock conversion scenarios, such as when a transaction already holds an update (U) lock on a page and needs to acquire an intent exclusive (IX) lock on the same resource due to a concurrent modification pattern. It is not acquired as part of standard UPDATE lock acquisition.

SQL Server's lock manager maintains lock compatibility matrices where UIX locks conflict with other UIX locks, exclusive locks, and shared-with-intent-exclusive locks on the same resource. The lock escalation engine attempts to acquire UIX locks at the page level first, then escalates to table level if the lock count exceeds the threshold (typically 5,000 locks per object).

The internal scheduler places the requesting task in a wait queue managed by the lock manager's waitable object infrastructure. SQL Server 2016 introduced lock memory optimizations that reduced UIX lock overhead, while SQL Server 2019 enhanced the lock monitor's efficiency in detecting and resolving UIX lock chains. In SQL Server 2022, the lock manager better handles UIX locks in columnstore scenarios, and SQL Server 2025 provides improved telemetry for UIX lock waits in Azure SQL Database scenarios.

Transaction isolation levels directly impact UIX lock duration. READ_COMMITTED retains UIX locks until the statement completes, while REPEATABLE_READ and SERIALIZABLE hold them until transaction commit. The lock manager's deadlock detection algorithm specifically monitors UIX lock chains because they frequently participate in deadlock cycles involving multiple UPDATE operations.

-- Identify current UIX lock waits and blocking chains
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    s.program_name,
    s.login_name,
    t.text,
    p.query_plan
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
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.wait_type = 'LCK_M_UIX';

-- Analyze UIX lock wait patterns over time
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_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_UIX';

-- Find objects with high UIX lock contention
SELECT 
    l.resource_database_id,
    DB_NAME(l.resource_database_id) as database_name,
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) as object_name,
    l.resource_type,
    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
WHERE l.request_mode = 'UIX'
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Identify blocking UIX lock holders with transaction details
SELECT 
    tl.request_session_id as blocked_session,
    tl.resource_type,
    tl.resource_database_id,
    DB_NAME(tl.resource_database_id) as database_name,
    tl.resource_associated_entity_id,
    OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) as table_name,
    tl.request_mode as requested_mode,
    tl.request_status,
    bl.request_session_id as blocking_session,
    bl.request_mode as blocking_mode,
    at.transaction_begin_time,
    at.transaction_type,
    st.text as blocking_sql
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_tran_locks bl ON tl.resource_associated_entity_id = bl.resource_associated_entity_id
    AND tl.resource_database_id = bl.resource_database_id
    AND tl.request_session_id != bl.request_session_id
INNER JOIN sys.dm_tran_session_transactions tst ON bl.request_session_id = tst.session_id
INNER JOIN sys.dm_tran_active_transactions at ON tst.transaction_id = at.transaction_id
INNER JOIN sys.dm_exec_connections ec ON bl.request_session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE tl.request_mode = 'UIX' 
    AND tl.request_status = 'WAIT'
    AND bl.request_status = 'GRANT';

Kill blocking sessions causing UIX lock waits

-- WARNING: Test in development first. This terminates active connections.
-- Identify and kill sessions blocking UIX locks for more than 30 seconds
DECLARE @BlockingSessionId INT;
DECLARE blocking_cursor CURSOR FOR
SELECT DISTINCT r.blocking_session_id
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_UIX' 
    AND r.wait_time > 30000
    AND r.blocking_session_id > 0;

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @BlockingSessionId;

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

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Enable lock escalation to reduce UIX lock granularity

-- Reduce page-level UIX locks by enabling table lock escalation
-- WARNING: May increase deadlock risk. Monitor after implementation.
DECLARE @TableName NVARCHAR(128);
DECLARE table_cursor CURSOR FOR
SELECT DISTINCT OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id)
FROM sys.dm_tran_locks l
WHERE l.request_mode = 'UIX' 
    AND l.resource_type = 'PAGE'
    AND OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) IS NOT NULL;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('ALTER TABLE ' + @TableName + ' SET (LOCK_ESCALATION = TABLE)');
    PRINT 'Enabled table lock escalation for: ' + @TableName;
    FETCH NEXT FROM table_cursor INTO @TableName;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

Implement READ_COMMITTED_SNAPSHOT to reduce UIX lock duration

-- Enable RCSI to reduce UIX lock holding time
-- WARNING: Requires exclusive database access during execution
-- Increases tempdb usage for versioning
DECLARE @DatabaseName NVARCHAR(128) = DB_NAME();
DECLARE @SQL NVARCHAR(MAX);

-- Check if already enabled
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName AND is_read_committed_snapshot_on = 0)
BEGIN
    SET @SQL = 'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + ' SET READ_COMMITTED_SNAPSHOT ON';
    EXEC sp_executesql @SQL;
    PRINT 'READ_COMMITTED_SNAPSHOT enabled for database: ' + @DatabaseName;
END
ELSE
BEGIN
    PRINT 'READ_COMMITTED_SNAPSHOT already enabled for database: ' + @DatabaseName;
END;

Configure appropriate isolation levels for your workload. READ_COMMITTED_SNAPSHOT eliminates most UIX lock waits by using row versioning instead of blocking readers. For applications requiring blocking behavior, ensure UPDATE transactions commit quickly and avoid long-running transactions that hold UIX locks.

Implement proper indexing strategies to minimize lock escalation. Create covering indexes for frequently updated columns to reduce the number of pages that require UIX locks. Partition large tables to isolate UIX lock contention to specific partition boundaries.

Monitor lock escalation thresholds using sys.dm_db_index_operational_stats. The lock_escalation_count column reveals when tables frequently escalate from page-level UIX locks to table-level locks. Consider disabling lock escalation on tables with high concurrent UPDATE activity using ALTER TABLE SET (LOCK_ESCALATION = DISABLE).

Establish lock timeout values appropriate for your application's tolerance using SET LOCK_TIMEOUT. Values between 5000-10000 milliseconds prevent indefinite UIX lock waits while allowing reasonable time for lock acquisition. Implement retry logic in application code to handle lock timeout exceptions gracefully.

Use Query Store to identify queries causing excessive UIX lock waits. The sys.query_store_wait_stats DMV captures wait statistics per query, enabling you to target optimization efforts on the highest-impact statements. Focus on optimizing UPDATE statements with complex WHERE clauses or those affecting large row counts.