Module 5 · Transactions and concurrency control

Episode 40 Lab: Two Users Bought the Last Ticket

8 min · SQL · Postgres

Title card of Episode 40: Lab: Two Users Bought the Last Ticket

▶ This video is coming to YouTube soon.

One ticket left, two buyers, two sales. This lab reproduces the bug in two real Postgres sessions, replays it in a simulator to show the lost update at READ COMMITTED, then fixes it three ways (SELECT ... FOR UPDATE, a single atomic UPDATE, SERIALIZABLE with a retry), proves each fix in Postgres and measures them with six buyers.

Chapters

  1. 0:00Intro
  2. 0:13The symptom
  3. 0:34Reproduce: two checkouts, one ticket
  4. 1:15Diagnose: a lost update
  5. 2:18Why READ COMMITTED allowed it
  6. 2:45Three ways to tie the decision to the write
  7. 3:22Fix 1: SELECT … FOR UPDATE
  8. 3:48Fix 2: one atomic UPDATE
  9. 4:13Half a fix, and a safety net
  10. 4:57Fix 3: SERIALIZABLE and a retry
  11. 5:28The code: the atomic checkout
  12. 6:00Measure: six buyers, two tickets
  13. 6:51Before and after
  14. 7:23Recap
  15. 7:50Next: Write-Ahead Logging