A database engine can take locks at row, page, or whole-table granularity. What are the trade-offs, and what makes an engine choose a coarser or finer level?

## Why granularity is a dial at all Every lock protects a *resource*, and resources nest: a database contains tables, a table contains pages (fixed-size blocks, typically 8 or 16 KB), a page contains rows. The engine can place its lock at any level of that hierarchy. The choice is a pure engineering trade between two costs that pull in opposite directions. ## The cost of fine granularity A row lock is the finest practical unit in mainstream engines. Its benefit is obvious: two transactions touching different rows never wait for each other, so a hot table can absorb thousands of concurrent writers. Its costs are real but easy to overlook: - **Memory.** Each lock is an entry in the lock manager — roughly 100–250 bytes in engines that keep locks in a shared structure. A single statement that updates ten million rows would need ten million entries, which is why unbounded row locking is a genuine memory-exhaustion risk. - **CPU.** Acquire, conflict-check and release are hash-table operations under a latch. Ten million of them is not free, and the latch on hot hash buckets can itself become a scalability bottleneck on many-core machines. - **Conflict-check cost at coarse levels.** If rows are locked individually, how does a transaction that wants the *whole table* know whether anything inside is locked? Naively it would have to scan every row lock. This is precisely the problem intent locks exist to solve. InnoDB sidesteps part of the memory cost by storing lock bits per page-of-records rather than one heap object per row; PostgreSQL sidesteps it differently, marking the row's own header with the locking transaction id so a row-level write lock costs no lock-manager entry at all. Both are optimisations of the same idea. ## The cost of coarse granularity A table lock is one entry. Checking it is O(1). Memory is constant. It is the cheapest possible bookkeeping — and it serialises every writer to the table, including transactions working on completely unrelated rows. On a hot OLTP table that is catastrophic for throughput; on a nightly bulk load into a table nobody else is reading, it is free performance. ## The page-level middle ground Page locks were common in older engines (and remain a level SQL Server can use). One entry covers every row that happens to live on the page, which is a real win on scans. The failure mode is **false contention**: two transactions updating logically unrelated rows collide only because the storage layer placed those rows in the same block. Because physical layout is invisible to the application, the resulting blocking looks random and is very hard to reason about — a big reason modern engines defaulted to row locks. ## How the engine decides The input is the **estimated number of rows the access path will touch**: - Indexed lookup of a handful of rows → row locks. - Full scan with a modifying statement → the engine may take row locks and then escalate, or take a table lock up front, depending on the product. - `TRUNCATE`, `ALTER TABLE`, index rebuild → table-level exclusive lock, because the object's structure itself is changing. This is why plan quality and locking behaviour are coupled. A missing index turns "lock 3 rows" into "scan and lock the table", and the symptom the on-call engineer sees is not a slow query but a blocking storm. ## What the two families do differently PostgreSQL and MySQL/InnoDB lock rows and **never escalate** — they will happily hold millions of row locks and spend the memory. SQL Server and DB2 **escalate**: past a per-statement threshold (around 5,000 locks in SQL Server, plus memory-pressure triggers) they trade the accumulated fine locks for one coarse lock, deliberately buying memory back with concurrency. Both designs are defensible. Never escalating gives predictable concurrency and an unbounded memory tail; escalating gives bounded memory and a concurrency cliff that arrives suddenly under load. ## What the application actually controls You rarely set granularity directly, and hint-forcing it is usually a smell. The levers that matter: 1. **Touch fewer rows per statement** — selective predicates, supporting indexes. 2. **Batch large mutations** — update 5,000 rows per transaction in a loop rather than 5,000,000 in one, which caps lock count, keeps transactions short and avoids escalation thresholds. 3. **Keep transactions short** — granularity determines *how much* is locked, but duration determines *for how long*, and duration is usually the bigger production problem. 4. **Schedule DDL deliberately**, since it takes the coarsest lock there is. ## What interviewers listen for The trade-off stated in both directions (concurrency vs. per-lock overhead), awareness that page locks create false contention that the application cannot see or predict, and — the senior signal — connecting granularity back to access-path selection: the fastest way to make an engine lock too much is to make it scan too much.