LCK_M_RX_X Wait Type Explained

The LCK_M_RX_X wait type manifests when SQL Server's lock manager cannot immediately grant a requested RangeX-X lock. This lock mode combines two distinct locking concepts: an exclusive lock on the current key value (X) and an exclusive range lock (RX) protecting the gap between the current key and the previous key in the index sort order.

SQL Server's lock manager maintains these range locks through the lock hash table, where each lock resource is identified by the database ID, object ID, index ID, and the specific key value. When a transaction requests RangeX-X locks, typically under serializable isolation level or when explicit locking hints force range lock acquisition, the lock manager must verify that no conflicting locks exist on both the specific key and the preceding range gap.

The scheduler thread requesting this lock transitions into a suspended state and registers the wait in sys.dm_os_waiting_tasks. The lock manager places the request into a conversion queue if a compatible lock exists on the resource, or into a grant queue if no lock exists. Range locks specifically protect against phantom reads by preventing other transactions from inserting keys within the protected range.

In SQL Server 2016 and later versions, lock escalation thresholds and memory pressure handling changed, affecting when range locks convert to table locks. SQL Server 2019 introduced intelligent query processing features that can influence locking patterns, while SQL Server 2022's parameter sensitive plan optimization can alter execution plans and subsequently change locking behavior for identical queries under different parameter values.

-- Current blocking chain for RX-X locks
SELECT 
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    er.command,
    er.database_id,
    DB_NAME(er.database_id) as database_name,
    st.text as sql_text
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE wt.wait_type = 'LCK_M_RX_X';

-- Lock details for sessions involved in RX-X contention
SELECT 
    tl.request_session_id,
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) as object_name,
    tl.resource_description,
    tl.request_mode,
    tl.request_status,
    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_desc
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
WHERE tl.request_mode LIKE '%RangeX-X%'
   OR tl.request_session_id IN (
       SELECT DISTINCT blocking_session_id 
       FROM sys.dm_os_waiting_tasks 
       WHERE wait_type = 'LCK_M_RX_X'
   );

-- Historical wait statistics for range lock contention
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
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'LCK_M_RX%'
ORDER BY wait_time_ms DESC;

-- Index usage and contention points for affected objects
SELECT 
    OBJECT_NAME(i.object_id, DB_ID()) as table_name,
    i.name as index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_update,
    p.rows as row_count
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.object_id IN (
    SELECT DISTINCT resource_associated_entity_id
    FROM sys.dm_tran_locks 
    WHERE request_mode LIKE '%RangeX-X%'
)
AND ius.database_id = DB_ID();

Identify and terminate long-running serializable transactions

-- WARNING: This script terminates active sessions. Test thoroughly in development.
-- Only run during maintenance windows or after confirming with application teams.

DECLARE @session_id int;
DECLARE kill_cursor CURSOR FOR
SELECT DISTINCT wt.blocking_session_id
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_sessions s ON wt.blocking_session_id = s.session_id  
WHERE wt.wait_type = 'LCK_M_RX_X'
  AND s.transaction_isolation_level = 4 -- SERIALIZABLE
  AND DATEDIFF(minute, s.last_request_start_time, GETDATE()) > 5; -- Running > 5 minutes

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session: ' + CAST(@session_id AS VARCHAR(10));
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM kill_cursor INTO @session_id;
END;
CLOSE kill_cursor;
DEALLOCATE kill_cursor;

This script terminates blocking sessions running under serializable isolation level for more than 5 minutes. Use extreme caution as this will rollback active transactions.

Enable snapshot isolation to reduce range lock contention

-- Enable snapshot isolation database options
-- This requires exclusive database access briefly during ALTER DATABASE

ALTER DATABASE [YourDatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;

-- Verify snapshot isolation is enabled
SELECT 
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases 
WHERE name = 'YourDatabaseName';

Enabling snapshot isolation eliminates range locks for read operations but increases tempdb usage. Monitor tempdb space after implementation.

Add covering indexes to reduce lock escalation

-- Template for creating covering indexes on frequently contested ranges
-- Replace table_name, existing_key_columns, and include_columns based on workload analysis

CREATE NONCLUSTERED INDEX [IX_TableName_Covering_RangeLock] 
ON [dbo].[table_name] ([existing_key_columns])
INCLUDE ([frequently_selected_columns])
WITH (
    FILLFACTOR = 90,           -- Leave space for inserts
    PAD_INDEX = ON,            -- Apply fillfactor to intermediate pages
    ONLINE = ON,               -- Build online if Enterprise Edition
    MAXDOP = 4                 -- Limit parallelism during build
);

Covering indexes reduce the need for key lookups that can trigger additional range locks. Monitor index maintenance overhead after creation.

Configure applications to use read committed snapshot isolation instead of serializable isolation level where phantom reads are not critical business requirements. This eliminates range lock acquisition for most read operations while maintaining transactional consistency.

Implement query hints strategically using NOLOCK for reporting queries that can tolerate dirty reads, or READPAST for queue-processing patterns where skipping locked rows is acceptable. Avoid WITH (SERIALIZABLE) hints unless absolutely required for business logic.

Design indexes with appropriate fillfactor settings (85-90%) on frequently updated tables to reduce page splits that can intensify range lock contention. Monitor index fragmentation weekly and rebuild indexes showing logical fragmentation above 30%.

Establish connection pooling with proper timeout settings to prevent abandoned connections from holding range locks indefinitely. Configure query timeout values between 30-60 seconds for OLTP workloads to prevent runaway queries from blocking range lock requests.

Monitor sys.dm_os_wait_stats daily for LCK_M_RX_X trends and correlate spikes with deployment schedules or batch processing windows. Set up automated alerts when range lock waits exceed 10% of total wait time, indicating systematic contention requiring architectural review.