What are lock escalation and its potential impact on a busy system?

Intermediate

Answer

Lock escalation happens when SQL Server converts many fine grained row or page level locks into a single, larger table level lock to reduce the memory overhead of tracking so many individual locks, but this can unexpectedly block other transactions that only needed access to a small, unrelated part of the same table.

-- Escalation typically occurs automatically
-- after a transaction acquires around 5000 locks
-- on a single table within one statement

Real-world example A large batch update unexpectedly escalates to a table level lock, briefly blocking unrelated queries trying to read other rows in the same table, prompting the team to break the batch update into smaller chunks to avoid triggering escalation.

Common follow-ups: How do you prevent lock escalation from happening during a large batch operation?;What are the signs that lock escalation is negatively affecting your system?

Query Optimization & Plans;Transactions & ACID

All 7 Isolation & Locking questions