Chapter 27 — Key Takeaways

The big idea

MVCC keeps readers and writers apart; concurrency control handles writers vs. writers on the same row. The canonical bug is the lost update (read-modify-write race): two transactions read the same value, each computes a new one, and one write overwrites the other.

Locks

  • Row-level (fine-grained, preferred) vs. table-level (coarse, mostly DDL).
  • Shared (read-with-intent) vs. exclusive (modify) modes; conflicting requests wait.
  • Principle: lock as little as possible, as briefly as possible.

Preventing the lost update

  • Do arithmetic in one SQL statement (SET n = n - 1 WHERE ... AND n > 0) — atomic on the row; check the affected-row count. (Often the cleanest fix.)
  • Pessimistic: SELECT ... FOR UPDATE locks the row up front; others wait. Simple, correct, blocks on hot rows.
  • Optimistic: a version column + compare-and-swap UPDATE (only if version unchanged) + retry on 0 rows. No locks held; best for low contention.
  • (Case Study 1: the oversold last ticket — atomicity wasn't enough; needed FOR UPDATE or a guarded atomic UPDATE.)

Deadlocks

  • A cycle of transactions each waiting for a lock the other holds.
  • PostgreSQL detects and aborts one (so it never hangs) → the app must retry.
  • Prevent with consistent lock ordering (e.g., always lock the lower id first) so cycles can't form. Keep transactions short. (Case Study 2: intermittent deadlocks from request-order locking → fixed by canonical order + retry.)

Other tools

  • Advisory locks (pg_advisory_lock(n)) — app-defined coordination of non-row resources (a named mutex / leader election).
  • Connection pooling (PgBouncer) — caps and reuses the server's bounded connections; prevents connection exhaustion at scale ("more app servers made the DB slower").

Designing for concurrency

Arithmetic in SQL · FOR UPDATE for read-then-decide · version columns for low contention · consistent lock order · retry deadlocks/serialization failures · short transactions · pooled connections · right isolation level (Ch. 26).

You can now…

  • ☐ Recognize and prevent lost updates (atomic SQL / FOR UPDATE / optimistic).
  • ☐ Use row locks and choose pessimistic vs. optimistic by contention.
  • ☐ Cause, prevent (consistent ordering), and retry deadlocks.
  • ☐ Use advisory locks and explain why pooling matters.

Looking ahead

Chapter 28 — Database Internals. How PostgreSQL stores/retrieves data: pages, the heap, the buffer pool, the WAL (durability), MVCC dead tuples & VACUUM, checkpoints — the machinery that explains why all of Part IV works.

One sentence to carry forward: Two writers on one row need concurrency control — do the arithmetic in SQL or lock with FOR UPDATE, lock in a consistent order to avoid deadlocks, retry the recoverable failures, and pool your connections.