Locks, Latches, and Coordination

Concurrency still needs traffic rules

MVCC lets readers and writers avoid many direct conflicts, but a database still needs coordination everywhere. A table cannot be dropped while another session scans it. Two processes cannot update the same buffer header at the same instant. An index page split cannot be observed halfway by a search that depends on its structure. Locks and latches are how the system marks a part of its state as off limits to others for a moment.

A heavyweight lock protects logical database objects and user-visible operations. Relations, rows, transactions, and advisory resources can have lock modes with compatibility rules. One mode may allow many readers. Another may exclude writers. A schema change may require stronger exclusion than a select. These locks can wait, be inspected, participate in deadlock detection, and last for a transaction or statement.

Logical claims and tiny critical sections

A latch or lightweight lock protects internal memory structures for short moments. It is not about user semantics; it is about preventing data races. A process may briefly hold a buffer content lock while reading or changing a page, or a spin-oriented primitive while adjusting shared metadata. These critical sections must be tiny. Holding an internal latch while doing disk I/O or waiting on user-level locks can stall the whole system.

Precision beats one big lock

A single global database lock is simple and safe but terrible for concurrency. Only one important thing happens at a time. A read of one table blocks an update to another. A schema change freezes unrelated sessions. Granular locks let independent work proceed, but they require careful compatibility matrices and deadlock handling. The system gets faster because it gets more precise.

Logical locks and internal latches run on different clocks. If a transaction holds a table lock until commit, that is acceptable because the lock represents a semantic claim. If it holds a buffer content latch until commit, the system is broken, and other processes needing that page may freeze. The duration and purpose of the coordination primitive must match the resource. Confusing these layers creates performance pathologies and correctness bugs.

Deadlocks are inevitable in a rich lock system. Transaction A waits for a resource held by transaction B, while B waits for a resource held by A. The database cannot solve this by waiting longer. It has to detect cycles in the wait graph and abort one participant. Deadlock detection is what a serious multi-user system requires, not a rare emergency feature. Any engine that allows multiple locks per transaction needs a story for cycles.

Coordination shows up everywhere

Row-level locking works alongside MVCC. Updating a row often requires waiting on the transaction that last changed it. Select-for-update style operations intentionally lock rows to reserve future modification rights. These locks prevent lost updates and coordinate business workflows, but they do not replace snapshot visibility. A transaction can both see an older version and wait to update the current chain, depending on timing and isolation.

Schema locks protect object definitions. A query that has planned against a table's columns and types needs that table to remain compatible while the query runs. A migration that rewrites or drops the table needs stronger access. The lock manager mediates this. Without schema coordination, the executor might read pages under a definition that no longer exists.

Internal coordination also appears in indexes. A B-tree search descending through pages must tolerate concurrent splits. Writers need to change page links and parent entries without letting readers lose the path. This requires short page-level locks, ordering rules, and sometimes right-link traversal. The user sees a simple index lookup, while the engine runs a careful sequence of structure-preserving steps.

Good coordination minimizes waiting without pretending waiting can disappear. It narrows resource scope, keeps internal critical sections short, orders acquisitions consistently where possible, and detects cycles when ordering is not enough. It also exposes enough observability for operators to understand blocked sessions. A lock wait hidden from view becomes a mystery outage.