LCK_M_RS_U Wait Type Explained

The SQL Server lock manager generates LCK_M_RS_U waits during range scan operations under SERIALIZABLE or REPEATABLE READ isolation levels. This wait specifically involves two lock components: an Update (U) lock on the current key value and a Range-Shared (RS) lock spanning from the previous key to the current key.

Range locks prevent phantom reads by locking not just existing rows but also the gaps between rows. When a transaction scans an index under SERIALIZABLE isolation, it places range locks to ensure no other transaction can insert, update, or delete rows within the scanned range. The "RS_U" combination indicates the transaction holds a range-shared lock on the gap and needs an update lock on the key itself.

SQL Server 2016 introduced adaptive query processing which can dynamically change execution plans, potentially altering locking patterns mid-execution. SQL Server 2019 added intelligent query processing features that may reduce some range locking scenarios through batch mode processing. SQL Server 2022's parameter-sensitive plan optimization can generate different execution plans for the same query with different parameter values, potentially creating inconsistent locking patterns.

The lock manager's range locking mechanism works by treating each index key as a boundary. For a key K, the range lock covers the gap from the previous key to K. Multiple concurrent transactions requesting overlapping range locks create a dependency chain where each must wait for the previous transaction's range locks to release.

This wait type commonly appears in three scenarios: serializable transactions performing range scans, foreign key constraint checks during bulk operations, and cursor operations with high isolation levels. The lock compatibility matrix shows that Range-Shared locks conflict with Intent-Exclusive and Exclusive locks, creating blocking chains when concurrent modifications occur within the same key range.

-- Identify sessions with active LCK_M_RS_U waits
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.transaction_isolation_level,
    OBJECT_NAME(p.object_id) AS table_name,
    i.name AS index_name,
    t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
LEFT JOIN sys.partitions p ON r.wait_resource LIKE '%' + CAST(p.hobt_id AS VARCHAR(20)) + '%'
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE r.wait_type = 'LCK_M_RS_U';

-- Analyze lock blocking chains for range locks
SELECT 
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_resource,
    blocking.session_id AS blocking_session,
    blocking_sql.text AS blocking_query,
    blocked_sql.text AS blocked_query,
    blocked.wait_time / 1000 AS wait_seconds
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
WHERE blocked.wait_type LIKE 'LCK_M_RS%';

-- Show transaction isolation levels and lock escalation settings
SELECT 
    s.session_id,
    s.transaction_isolation_level,
    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 isolation_level_name,
    t.name AS table_name,
    t.lock_escalation_desc,
    s.open_transaction_count
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
JOIN sys.partitions p ON r.wait_resource LIKE '%' + CAST(p.hobt_id AS VARCHAR(20)) + '%'
JOIN sys.tables t ON p.object_id = t.object_id
WHERE r.wait_type = 'LCK_M_RS_U';

-- Identify indexes with high range lock contention
SELECT 
    OBJECT_NAME(p.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    COUNT(*) AS range_lock_waits,
    AVG(r.wait_time) AS avg_wait_ms,
    MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests r
JOIN sys.partitions p ON r.wait_resource LIKE '%' + CAST(p.hobt_id AS VARCHAR(20)) + '%'
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE r.wait_type LIKE 'LCK_M_RS%'
GROUP BY OBJECT_NAME(p.object_id), i.name, i.type_desc
ORDER BY COUNT(*) DESC;

-- Show active transactions with range locks
SELECT 
    t.transaction_id,
    t.name AS transaction_name,
    s.session_id,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    s.transaction_isolation_level,
    l.resource_type,
    l.request_mode,
    l.request_status
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_locks l ON t.transaction_id = l.request_owner_id
WHERE l.request_mode LIKE '%RS%' OR l.request_mode LIKE '%RangeS%'
ORDER BY t.transaction_begin_time;

Reduce isolation level for read operations

-- Change transaction isolation level to reduce range locking
-- Only use when phantom reads are acceptable
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Or enable read committed snapshot isolation database-wide
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON;

This eliminates range locks for most read operations but allows phantom reads. Test thoroughly in development as this changes fundamental concurrency behavior.

Enable lock escalation to reduce granular locks

-- Enable table-level lock escalation to reduce range lock contention
-- WARNING: Can cause more blocking if many concurrent small transactions exist
ALTER TABLE [dbo].[YourTable] SET (LOCK_ESCALATION = TABLE);

-- Alternative: Disable lock escalation entirely (use cautiously)
-- ALTER TABLE [dbo].[YourTable] SET (LOCK_ESCALATION = DISABLE);

Lock escalation converts many row locks to a single table lock, reducing lock manager overhead but potentially increasing blocking scope. Monitor blocking patterns after implementation.

Optimize query to reduce range scan requirements

-- Replace range scans with more selective seeks using covering indexes
-- Example: Instead of scanning entire range, use specific lookups
CREATE NONCLUSTERED INDEX IX_OptimizedSeek 
ON [dbo].[YourTable] (SearchColumn, DateColumn)
INCLUDE (Column1, Column2, Column3)
WITH (FILLFACTOR = 90, PAD_INDEX = ON);

-- Rewrite queries to avoid serializable scans
-- Before: SELECT * FROM table WHERE date_col BETWEEN @start AND @end
-- After: Use specific date boundaries with EXISTS clauses

Covering indexes eliminate key lookups and reduce the range of keys that need range locks. The fillfactor setting reduces page splits that can cause additional locking.

Implement query hints for specific problematic queries

-- Use READUNCOMMITTED hint for reporting queries that can tolerate dirty reads
SELECT * FROM [dbo].[YourTable] WITH (READUNCOMMITTED)
WHERE condition = @value;

-- Or use NOLOCK (equivalent but less explicit)
SELECT * FROM [dbo].[YourTable] WITH (NOLOCK)
WHERE condition = @value;

-- For specific cases, use READPAST to skip locked rows
SELECT * FROM [dbo].[YourTable] WITH (READPAST)
WHERE condition = @value;

These hints bypass normal locking protocols. READUNCOMMITTED and NOLOCK can return inconsistent data. READPAST skips locked rows entirely, which may miss data. Use only when data consistency requirements are well understood.

Configure READ_COMMITTED_SNAPSHOT at the database level to eliminate most range locking scenarios while maintaining consistent reads through row versioning. This requires additional tempdb capacity for version storage but dramatically reduces locking contention.

Design indexes specifically to minimize range scan operations. Create covering indexes that include all columns needed by frequent queries, allowing index seeks instead of range scans. Position highly selective columns first in composite indexes to reduce the key range that requires locking.

Implement application-level pagination using OFFSET/FETCH or keyset pagination instead of large range queries. Break large serializable transactions into smaller, more focused operations that hold range locks for shorter durations.

Monitor lock escalation thresholds and adjust them appropriately for your workload. Tables with frequent small updates should use AUTO lock escalation, while large reporting tables might benefit from TABLE escalation to reduce lock manager overhead.

Configure Resource Governor to limit the number of concurrent high isolation level queries that can execute simultaneously, preventing excessive range lock contention during peak periods.

Use SQL Server Extended Events to capture range locking patterns and identify specific queries causing the most contention. Create custom events targeting lock_acquired and lock_released events with range lock filters.