What is lock escalation in a database engine, and how would you recognise and mitigate it when a production workload starts suffering from it?

## The mechanism When a transaction locks rows individually, each lock is an entry in the engine's lock manager — memory that is finite and shared across all sessions. A statement modifying ten million rows would want ten million entries. Rather than let that exhaust the instance, some engines **escalate**: they acquire a single lock on the parent object (the table, or in some products the partition) in a mode that covers everything the fine locks covered, then release all the fine locks. The key properties: - Escalation goes **row/page → table**, in one jump. It is not gradual. - It is triggered by **lock count for a single statement** (SQL Server: roughly 5,000 locks on one object) or by **total lock memory pressure** across the instance (SQL Server: when locks consume ~40% of available lock memory). - The resulting coarse lock is held **for the rest of the transaction**, not just the statement. That is why one escalating statement early in a long transaction can block a table for minutes. - If the coarse lock cannot be granted immediately — someone else holds an incompatible lock inside the table — the escalation attempt typically **fails and is retried later**, so the transaction keeps its fine locks and continues. ## Why engines disagree about it SQL Server and DB2 escalate by design: bounded lock memory is treated as the more important invariant, and the cost is a concurrency cliff. PostgreSQL and MySQL/InnoDB **never escalate** — PostgreSQL records the locking transaction id in the row header itself, so row-level write locks cost no lock-manager entry, and InnoDB stores lock bits compactly per page of records. Their tail risk is different: a huge transaction consumes memory or undo/version space rather than suddenly blocking the table. Neither approach is wrong; they are different answers to "what should degrade first under an oversized transaction — memory or concurrency?" Being able to state that trade-off is the senior-level answer. ## Recognising it in production Escalation rarely announces itself as "escalation". What you observe is: 1. **A cliff, not a slope.** Latency is flat as the batch grows, then falls off sharply. Nothing about the query plan changed; the lock count crossed a threshold. 2. **A blocking chain with a coarse blocker.** Many sessions wait, and the head of the chain holds an *object*-level lock even though its statement logically touched a subset of rows. In SQL Server, `sys.dm_tran_locks` shows `resource_type = OBJECT` with mode X where you expected `KEY`/`RID` locks. That mismatch is the tell. 3. **Correlation with batch size.** The same job at 1,000 rows per transaction is invisible; at 50,000 it takes the table. 4. **Explicit signals** where the engine emits them — SQL Server's `Lock:Escalation` extended event / trace event names the object and the trigger. A useful diagnostic habit: when blocking is reported, always look at the *granularity* of the blocker's lock, not only its mode. "X on one row" and "X on the table" produce very different blast radii from the same statement text. ## Mitigations, best first **1. Reduce rows touched per statement.** Escalation is triggered by lock count, and lock count comes from the access path. A missing index that turns a targeted update into a scan is the single most common root cause; adding the index often removes the problem entirely without touching escalation settings. **2. Batch and commit.** Rewrite `UPDATE ... WHERE <broad predicate>` as a loop over key ranges of a few thousand rows, committing each chunk. This caps live lock count below the threshold, releases locks regularly so other work interleaves, and bounds transaction log / undo growth as a bonus. It also makes the job restartable. **3. Partition the table.** In engines that escalate to the partition level when configured to (SQL Server's `ALTER TABLE ... SET (LOCK_ESCALATION = AUTO)`), escalation lands on one partition rather than the whole object, so unrelated partitions keep serving traffic. **4. Keep transactions short.** Escalation is only painful because the coarse lock is held to commit. A transaction that escalates and then commits in 50 ms is barely noticeable; one that escalates and then waits on an external API call is an outage. **5. Disable escalation for a specific table** (`LOCK_ESCALATION = DISABLE`) — a genuine last resort. You have swapped a concurrency cliff for an unbounded lock-memory tail, and lock memory is instance-wide, so the failure mode you create hurts every database on the instance, not just this table. ## The anti-patterns Grabbing a table hint to force row locks is the reflex to resist: hints override the engine's judgement permanently, including on the plans where escalation was the right call. Similarly, raising lock memory limits treats the symptom while leaving an unbounded transaction unbounded. ## What interviewers listen for A correct definition (many fine locks → one coarse lock, driven by count or memory pressure, held to end of transaction), recognition that it is a *memory-protection* feature rather than a bug, the diagnostic move of checking the blocker's lock granularity, and mitigations that start with batching and indexing rather than with disabling the mechanism. Mentioning that PostgreSQL and InnoDB never escalate shows you know it is an engine-family behaviour, not a universal law of relational databases.