Explaining the PG concurrency control mechanisms using rollercoasters
summary
Visual, timestamp-based walkthrough of PostgreSQL MVCC using rollercoaster metaphors that maps inserts, updates (as insert+delete) and deletes onto page-level rows with xmin/xmax visibility rules and transaction ID wheel behavior; shows how vacuum and freezing let the system advance without wraparound, how long-running transactions block cleanup, and how locking works (reader vs writer, deadlocks) with practical pitfalls like select for update, and contrasts read committed vs repeatable read and when serializable will abort and force retries.
AI-written summary · about
chapters (11)
- 0:00Introduction to database concurrency
- 3:44What is a transaction
- 5:28How transactions work in practice
- 10:51Transaction snapshots explained
- 17:48Transaction ID wheel and freezing
- 21:04How to use transactions properly
- 24:54Locking mechanisms in PostgreSQL
- 32:20Deadlocks and their resolution
- 32:42Isolation levels overview
- 35:16Key learnings and summary
- 37:44Q&A session
related talks
45mTransactions and Concurrency Control Patterns
50mWe have multiple concurrent versions of this title trying to understand MVCC
28mUnderstanding Transaction Isolation for App Developers
49mConcurrency Deep-Dive
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
41mConcurrency, Scalability and Transactions -- Myths and Surprises
39mAnatomy of Table-Level Locks in PostgreSQL
47mAnatomy of concurrent data access in PostgreSQL