LCK_M_IX Wait Type Explained

Intent Exclusive (IX) locks are hierarchy locks that SQL Server's lock manager uses to signal intent to modify data at lower levels in the lock hierarchy. When a session needs to acquire a page-level or row-level exclusive lock, it must first obtain an IX lock on the table. The lock manager enforces compatibility rules where IX locks conflict with Shared (S), Shared with Intent Exclusive (SIX), and Exclusive (X) locks.

The lock manager in SQL Server 2016+ uses a hash table structure to track lock resources, with each lock request queued in FIFO order when conflicts occur. LCK_M_IX waits specifically happen when the lock manager cannot grant an IX lock because incompatible locks exist on the same resource. The session enters a wait state, consuming no CPU while the scheduler parks the task until the conflicting lock releases.

SQL Server 2019 introduced lock memory optimization that reduced lock manager overhead, but the fundamental IX lock behavior remains unchanged across versions. The duration of LCK_M_IX waits directly correlates with how long the blocking session holds its conflicting lock, making transaction duration the primary factor in wait time.

Unlike other lock waits, IX locks are purely hierarchical signals and don't prevent concurrent IX locks on the same resource. This means multiple sessions can hold IX locks simultaneously, but they all wait when encountering S, SIX, or X locks.

-- Identify current blocking chains causing LCK_M_IX waits
SELECT 
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text,
    s.login_name,
    s.program_name
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_IX'
ORDER BY r.wait_time DESC;

-- Show lock details for LCK_M_IX waits including resource information
SELECT 
    tl.request_session_id,
    tl.resource_type,
    tl.resource_database_id,
    DB_NAME(tl.resource_database_id) AS database_name,
    tl.resource_associated_entity_id,
    tl.resource_lock_partition,
    tl.request_mode,
    tl.request_type,
    tl.request_status,
    OBJECT_NAME(p.object_id, tl.resource_database_id) AS object_name
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.request_mode = 'IX' 
   OR EXISTS (
       SELECT 1 FROM sys.dm_exec_requests r 
       WHERE r.session_id = tl.request_session_id 
       AND r.wait_type = 'LCK_M_IX'
   )
ORDER BY tl.request_session_id;

-- Historical LCK_M_IX wait statistics with percentage impact
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct_total_waits,
    wait_time_ms / waiting_tasks_count AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_IX'
    AND waiting_tasks_count > 0;

-- Find long-running transactions that may be causing IX lock waits
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    t.transaction_type,
    t.transaction_state,
    r.command,
    r.wait_type,
    SUBSTRING(qt.text, r.statement_start_offset/2+1, 
        CASE WHEN r.statement_end_offset = -1 
             THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
             ELSE r.statement_end_offset 
        END - r.statement_start_offset)/2 AS current_statement
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE t.transaction_begin_time < DATEADD(minute, -2, GETDATE())
ORDER BY t.transaction_begin_time;

Kill blocking sessions causing excessive LCK_M_IX waits

-- WARNING: This kills sessions. Verify blocking session details first.
-- Replace @blocking_spid with actual session ID from diagnostic queries
DECLARE @blocking_spid INT = 0; -- SET THIS VALUE

IF @blocking_spid > 50 -- Safety check to avoid killing system processes
BEGIN
    PRINT 'Killing session: ' + CAST(@blocking_spid AS VARCHAR(10));
    KILL @blocking_spid;
END
ELSE
BEGIN
    PRINT 'Invalid or unsafe session ID specified';
END

Test in development first. Killing sessions can cause application errors and transaction rollbacks.

Enable Read Committed Snapshot Isolation to reduce IX lock contention

-- Enables RCSI to reduce reader-writer blocking
-- WARNING: Requires exclusive database access during ALTER DATABASE
USE master;
GO

-- Check current isolation settings first
SELECT 
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases 
WHERE name = 'YourDatabaseName';

-- Enable RCSI (replace YourDatabaseName)
ALTER DATABASE [YourDatabaseName] 
SET READ_COMMITTED_SNAPSHOT ON;

Requires brief exclusive database access. Plan maintenance window. Increases tempdb usage.

Add missing indexes to reduce lock duration

-- Creates indexes based on missing index recommendations
-- Reduces lock duration by making queries more efficient
SET NOCOUNT ON;

-- Review missing index suggestions first
SELECT 
    ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS improvement_measure,
    'CREATE INDEX IX_' + 
    OBJECT_NAME(d.object_id,d.database_id) + '_' +
    REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns,''),', ','_'),'[',''),']','') +
    CASE WHEN d.inequality_columns IS NOT NULL 
         THEN '_' + REPLACE(REPLACE(REPLACE(d.inequality_columns,', ','_'),'[',''),']','') 
         ELSE '' END +
    ' ON ' + d.statement +
    ' (' + ISNULL (d.equality_columns,'') +
    CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL 
         THEN ',' ELSE '' END +
    ISNULL (d.inequality_columns, '') +
    ')' + 
    ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) > 10000
ORDER BY improvement_measure DESC;

-- Execute top recommendations after review

Review each suggestion carefully. Create indexes during maintenance windows. Monitor index usage post-deployment.

Configure lock timeout at application level using SET LOCK_TIMEOUT to prevent indefinite waits. Set values between 5000-30000 milliseconds based on application requirements. Applications must handle timeout errors gracefully.

Implement proper transaction boundaries by keeping transactions short and avoiding user input within transaction blocks. Long-running transactions are the primary cause of IX lock contention. Use explicit BEGIN TRAN/COMMIT pairs and avoid implicit transactions.

Enable Read Committed Snapshot Isolation (RCSI) for databases with mixed read-write workloads. RCSI eliminates reader-writer blocking scenarios that commonly cause IX lock waits. Monitor tempdb usage after enabling RCSI as it stores row versions.

Optimize query performance through proper indexing strategies. Faster queries hold locks for shorter durations, reducing lock contention. Focus on covering indexes for frequently executed queries and eliminate table scans that require extended lock durations.

Implement connection pooling and proper connection management. Applications that hold database connections idle with open transactions cause unnecessary lock retention. Configure appropriate connection timeout values and connection pool sizing.

Monitor lock waits using Extended Events or third-party tools. Create alerts when LCK_M_IX wait times exceed baseline thresholds. Trending lock wait data helps identify patterns and prevent issues before they impact users.