Module 5 · Transactions and concurrency control

Episode 38 MVCC

10 min · SQL · Postgres

Title card of Episode 38: MVCC

▶ This video is coming to YouTube soon.

How can one transaction read a row while another is changing it, with nobody waiting? This episode animates Postgres's MVCC: every update writes a new row version with xmin and xmax, a snapshot picks exactly one version, and VACUUM removes the rest. Then pageinspect shows both versions in a real page, and a second session keeps reading the old balance after the update commits.

Chapters

  1. 0:00Intro
  2. 0:13Same row, same moment, no waiting
  3. 0:34One copy: wait, or read dirt
  4. 1:16Every update writes a new version
  5. 2:15A snapshot picks exactly one version
  6. 3:20The code: the visibility rule
  7. 4:02Readers never wait; writers still queue
  8. 4:53Garbage, the horizon and VACUUM
  9. 5:48Versions in the heap, or in an undo log
  10. 6:35Proof: two versions in one page
  11. 7:43Proof: a snapshot ignores a commit
  12. 8:41Trade-offs
  13. 9:08Recap
  14. 9:35Next: Isolation Levels in Real Life