LCK_M_RX_S Wait Type Explained

The lock manager requests an RX_S lock when a query needs to read a specific key value while preventing other transactions from inserting records in the gap before that key. This lock type combines two operations: a shared lock (S) on the current key value for read access, and an exclusive range lock (RX) on the gap between the current key and the previous key to prevent phantom inserts.

The scheduler places the requesting task into a wait state when another session holds conflicting locks on either the key value or the range. Common conflicting locks include exclusive locks (X) on the target key, shared range locks (RangeS_S) from other serializable transactions, or insert range locks (RangeI_N) from concurrent INSERT operations.

Starting with SQL Server 2019, the lock manager became more efficient at detecting compatible range operations, reducing unnecessary blocking in some scenarios.

The wait typically manifests in workloads mixing SELECT queries using SERIALIZABLE isolation with concurrent INSERT/UPDATE/DELETE operations on the same indexed columns. Page splits and index maintenance operations can amplify these waits by forcing lock escalation and extending lock hold times.

-- Current sessions experiencing LCK_M_RX_S waits
SELECT 
    s.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 query_text
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_RX_S';

-- Lock details for RX_S conflicts
SELECT 
    l.request_session_id,
    l.resource_type,
    l.resource_database_id,
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_type,
    l.request_status,
    OBJECT_NAME(p.object_id) AS table_name
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE l.request_mode LIKE '%RX%' OR l.resource_description LIKE '%RX%'
ORDER BY l.request_session_id;

-- Historical LCK_M_RX_S wait statistics
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_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_RX_S'
    AND waiting_tasks_count > 0;

-- Index usage on tables experiencing range lock waits
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats us ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_tran_locks l 
    WHERE l.resource_associated_entity_id = us.object_id 
    AND l.request_mode LIKE '%RX%'
);

-- Blocking chain analysis for range lock waits
WITH BlockingChain AS (
    SELECT 
        session_id,
        blocking_session_id,
        wait_type,
        wait_resource,
        0 AS level
    FROM sys.dm_exec_requests
    WHERE blocking_session_id = 0 
        AND session_id IN (
            SELECT DISTINCT blocking_session_id 
            FROM sys.dm_exec_requests 
            WHERE blocking_session_id != 0
        )
    
    UNION ALL
    
    SELECT 
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_resource,
        bc.level + 1
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
    WHERE r.wait_type LIKE 'LCK_M%'
)
SELECT * FROM BlockingChain
ORDER BY level, session_id;

Reduce transaction isolation level for read queries

-- Change isolation level for specific queries experiencing RX_S waits
-- Test thoroughly in development first
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Your SELECT query here
SELECT * FROM your_table WHERE your_conditions;
COMMIT;

This reduces the need for range locks by allowing phantom reads. Test application logic carefully as this changes consistency guarantees.

Add missing indexes to reduce scan ranges

-- Create covering indexes to eliminate range scans
-- Replace with actual table and column names from your workload
CREATE NONCLUSTERED INDEX IX_YourTable_Covering 
ON YourTable (KeyColumn1, KeyColumn2)
INCLUDE (NonKeyColumn1, NonKeyColumn2)
WITH (ONLINE = ON, MAXDOP = 4);
-- Monitor index usage after creation

Covering indexes eliminate key lookups and reduce the range of keys that need range locks. Test index overhead impact on write operations.

Implement query hints for problematic queries

-- Use NOLOCK hint for reports that can tolerate dirty reads
-- Only for read-only reporting queries
SELECT * FROM your_table WITH (NOLOCK)
WHERE your_conditions;

-- Alternative: Use snapshot isolation for consistency without blocking
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
-- This change affects all queries database-wide

NOLOCK eliminates all locking but allows inconsistent reads. Snapshot isolation provides consistency without blocking but increases tempdb usage.

Optimize transaction boundaries

-- Break large transactions into smaller chunks
DECLARE @BatchSize INT = 1000;
DECLARE @RowsProcessed INT = 1;

WHILE @RowsProcessed > 0
BEGIN
    BEGIN TRANSACTION;
    
    UPDATE TOP (@BatchSize) your_table 
    SET your_column = new_value
    WHERE your_conditions;
    
    SET @RowsProcessed = @@ROWCOUNT;
    COMMIT TRANSACTION;
    
    -- Small delay to allow other transactions
    WAITFOR DELAY '00:00:01';
END;

Smaller transactions hold range locks for shorter periods, reducing blocking duration.

Configure READ_COMMITTED_SNAPSHOT at the database level for applications that can accept snapshot isolation semantics instead of serializable reads. This eliminates most range locking scenarios while maintaining read consistency.

Create proper covering indexes for frequently executed range queries. Analyze the wait_resource values from diagnostic queries to identify which key ranges experience the most contention, then add indexes that eliminate the need to scan large ranges.

Implement connection pooling with shorter connection timeouts to prevent long-running transactions from holding range locks indefinitely. Set lock_timeout values appropriately for non-critical queries that can be retried.

Monitor lock escalation thresholds and consider partitioning large tables where range scans frequently conflict with concurrent modifications. SQL Server 2019 introduced more intelligent lock escalation that can reduce range lock conflicts in partitioned scenarios.

Schedule maintenance operations and bulk data modifications during low-activity periods to minimize conflicts with interactive queries requiring range locks. Use batch processing patterns that commit frequently rather than single large transactions.