Module 5 · Transactions and concurrency control

Episode 32 ACID and Schedules

9 min · SQL · Postgres

Title card of Episode 32: ACID and Schedules

▶ This video is coming to YouTube soon.

What does a transaction really promise? Atomicity, consistency, isolation and durability, each shown with one picture, then two bank transfers interleaved on a timeline: serial schedules, one that loses $30 and one that's fine. Every claim is checked in Postgres: a ROLLBACK, a CHECK constraint, and two sessions where the second UPDATE waits for the first's row lock.

Chapters

  1. 0:00Intro
  2. 0:15Two transfers at once
  3. 0:33Atomicity: all of it, or none of it
  4. 1:24Consistency: the rules hold at every commit
  5. 1:59Isolation: as if each ran alone
  6. 2:42Durability: committed means kept
  7. 3:27Serial schedules: one at a time
  8. 4:12An interleaving that loses $30
  9. 4:52An interleaving that's fine
  10. 5:24Four schedules, two transfers
  11. 5:46The code: a transfer as one transaction
  12. 6:16Proof: rollback and a CHECK in Postgres
  13. 7:24Proof: two transfers, two sessions
  14. 8:24Who keeps each promise
  15. 8:50Recap
  16. 9:15Next: The Anomalies