Description
This wait occurs when a request is waiting to acquire a Key Range Insert Null lock. This mechanism acquires locks on a subset of rows in an index but only if the transaction isolation SERIALIZABLE is used. A null lock is used internally and is not documented by Microsoft. Values in this type are rare to non-existent
Resolved by
DBAs and Developers
Suggested solutions
- Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
- Keep transaction durations short
- Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
- Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
- Indexing, ensure long running queries and transactions are optimised
- 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