Chapter 27 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Concurrency Control → Explicit Locking." Row and table lock modes, SELECT FOR UPDATE/FOR SHARE, advisory locks, and the lock-conflict matrix. https://www.postgresql.org/docs/current/explicit-locking.html
  • PostgreSQL Docs: "Concurrency Control → Data Consistency Checks at the Application Level." Optimistic patterns and SELECT FOR UPDATE usage.
  • PostgreSQL Docs: deadlock detection (in the locking chapter) — how PostgreSQL detects and resolves deadlocks.

Concepts (🔬 CS Student)

  • Martin Kleppmann, Designing Data-Intensive Applications — "Transactions" (lost updates, write skew, the materialization of conflicts). The best modern treatment of these races.
  • Ramakrishnan & Gehrke / Silberschatz — concurrency-control chapters. Two-phase locking, deadlock handling, serializability theory.

Practical (💻 Developer · 🏗️ DBA)

  • "Lost update" and "SELECT FOR UPDATE" tutorials — worked two-session demos (the Group B/A exercises).
  • Optimistic locking patterns (version columns; how ORMs like SQLAlchemy/Hibernate implement it) — pairs with Chapter 30.
  • "Avoiding deadlocks: consistent lock ordering" — the canonical prevention technique (Case Study 2).
  • Retry logic for deadlocks/serialization failures — how to wrap transactions so recoverable failures don't reach users.

Connection pooling (🏗️ DBA · 💻 Developer)

  • PgBouncer (https://www.pgbouncer.org/) and pgpool-II — the standard external poolers; transaction vs. session pooling modes.
  • "Why connection pooling matters in PostgreSQL" — connection cost, max_connections, and the "more app servers made the DB slower" failure.

Reference (this book)

  • Chapter 26 — Transactions/Isolation: the level above the locks (Serializable can replace some manual locking).
  • Chapter 28 — Internals: locks, MVCC dead tuples, and VACUUM under the hood.
  • Chapters 29 & 38: connection pooling in application and operations contexts.

Do, don't just read

  • Reproduce the lost update with two sessions, then fix it two ways (atomic UPDATE, FOR UPDATE).
  • Cause a deadlock (T1 locks A→B, T2 locks B→A) and watch PostgreSQL abort one; then fix with consistent ordering.
  • Add a version column to a table and implement optimistic compare-and-swap with retry.

Next: Chapter 28 — Database Internals: how PostgreSQL stores and retrieves your data.