Module 5 · Transactions and concurrency control

Episode 33 The Anomalies

10 min · SQL · Postgres

Title card of Episode 33: The Anomalies

▶ This video is coming to YouTube soon.

The five classic anomalies of concurrent transactions, each built as a two-transaction timeline and then reproduced in real Postgres: READ UNCOMMITTED that never reads dirty data, a non-repeatable read and a phantom at READ COMMITTED, a lost update from read-modify-write, and write skew (two doctors on call) at REPEATABLE READ, stopped only by SERIALIZABLE.

Chapters

  1. 0:00Intro
  2. 0:14Two doctors, one rule
  3. 0:38Dirty read: a value nobody committed
  4. 1:14Proof: Postgres has no dirty reads
  5. 1:45Non-repeatable read: same row, new value
  6. 2:16Proof: READ COMMITTED vs. a snapshot
  7. 3:02Phantom: same query, a new row
  8. 3:32Proof: a phantom, then none
  9. 4:07Lost update: two writes, one survives
  10. 4:54Proof: a lost update, then a refusal
  11. 6:09The code: read-modify-write and its fixes
  12. 6:41Write skew: two good choices, one bad result
  13. 7:19Proof: write skew, then SERIALIZABLE
  14. 8:34Which anomalies each level allows, in Postgres
  15. 9:03Recap
  16. 9:27Next: Serializability