vault

Page Pinning and Latching

13 min read

Page Pinning and Latching

Two low-level mechanisms let many threads share the buffer pool safely. Pinning is a reference count on a frame: while a thread is using a page it pins it, and a frame with a non-zero pin count is exempt from eviction — the page cannot be swapped out from under the thread using it. Latching is short-duration physical mutual exclusion: a latch guards an in-memory structure (a page’s bytes, the buffer mapping table, a B-tree node) for the brief window of a single operation, in shared (read) or exclusive (write) mode. The crucial idea a database engineer must internalize is that latches are not locks. A latch protects the physical integrity of an in-memory data structure for a few instructions and has no deadlock detector; a lock protects the logical contents of the database (a row, a table) for the duration of a transaction and is deadlock-managed. Hellerstein, Stonebraker & Hamilton draw this contrast explicitly (Architecture of a Database System), and conflating the two is one of the most common and consequential confusions in database internals.

Pinning and latching operate on the frames of The Buffer Pool; the pin count is what makes the replacement policy skip a frame; and the transactional locks that latches are contrasted with belong to Two-Phase Locking and Multiversion Concurrency Control (ghost siblings in §7 of the parent MOC).

Mental Model

flowchart TB
  subgraph PIN["PINNING — reference count vs. eviction"]
    direction LR
    T1["Thread A: pin(page 88)<br/>refcount 0 → 1"] --> P["Frame for page 88<br/>refcount = 2, INELIGIBLE"]
    T2["Thread B: pin(page 88)<br/>refcount 1 → 2"] --> P
    P -->|"both unpin → refcount 0"| E["Now evictable by<br/>the replacement policy"]
  end
  subgraph LL["LATCH vs LOCK — the canonical contrast"]
    direction TB
    L1["LATCH<br/>protects: physical structure (page bytes, buffer table, B-tree node)<br/>held: for one operation (microseconds)<br/>deadlock: avoided by ordering / no detector<br/>owner: buffer/storage manager"]
    L2["LOCK<br/>protects: logical data (row, table)<br/>held: for the whole transaction<br/>deadlock: detected (wait-for graph) or prevented<br/>owner: lock manager"]
  end

Two mechanisms, two purposes. What it shows (top): a pin is a shared reference count — several threads can pin the same page at once; the frame is protected from eviction as long as any pin remains, and becomes an eviction candidate only when the count returns to zero. What it shows (bottom): the latch/lock dichotomy along five axes — what is protected (physical vs. logical), how long it is held (operation vs. transaction), how deadlock is handled (avoided by coding discipline vs. detected/prevented by a manager), and who owns the machinery. The insight to take: pinning answers “may this frame be reused?” (a memory-management question), while latching answers “may I touch this structure right now without another thread corrupting it?” (a physical-consistency question) — and both are distinct from transactional locking, which answers “is this data logically mine to read/write under my isolation level?”

Pinning: The Reference Count Against Eviction

Before a thread reads or writes a page it must pin the frame, and it must unpin when done. The pin is a counter (refcount in PostgreSQL’s buffer descriptor, up to 18 bits per buf_internals.h; a buf_fix_count in InnoDB). Its single job is to make the replacement policy pass the frame over: the PostgreSQL clock-sweep explicitly refuses to evict a buffer whose refcount is above zero, and the README warns that “an unpinned buffer is subject to being reclaimed and reused for a different page at any instant, so touching it is unsafe” (buffer/README). Hellerstein et al. describe the same protocol abstractly: GetPage() increments the pin count and returns a frame pointer; UnpinPage() decrements it; “a frame cannot be evicted if its pin count exceeds zero” (Architecture of a Database System).

Pinning is a reference count, not a mutex: many readers can pin the same frame simultaneously, and pinning grants no exclusive access to the contents — it only guarantees the frame will not be repurposed. To actually read or write the bytes safely, a thread additionally takes a content latch (below). This separation matters: a query executor may pin a page for a relatively long time (while it iterates the tuples on it) but only hold the content latch for the instants it is actually parsing the page structure. Forgetting to unpin — a pin leak — is a serious bug: leaked pins permanently subtract usable frames from the pool and, in the worst case, prevent a heavily-referenced page from ever being evicted or resized. PostgreSQL even has a flag, BM_PIN_COUNT_WAITER, for a backend waiting to become the sole pinner of a buffer (needed for operations like VACUUM truncation that must be certain no one else is looking).

Latches: Short-Duration Physical Locks

A latch is a mutual-exclusion primitive protecting an in-memory structure for the brief span of a physical operation. In a buffer pool there are several distinct latches, and PostgreSQL’s design shows the typical layering:

  • The buffer header spinlock protects the descriptor’s metadata (refcount, flags, tag) for the handful of instructions that mutate them. It is a spinlock because it is held for nanoseconds. (In modern PostgreSQL many of these mutations are done with atomic compare-and-swap on the packed 64-bit state word, avoiding even the spinlock on the fast path.)
  • The buffer content lock is a reader/writer latch (an LWLock) protecting the page bytes, taken in shared mode to read the page and exclusive mode to modify it. The README notes it “will not work for a single backend to try to acquire multiple locks on the same buffer,” a discipline that heads off self-deadlock.
  • The buffer mapping partition locks protect the shared page table (the tag → frame hash) during lookup and insertion.

Latches come in shared/exclusive modes and are implemented as blocking OS mutexes, test-and-set spinlocks, or reader-writer latches depending on expected hold time. The defining properties, all following from their short duration, are: they are cheap (acquire/release is a few instructions), they are held for one operation not one transaction, and they have no deadlock detector. Because building a wait-for graph and running cycle detection on every microsecond-scale acquisition would cost more than the latches themselves, deadlock among latches is instead avoided by construction — threads acquire latches in a globally agreed order, or use no-wait (“try-latch, back off and retry”) protocols. CMU 15-445’s index-concurrency lecture frames the general contrast this way: latches are “kept” only during an operation and protect the physical structure, whereas locks are held across a transaction and protect logical contents (index concurrency notes).

The Canonical Latches-versus-Locks Contrast

This distinction is worth stating as a full comparison, because it recurs everywhere in storage-engine design and is the classic interview trap. Following Hellerstein et al. (Architecture of a Database System):

DimensionLatchLock
ProtectsPhysical in-memory structures — buffer frames, index nodes, the page tableLogical database contents — rows, ranges, tables
Held forThe duration of one operation (microseconds)The duration of a transaction (potentially long)
DeadlockAvoided by coding discipline (ordered acquisition / no-wait); no detectorDetected (wait-for-graph cycle detection) or prevented (wait-die / wound-wait)
ModesShared / ExclusiveShared / Exclusive plus variants (intention, update, gap…)
Owned/managed byThe buffer or storage manager (embedded in the structure)A separate lock manager with its own tables
CostLow — simple acquire/releaseHigher — conflict detection, lock-table bookkeeping, waits
Visible toOnly the engine internalsThe transaction/isolation model (drives Two-Phase Locking)

The one-line takeaway: latches keep the bytes consistent; locks keep the transactions correct. A latch on a B-tree node stops two threads from corrupting the node’s slot array while both split it; a lock on a row stops a second transaction from reading a value the first has not yet committed. They live at different layers, are held for wildly different durations, and only locks participate in the isolation guarantees of ACID isolation.

InnoDB embodies the same split in its own vocabulary. Internally it uses latches — “mutexes and rw-locks” that protect in-memory structures for short durations (surfaced via SHOW ENGINE INNODB MUTEX and the Performance Schema). Transactionally it uses locks: shared (S) and exclusive (X) row locks, table-level intention locks (IS/IX), record, gap, and next-key locks, all held to the end of the transaction and all subject to InnoDB’s deadlock detector, which aborts a victim when a wait-for cycle forms (MySQL 8.4 InnoDB locking). The redo/undo logging of those transactional locks is WAL territory; the latches are pure physical-consistency machinery beneath it.

Latch Coupling (Crabbing) on B-Tree Descent

The most instructive use of latches is latch coupling, also called crabbing — the protocol that lets many threads descend and modify a B-tree concurrently without corrupting it. The name evokes a crab walking: you never let go of one handhold until you have the next. CMU 15-445 describes the protocol as one that “releases latches for the parent if the child is deemed ‘safe,’” where a safe node is “one that will not split, merge, or redistribute when updated” (index concurrency notes). The mechanics:

  1. Latch the root, then latch the appropriate child.
  2. Once the child is latched, decide whether the parent can be released. For a search (read), the parent is always safe once you hold the child, so release it immediately — searches hold at most two latches at a time, in shared mode.
  3. For an insert, the child is safe (and the parent releasable) only if the child is not full — a non-full child cannot split, so the insert cannot propagate a split up into the parent. If the child is full, keep the parent (and possibly ancestors) latched, because a split there will have to modify the parent.
  4. For a delete, the child is safe only if it is more than half full — otherwise a merge/redistribution could ripple upward.
  5. Descend, repeating: each level, latch the child, then release ancestors that the child has proven safe.

Because writes latch nodes in exclusive mode from the top down, and always in the same order (root → leaf), crabbing is deadlock-free by construction — the ordered-acquisition discipline that substitutes for a latch deadlock detector. Its weakness is contention at the top: every writer must exclusively latch the root, serializing the tree’s throughput. The standard optimization, optimistic crabbing (optimistic lock coupling), assumes splits are rare: descend taking only shared latches, and if the leaf turns out to be unsafe (needs a split), release everything and restart the descent taking exclusive latches pessimistically. Since the vast majority of inserts do not split a node, the common path never exclusively latches the root, dramatically reducing contention.

Uncertain

Verify against the primary lecture PDF: the exact “safe node” thresholds and the precise optimistic-crabbing restart protocol. Reason: the CMU 15-445 index-concurrency PDF could not be text-extracted during this task, so these details rest on the search summary of that lecture plus standard textbook knowledge, not a direct read of the source. To resolve: read the lecture notes in full. uncertain

Observability

  • PostgreSQL: wait events surface latch contention. pg_stat_activity.wait_event_type = 'LWLock' with wait_event like BufferMapping, BufferContent, or WALWrite shows threads blocked on buffer latches; wait_event_type = 'BufferPin' shows a backend waiting to become sole pinner of a buffer.
  • InnoDB: SHOW ENGINE INNODB MUTEX and the Performance Schema events_waits_* tables report latch (mutex/rw-lock) waits; SHOW ENGINE INNODB STATUS reports semaphore waits under SEMAPHORES, and long semaphore waits are a red flag for latch contention or a stuck thread.

Failure Modes

  • Hot-page latch contention. A single page touched by every transaction — the B-tree root, a monotonically increasing primary-key leaf (right-edge insert hotspot), a global counter/sequence page — becomes a latch bottleneck: every writer must exclusively latch it, serializing them. Symptoms are high CPU in spin loops and long semaphore/LWLock waits. Fixes include optimistic crabbing, hash-partitioned keys to spread inserts, or sharding the counter.
  • Latch convoy. If a thread is descheduled while holding a hot latch, every waiter piles up behind it and throughput collapses until it is rescheduled — an amplified version of contention that scheduling jitter triggers.
  • Pin leaks. A code path that pins without unpinning (an error path that skips ReleaseBuffer) permanently removes a frame from circulation and can pin a page forever, thwarting eviction and buffer-pool resize.
  • Mistaking a latch for a lock (or vice versa). Expecting deadlock detection on latches (there is none — a latch cycle just hangs) or holding a latch for a transaction’s duration (which would destroy concurrency and can itself deadlock undetectably) are classic, hard-to-debug errors. Latches must be released promptly; anything held across user think-time or an fsync must be a lock, not a latch.

Alternatives and When to Choose Them

Traditional pessimistic latch coupling is simple and correct but contends at the tree top. Optimistic Lock Coupling (OLC) — descend with shared latches, validate a version counter, restart on conflict — is the modern default in high-core-count engines because it keeps the common (no-split) path nearly contention-free. Lock-free / latch-free structures such as Microsoft’s Bw-tree push further: nodes are updated by atomic compare-and-swap on a mapping table with delta records, eliminating latches entirely at the cost of substantial complexity and read-side delta consolidation — worthwhile for in-memory or flash-optimized engines where latch contention would otherwise dominate. For most disk-based B-tree engines, pessimistic crabbing with an optimistic fast path is the pragmatic sweet spot: cheap, deadlock-free by ordering, and contention-tolerable for typical workloads.

Production Notes

Latch contention is a scaling problem, not a correctness problem, and it tends to appear suddenly when a workload’s concurrency crosses a threshold — you add cores or connections expecting linear speedup and instead throughput plateaus or dips as threads spin on a shared latch. The diagnostic reflex is to look at wait events (LWLock/BufferContent in PostgreSQL, SEMAPHORES and mutex waits in InnoDB) rather than at CPU utilization alone, because a latch convoy shows up as busy CPU doing no useful work. Two structural culprits recur in the field: right-edge insert hotspots on auto-increment/serial primary keys (every insert latches the same rightmost leaf and the root), mitigated by UUID/hash keys or by InnoDB’s insert buffering; and buffer-mapping-partition contention on many-core machines, mitigated by more buffer-pool instances (InnoDB) or a finer-partitioned mapping table. The deep lesson is architectural: correctness comes from locks and WAL, but scalability is usually won or lost in the latching layer.

See Also

Graph View