Module 5 · Transactions and concurrency control

Episode 35 Two-Phase Locking

10 min · SQL · Postgres

Title card of Episode 35: Two-Phase Locking

▶ This video is coming to YouTube soon.

Why locking every row still loses money, and how two-phase locking fixes it: shared and exclusive locks, the growing and shrinking phases, why lock points make every schedule conflict-serializable, cascading aborts and strict 2PL, and IS/IX/SIX intention locks. Then the real thing in Postgres: pg_locks, pgrowlocks, FOR SHARE vs FOR UPDATE, and LOCK TABLE modes blocking each other.

Chapters

  1. 0:00Intro
  2. 0:15Lock everything, still lose money
  3. 0:36Locking each operation isn't enough
  4. 1:23Two-phase locking fixes the lost update
  5. 2:12Grow, lock point, shrink
  6. 3:04Why 2PL never makes a cycle
  7. 3:32Cascading aborts, and strict 2PL
  8. 4:21Intention locks: lock at the right size
  9. 5:14The full compatibility matrix
  10. 5:46The code: a lock request
  11. 6:15Postgres's table locks, and their textbook cousins
  12. 6:50Proof: what a transaction holds
  13. 7:46Proof: FOR SHARE shares, FOR UPDATE waits
  14. 8:40Proof: table lock modes conflict
  15. 9:38Recap
  16. 10:05Next: Deadlocks