LCK_M_RX_S

Description

This wait occurs when a request is waiting to acquire a Key Range Share lock. This mechanism acquires locks on a subset of rows in an index but only if the transaction isolation SERIALIZABLE is used. A share lock typically occurs when read requests are blocked by another transaction (implicit of explicit) that has been kept open for an extended period of time

Resolved by

DBAs and Developers

Suggested solutions

  1. Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
  2. Keep transaction durations short
  3. Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
  4. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
  5. Indexing, ensure long running queries and transactions are optimised
  6. Ensure locks being escalated correctly: row, page or table (if table is partitioned considered enabling partition escalation)

Additional research

Key-Range Locking
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations

See more

Consider using ApexSQL Monitor, a Windows and SQL Server monitoring tool able to track SQL Server wait types, including the individual query wait statistics

⇐ Back to index