LCK_M_RIn_NL Wait Type in SQL Server

---

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/HOLDLOCK hints, 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

1. Confirm who runs serializable (sys.dm_exec_sessions.transaction_isolation_level = 4) and whether it is intentional.
2. Rework upserts: unique constraint plus catch-duplicate-and-retry under READ COMMITTED beats a serializable existence check for both throughput and deadlocks.
3. 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.

---