Module 5 · Transactions and concurrency control

Episode 36 Deadlocks

8 min · SQL · Postgres

Title card of Episode 36: Deadlocks

▶ This video is coming to YouTube soon.

Two bank transfers lock rows in opposite orders and wait for each other forever. This episode builds the waits-for graph, shows how Postgres detects the cycle after deadlock_timeout and aborts one transaction, animates wait-die and wound-wait, and fixes it for good with consistent lock ordering. Proof: a real deadlock in Postgres with its DETAIL lines and SQLSTATE 40P01, then the ordered version committing.

Chapters

  1. 0:00Intro
  2. 0:15Two transfers, opposite directions
  3. 0:37A cycle in the waits-for graph
  4. 1:31A deadlock needs all four
  5. 1:57Detection: search after a timeout
  6. 2:43Wait-die: the young die
  7. 3:29Wound-wait: the old wound
  8. 4:06Four ways to deal with deadlocks
  9. 4:33The fix: lock rows in a consistent order
  10. 5:10The code: lock in id order, retry on 40P01
  11. 5:35Proof: a real deadlock, then none
  12. 7:11Recap
  13. 7:38Next: Timestamp Ordering and OCC