Concurrency still needs traffic rules

A bookstore report can read an older tuple version while a clerk writes a newer one, but MVCC does not make every conflict disappear. The report's table cannot be dropped midway. Two processes cannot edit the same shared buffer metadata simultaneously. An index search cannot observe half a page split. These conflicts concern different resources and lifetimes, so one universal lock would be the wrong abstraction.

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

PostgreSQL uses LWLocks, spinlocks, and buffer content locks to protect internal structures for short moments. These are sometimes described generically as latches, but the concrete primitive matters. They prevent data races rather than express user semantics. Their critical sections must be tiny; holding one across disk I/O or a user-level wait can stall unrelated backends.

Trace two kinds of coordination

A query acquires a relation lock compatible with ordinary readers and writers, preserving the logical table definition for the statement. While reading a heap page, it pins the buffer and briefly acquires the page's content lock to inspect consistent bytes. The relation lock may legitimately last through the transaction; the content lock is released as soon as the page-local operation finishes.

The invariant is a match between purpose and lifetime. Logical claims may wait, appear in lock views, and participate in deadlock detection. Internal synchronization protects only enough shared state to make one operation atomic and must not inherit transaction duration. Calling both simply 'locks' hides the distinction that prevents either corrupted memory or needless system-wide stalls.

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.

Choose coordination by four dimensions

Name the resource, the conflicting operations, the longest legitimate duration, and the behavior while waiting. A table definition and a buffer-header field produce very different answers. This test usually reveals whether the design needs a transaction-level lock, an LWLock, a spinlock, an atomic operation, or no shared mutation at all. The primitive follows the invariant; its familiar name should not choose the architecture.