MVCC and isolation: free-recall review
Free-recall prompts across the MVCC unit — visibility math, isolation anomalies, HOT, oldest xmin and bloat, SSI. Answer from memory first, then reveal.
Retrieval beats re-reading. For each prompt, say or write a full answer from memory before you open the model answer — the effort of recall is what makes the mechanism stick when you need it at 3 AM.
Reconstruct the unit’s spine without looking back: the visibility rule, what each isolation level prevents and allows, the difference between write skew and lost update, what pins the oldest xmin, and how SSI catches write skew.
- 01What is a Postgres snapshot (three numbers) and what is the visibility rule applied to each tuple?
- 02READ COMMITTED vs REPEATABLE READ: when is the snapshot taken, and what anomaly distinguishes them?
- 03Distinguish a lost update from write skew, and name the fix for each.
- 04What is the global oldest xmin, and why does a long transaction or orphan replication slot cause unbounded bloat through it?
- 05What two conditions make an UPDATE a HOT update, and why does it matter for write cost?
- 06How does SSI detect write skew, what does it abort with, and what is the expected false-positive rate?
If you reconstructed each answer from memory, you hold the unit’s spine: a snapshot plus a tuple header is the entire visibility mechanism; the isolation level you pick decides which anomalies you own — RC leaves lost updates to the app, REPEATABLE READ stabilises reads and catches concurrent row updates but allows write skew, SERIALIZABLE adds SSI to catch write-skew cycles via predicate-lock dependencies; HOT updates cut write cost when no indexed column changes and the page has room; and the global oldest xmin is the lever behind almost every bloat incident — a long transaction or orphan slot pins it and autovacuum goes quietly futile.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.