LCK_M_RIn_NL

A transaction is waiting to acquire a key-range lock on an index. Before inserting a new key into the index, this lock is used to test the ranges. The 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 NULL lock on the last key in the range to protect that index entry.

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 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.


 


Didn't find what you were looking for?