If you’ve ever watched a single point query hang for a few seconds while some unrelated batch job ran in another session, and the two were nowhere near each other in the table, this is for you. The query was reading one row by primary key. The batch job was updating a completely different range of rows. On paper they never intersect, and row-level locking is supposed to let them run right past each other. But the point read sat there and waited, and when I pulled the wait stats it was blocked on a lock the batch job never explicitly asked for.
That lock is lock escalation, and it turns a row-level update into a table-level outage.
The problem
SQL Server starts a big write with fine-grained locks, one per row or per page, so that concurrent sessions touching other rows can keep going. That’s the whole point of row locking. But locks cost memory, and holding tens of thousands of them for one statement is expensive, so when a single statement acquires roughly 5000 locks on one object, SQL Server trades them all in for one lock on the entire table. It’s an optimization for the writer. For everyone else it’s a wall. A table-level exclusive lock means no other session can touch any row of that table, including rows the writer never went near, until the writing transaction commits.
So the failure mode isn’t “two sessions fought over the same row.” It’s “one session updated 8000 rows and the whole table went dark.” I wanted to see the exact moment it flips, so I built a SQL Server 2022 with a 200,000-row orders table, a clustered primary key on id, and started holding transactions open on purpose.
Watching an untouched row block
First experiment, the simplest one. Open a transaction, update rows 1 through 8000, and leave it open. Then from a second session read row 150000 by primary key, a row the update never touched, and time it.
-- session 1
BEGIN TRAN;
UPDATE orders SET amount = amount + 1 WHERE id BETWEEN 1 AND 8000;
-- (held open ~3 seconds, then COMMIT)
-- session 2, meanwhile
SELECT amount FROM orders WHERE id = 150000;
While session 1 held its transaction open, I looked at what it was actually holding in sys.dm_tran_locks:
resource_type request_mode count
KEY - 0
PAGE - 0
OBJECT X 1
Zero row locks. Zero page locks. One exclusive lock on the whole object. The 8000 individual row locks it started with had already collapsed into a single table X lock before I even looked. And session 2, reading a row 142,000 positions away from anything the update touched, went from a 33.4ms baseline to a 3001ms wait, returning only after session 1 committed at 3010ms. It didn’t slow down. It stopped, and waited out the writer.
A point read of row 150000, while rows 1 to 8000 were being updated
point SELECT latency, ms
The read never had a chance to be slow at its own work. It was fast, 33ms, and then it wasn’t allowed to run at all. That’s the tell for escalation in production: not gradually rising latency, but reads that flatline until some writer commits.
Where the cliff actually is
The interesting question is exactly when SQL Server decides to trade in the row locks. The documented number is around 5000 locks per statement, so I swept the update size from 500 rows up to 12000, and after each one, still inside an open transaction, I counted the locks and fired a point read at the untouched row 150000 with an 800ms lock timeout.
update_size key_locks object_mode escalated concurrent_read
500 500 IX no 0.7ms
2000 2000 IX no 0.7ms
4000 4000 IX no 2.0ms
5000 5000 IX no 2.5ms
6000 6000 IX no 0.5ms
7000 0 X yes 801.7ms (timed out)
8000 0 X yes 803.8ms (timed out)
12000 0 X yes 802.6ms (timed out)
Two things surprised me here. First, the escalation didn’t happen at exactly 5000. At 6000 held row locks SQL Server was still holding all 6000 of them, IX on the object, and the concurrent read went through in half a millisecond. The flip landed somewhere between 6000 and 7000, a bit north of the documented threshold, because page-lock coalescing and the exact plan shift the count around. Don’t treat 5000 as a hard line, treat it as a neighborhood. Second, the cliff is genuinely a cliff. There’s no ramp. 6000 rows is 0.5ms and business as usual, 7000 rows is a table lock and every other reader on the table times out.
Row locks held, and concurrent read latency, across the escalation cliff
row (KEY) locks held
concurrent read, ms (800ms timeout)
The left panel is the mechanism in one picture. The row locks climb honestly, 4000, 5000, 6000, and then at 7000 they don’t climb to 7000, they drop to zero, because they’ve been swapped for the one lock that ruins everyone’s afternoon. The right panel is what your users feel. Nothing, nothing, nothing, then a timeout.
Getting the same work done without the wall
The update still has to happen. You do have 50000 rows to touch. The fix isn’t to avoid the write, it’s to never let one statement cross the escalation threshold, and the plain way to do that is to break the update into batches small enough that each one stays under the cliff and releases its locks between batches.
-- instead of one statement over 50000 rows:
DECLARE @lo INT = 1;
WHILE @lo <= 50000
BEGIN
UPDATE orders SET amount = amount + 1
WHERE id BETWEEN @lo AND @lo + 1999; -- 2000 rows, well under the cliff
SET @lo += 2000; -- each batch commits on its own
END
To measure it I ran a background reader hammering point selects at rows the update never covers, for the full duration of the write, and compared three ways of doing the same 50000-row update: one big statement, 2000-row batches, and one big statement with escalation turned off on the table (ALTER TABLE orders SET (LOCK_ESCALATION = DISABLE)). Each writer held its locks for about the same three seconds of wall time, so the only variable is how the locking behaves.
Reader throughput and worst-case latency, same 50,000-row update three ways
reads completed during the write
worst read latency, ms
One note on why I’m quoting the worst case and the blocked count instead of a percentile. In the single-update run the reader’s p99 was a tidy 2.4ms, which looks completely fine, because the three reads that ate a full second are less than 1% of the samples and hide under the 99th percentile entirely. The p99 lies here. The blocked count and the max are where the pain actually lives, and they say three reads timed out and the worst waited over a second. When you’re hunting escalation, don’t trust the percentile, count the timeouts.
Batching wins on the number that matters, reader throughput, without giving up anything. Disabling escalation on the table wins by a hair more, because it never takes the table lock at all, but it does it by holding all 50000 fine-grained locks in memory for the duration, and that memory pressure is its own tax at real scale. Batching gets you almost the identical result and never holds more than a couple thousand locks at once, which is why it’s the default reach.
The takeaway
Lock escalation is a memory optimization that most people meet as an availability incident. One statement crosses roughly 5000 locks on an object, SQL Server swaps its row locks for a single table lock, and every other session on that table blocks until it commits, even the ones reading rows the writer never touched. Treat that 5000 as approximate, because coalescing pushed my flip up past 6000.
If a big write is starving your readers, the plain fix is to batch it into chunks that stay under the cliff and commit between them, which on my box turned 457 completed reads into 5210 and dropped the worst read from a 1007ms timeout to 7ms. LOCK_ESCALATION = DISABLE does about the same but pays for it in lock memory, so keep it for the specific table where you’ve measured that batching isn’t an option. And the one thing to remember when you’re staring at wait stats and a query is blocked on a row nobody else is using: the writer didn’t lock that row, it locked the table, and it did it to save memory. The harness is on GitHub if you want to watch your own escalation cliff. These are laptop numbers under emulation, the shape of the thing, not a capacity statement about your server.
Comments