LCK_M_RIN_S Wait Type Explained

The lock manager generates LCK_M_RIN_S waits when a session requests a shared range insert null lock and cannot immediately acquire it due to conflicting locks. Range insert null locks protect against phantom reads by preventing other transactions from inserting rows within a scanned range.

These locks operate at the index level, specifically protecting gaps between existing index keys. When SQL Server scans an index range under serializable isolation level, it acquires RangeI-N locks on the gaps to ensure no new rows appear in subsequent reads of the same range. The shared variant (RIN-S) allows multiple readers to scan the same range simultaneously while blocking inserters.

The lock manager evaluates compatibility using the standard matrix where RangeI-N locks conflict with insert operations attempting to place new keys within the protected range. Sessions holding exclusive locks on overlapping ranges will cause requesting sessions to wait with LCK_M_RIN_S.

SQL Server 2019 introduced improvements to lock escalation thresholds that can reduce these waits in some scenarios. SQL Server 2022's intelligent query processing optimizations may choose different execution plans that avoid range locks entirely when possible.

The duration of these waits depends on the holding transaction's commit behavior. Long-running serializable transactions create extended blocking windows, particularly problematic in OLTP workloads mixing read and insert operations on the same index ranges.

-- Identify current LCK_M_RIN_S blocking chains
SELECT 
    l.request_session_id,
    DB_NAME(l.resource_database_id) AS database_name,
    OBJECT_NAME(p.object_id, l.resource_database_id) AS table_name,
    i.name AS index_name,
    l.resource_type,
    l.resource_description,
    l.request_mode,
    l.request_status,
    s.login_name,
    s.program_name,
    t.text AS sql_text
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE l.request_mode = 'RangeI-N'
AND l.request_status = 'WAIT';

-- Find sessions with serializable isolation causing range locks
SELECT 
    s.session_id,
    s.transaction_isolation_level,
    DB_NAME(r.database_id) AS database_name,
    COUNT(*) AS range_lock_count,
    s.login_name,
    s.program_name,
    t.text AS current_sql
FROM sys.dm_tran_locks r
INNER JOIN sys.dm_exec_sessions s ON r.request_session_id = s.session_id
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE r.resource_type IN ('KEY', 'RID')
AND r.request_mode LIKE 'RangeI%'
AND s.transaction_isolation_level = 4 -- SERIALIZABLE
GROUP BY s.session_id, s.transaction_isolation_level, r.database_id, s.login_name, s.program_name, t.text
ORDER BY range_lock_count DESC;

-- Analyze wait statistics for range insert locks
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 LIKE 'LCK_M_RIN%'
ORDER BY wait_time_ms DESC;

-- Identify problematic queries causing range lock contention
SELECT TOP 10
    qs.sql_handle,
    qs.plan_handle,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%SERIALIZABLE%'
   OR qt.text LIKE '%HOLDLOCK%'
   OR qt.text LIKE '%XLOCK%'
ORDER BY qs.total_elapsed_time DESC;

Reduce isolation level for read operations This script identifies and suggests isolation level changes for queries that may not require serializable reads.

-- WARNING: Test isolation level changes thoroughly in development
-- Check current connection isolation levels causing contention
SELECT DISTINCT
    s.session_id,
    CASE s.transaction_isolation_level 
        WHEN 1 THEN 'READ UNCOMMITTED'
        WHEN 2 THEN 'READ COMMITTED' 
        WHEN 3 THEN 'REPEATABLE READ'
        WHEN 4 THEN 'SERIALIZABLE'
        WHEN 5 THEN 'SNAPSHOT'
    END AS current_isolation_level,
    'READ COMMITTED SNAPSHOT' AS suggested_level,
    s.program_name,
    COUNT(*) as lock_count
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_tran_locks l ON s.session_id = l.request_session_id
WHERE s.transaction_isolation_level >= 3
AND l.request_mode LIKE 'RangeI%'
GROUP BY s.session_id, s.transaction_isolation_level, s.program_name;

-- Enable Read Committed Snapshot at database level (requires exclusive access)
-- ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON;

Kill blocking sessions (emergency only) Use when critical operations are blocked by long-running serializable transactions.

-- DANGER: Only use in emergencies after confirming transaction can be safely rolled back
-- Identify and prepare to kill long-running blocking sessions
SELECT 
    'KILL ' + CAST(s.session_id AS VARCHAR(10)) + ';' AS kill_command,
    s.session_id,
    s.login_name,
    s.program_name,
    s.last_request_start_time,
    DATEDIFF(minute, s.last_request_start_time, GETDATE()) AS minutes_running,
    t.text AS sql_text
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_tran_locks l ON s.session_id = l.request_session_id
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE l.request_mode LIKE 'RangeI%'
AND s.session_id IN (
    SELECT DISTINCT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id > 0
    AND wait_type = 'LCK_M_RIN_S'
)
AND DATEDIFF(minute, s.last_request_start_time, GETDATE()) > 10;

-- Execute the kill commands manually after review

Add covering indexes to reduce range scanning Creates indexes that eliminate the need for range locks on certain query patterns.

-- Identify missing indexes that could reduce range scan requirements
SELECT DISTINCT
    d.database_id,
    DB_NAME(d.database_id) AS database_name,
    OBJECT_NAME(d.object_id, d.database_id) AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_RangeLock' +
    ' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(d.object_id, d.database_id)) +
    ' (' + ISNULL(d.equality_columns, '') + 
    CASE WHEN d.inequality_columns IS NOT NULL AND d.equality_columns IS NOT NULL THEN ',' ELSE '' END +
    ISNULL(d.inequality_columns, '') + ')' +
    CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE (' + d.included_columns + ')' ELSE '' END +
    ';' AS suggested_index
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.objects o ON d.object_id = o.object_id
WHERE d.database_id = DB_ID()
AND EXISTS (
    SELECT 1 FROM sys.dm_tran_locks l 
    WHERE l.resource_associated_entity_id IN (
        SELECT hobt_id FROM sys.partitions WHERE object_id = d.object_id
    )
    AND l.request_mode LIKE 'RangeI%'
);

-- Review and create indexes manually after testing

Configure Read Committed Snapshot Isolation (RCSI) at the database level to eliminate most serializable lock contention while maintaining data consistency. RCSI uses row versioning instead of range locks for read operations, dramatically reducing LCK_M_RIN_S waits.

Design application transaction boundaries to minimize the duration of serializable transactions. Break long-running operations into smaller, committed chunks when business logic permits. Use explicit transaction scope with appropriate isolation levels rather than connection-level defaults.

Implement strategic indexing to support range queries without requiring extensive range locks. Covering indexes that include all columns referenced in WHERE and SELECT clauses allow the optimizer to satisfy queries without scanning large ranges or acquiring excessive locks.

Monitor isolation level usage across applications using extended events or DMV queries. Many applications default to higher isolation levels than necessary. Educate development teams on isolation level implications and establish coding standards that specify appropriate levels for different operation types.

Consider partitioning large tables where range queries consistently target specific date ranges or other logical boundaries. Partition elimination can reduce the scope of range locks and improve concurrent access patterns.

Set up automated monitoring for LCK_M_RIN_S wait accumulation using SQL Agent jobs or third-party tools. Alert when wait times exceed baseline thresholds, typically when average waits exceed 100ms or total wait time grows beyond normal patterns.