Module 5 · Transactions and concurrency control

Episode 39 Isolation Levels in Real Life

9 min · SQL · Postgres

Title card of Episode 39: Isolation Levels in Real Life

▶ This video is coming to YouTube soon.

The SQL standard has four isolation levels; Postgres has three behaviours. This episode animates READ COMMITTED's snapshot per statement, REPEATABLE READ's single snapshot, the write skew it lets through, and how SERIALIZABLE (SSI) tracks read/write dependencies to abort it, then proves each one in two real Postgres sessions, down to SQLSTATE 40001.

Chapters

  1. 0:00Intro
  2. 0:12Four names, three behaviours
  3. 0:33The standard: levels by anomaly
  4. 1:10READ COMMITTED: a snapshot per statement
  5. 1:39REPEATABLE READ: one snapshot, first updater wins
  6. 2:17Write skew: what snapshots can't see
  7. 2:57SERIALIZABLE: SSI finds the pivot
  8. 4:10What Postgres actually gives you
  9. 4:48Proof: READ UNCOMMITTED reads committed data
  10. 5:06Proof: READ COMMITTED sees new commits
  11. 5:30Proof: REPEATABLE READ has no phantoms
  12. 6:03Proof: both doctors go home
  13. 6:32Proof: SERIALIZABLE aborts the pivot
  14. 7:09The code: retry on 40001
  15. 7:38Choosing a level
  16. 8:03Recap
  17. 8:30Next: Lab: Two Users Bought the Last Ticket