LCK_M_RIn_X

A transaction is waiting to acquire a key-range lock on an index. This lock has two components:  

  • a lock in insert mode on the range between two consecutive index entries or rows. Other transactions can't add data to this locked range.
  • a lock in exclusive mode on the index entries or rows. The locked rows can't be read or modified by other transactions.

Key-range locks protect the rows included in a record set, and prevent phantom reads, insertions and deletions when the T-SQL is using serializable transaction level.

Investigating

  • Check the affected queries and the Top 10 queries table. Tune queries so they run faster and require fewer locks. See Query tuning (TechNet).

  • On the Analysis page, check these metrics for additional details on locking behavior:

    • Lock timeouts/sec

    • Lock timeouts/sec

    • Avg. lock wait time

  • On the Analysis page, check these metrics to see whether memory problems or I/O bottlenecks are causing locks to be held for longer than usual:

    • Machine: memory used

    • Memory pages/sec

    • Disk avg. read time

    • Disk avg. write time

    • Buffer cache hit ratio

    • Buffer page life expectancy

See also Investigating I/O bottlenecks.


 


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?