LCK_M_IS Wait Type Explained

Intent Shared locks establish a hierarchy in SQL Server's lock manager to signal that a session intends to read data at a lower granular level. When a session needs to read rows within a table, it first acquires an IS lock at the table level, then shared locks on the actual pages or rows. The lock manager processes these requests through the lock hash table, where each lockable resource has a lock block containing the lock mode and owner information.

LCK_M_IS waits occur when the lock manager cannot grant the Intent Shared lock because incompatible locks exist. Exclusive table locks, schema modification locks, or bulk update locks will block IS lock acquisition. The requesting session enters a wait state, consuming a worker thread from the scheduler until the blocking lock releases.

Lock escalation is controlled at the table level using ALTER TABLE SET (LOCK_ESCALATION = TABLE | AUTO | DISABLE), a feature available since SQL Server 2008. This affects how quickly page locks escalate to table locks. SQL Server 2016 added Query Store, providing better visibility into which queries cause blocking. SQL Server 2019's intelligent query processing can reduce blocking through adaptive query plans, though this doesn't directly affect the locking mechanism itself.

The lock manager maintains lock compatibility matrices internally. IS locks are compatible with IS, S, U, IX, and SIX locks. IS locks are only incompatible with Exclusive (X) and Schema Modification (Sch-M) locks. Lock memory comes from the buffer pool, and excessive lock escalation can pressure memory, causing additional performance issues beyond the initial blocking.

-- Identify current blocking chains with lock details
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    s.program_name,
    s.login_name,
    t.text as current_sql,
    r.wait_time_ms
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'
ORDER BY r.wait_time_ms DESC;

-- Examine lock hierarchy and escalation patterns
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,
    t.text
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_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) t
WHERE l.request_mode IN ('IS', 'X', 'IX', 'S')
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Find transactions holding conflicting locks
SELECT 
    t.transaction_id,
    s.session_id,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) as duration_seconds,
    s.program_name,
    s.login_name,
    l.resource_type,
    l.request_mode,
    sql.text
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
INNER JOIN sys.dm_tran_locks l ON s.session_id = l.request_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) sql
WHERE l.request_mode = 'X' 
AND t.transaction_type = 1
ORDER BY t.transaction_begin_time;

-- Analyze historical blocking patterns from Extended Events
SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') as event_time,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@spid)[1]', 'int') as blocked_spid,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@spid)[1]', 'int') as blocking_spid,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]', 'varchar(130)') as sql_handle
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
CROSS APPLY (SELECT CAST(event_data as xml)) as event_xml(event_data)
WHERE event_data.value('(event/@name)[1]', 'varchar(50)') = 'blocked_process_report'
AND event_data.exist('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process[process/@waitresource[contains(., "OBJECT")]])') = 1
ORDER BY event_time DESC;

Kill blocking sessions causing extended waits

-- CAUTION: This terminates active sessions. Verify the SPID is safe to kill
-- Get blocking SPID first from diagnostic queries above
DECLARE @BlockingSPID INT = 0; -- Replace with actual blocking SPID

IF @BlockingSPID > 0
BEGIN
    -- Verify session details before killing
    SELECT session_id, login_name, program_name, status, open_transaction_count
    FROM sys.dm_exec_sessions 
    WHERE session_id = @BlockingSPID;
    
    -- Uncomment to execute kill
    -- KILL @BlockingSPID;
    PRINT 'Session ' + CAST(@BlockingSPID AS VARCHAR(10)) + ' would be terminated';
END

Force lock escalation to reduce granular locking

-- Enable table-level lock escalation for specific table experiencing excessive page locks
-- Test in development first, this changes locking behavior permanently
USE YourDatabase; -- Replace with actual database name
GO

-- Check current escalation setting
SELECT name, lock_escalation_desc 
FROM sys.tables 
WHERE name = 'YourTableName'; -- Replace with actual table name

-- Enable table lock escalation (from default AUTO)
-- This forces escalation to table level sooner, reducing lock memory usage
ALTER TABLE YourTableName SET (LOCK_ESCALATION = TABLE);

-- Alternative: Disable escalation entirely (use cautiously)
-- ALTER TABLE YourTableName SET (LOCK_ESCALATION = DISABLE);

Reduce transaction scope in application code

-- Example pattern to minimize lock duration in batch operations
-- Replace the table and column names with your actual schema
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
DECLARE @BatchSize INT = 1000;
DECLARE @RowsProcessed INT = 0;

WHILE @RowsProcessed < @BatchSize
BEGIN
    UPDATE TOP(100) YourTable 
    SET ProcessedFlag = 1,
        LastModified = GETDATE()
    WHERE ProcessedFlag = 0;
    
    SET @RowsProcessed = @RowsProcessed + @@ROWCOUNT;
    
    -- Commit every 100 rows to release locks frequently
    IF @RowsProcessed % 100 = 0
    BEGIN
        COMMIT;
        BEGIN TRANSACTION;
        WAITFOR DELAY '00:00:00.050'; -- 50ms pause between batches
    END;
END;

COMMIT;

Configure READ_COMMITTED_SNAPSHOT isolation at the database level to reduce shared lock contention. This eliminates most LCK_M_IS waits for read operations by using row versioning instead of shared locks. Monitor tempdb growth after enabling snapshot isolation.

Implement connection pooling with shorter connection timeouts to prevent abandoned transactions from holding locks indefinitely. Set LOCK_TIMEOUT to reasonable values (30-60 seconds) for application connections to fail fast rather than queue indefinitely.

Design batch operations to process data in small chunks with explicit transaction boundaries. Use WITH (NOLOCK) hint sparingly and only for reporting queries where dirty reads are acceptable. Prefer SNAPSHOT or READ_COMMITTED_SNAPSHOT over NOLOCK.

Establish monitoring on sys.dm_os_wait_stats for LCK_M_IS accumulation trends. Create Extended Events sessions to capture blocking events automatically. Set up alerts when wait times exceed defined thresholds (typically 30+ seconds).

Consider partitioning large tables to reduce lock contention scope. Implement proper indexing strategies to minimize lock escalation from row to page to table levels. Review application transaction patterns quarterly to identify opportunities for reducing lock duration.