LCK_M_RIn_NL is a wait to acquire an Insert Range lock with a NULL lock on the key itself. It appears when a session under SERIALIZABLE isolation wants to insert a new key: SQL Server locks the range between the neighbouring keys to keep the insert phantom-safe, while the NULL component on the key is an instant-release formality.
Like every LCK_M_R* wait, its very presence tells you serializable isolation is in play.
Is It a Problem?
It matters for the same reason the other range lock waits do: most systems never chose serializable deliberately. Concurrent inserts probing into the same key range under serializable block each other and are a well-known deadlock recipe, particularly in upsert patterns (IF NOT EXISTS ... INSERT with HOLDLOCK) where two sessions both range-lock the gap and then both try to insert into it.
If this wait registers alongside deadlock reports mentioning range locks, that is your pattern.
Common Causes
- Upsert code using
SERIALIZABLE/HOLDLOCKhints, two sessions colliding on the same range. - Distributed transactions silently promoting sessions to serializable.
- Hot insert points, ascending keys where every insert lands in the same rightmost range.
What To Do
- Confirm who runs serializable (
sys.dm_exec_sessions.transaction_isolation_level = 4) and whether it is intentional. - Rework upserts: unique constraint plus catch-duplicate-and-retry under
READ COMMITTEDbeats a serializable existence check for both throughput and deadlocks. - If serializable must stay, serialise the writers on an application lock (
sp_getapplock) so they queue rather than deadlock.
How To See It
Rank it against everything else with Get-WaitStatistics. Read it with the other LCK_M_R* waits and your deadlock history; they tell one story.
Part of the SQL Server Wait Types Library.
Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait Types.
Leave a Reply