Module 5 · Transactions and concurrency control

Episode 37 Timestamp Ordering and OCC

9 min · SQL · Postgres

Title card of Episode 37: Timestamp Ordering and OCC

▶ This video is coming to YouTube soon.

How do you keep transactions serializable without waiting for locks? This episode animates basic timestamp ordering (read and write timestamps, too-late reads and writes, restarts, the Thomas write rule) and optimistic concurrency control with backward validation, then shows Postgres's REPEATABLE READ failing the second updater with 40001 and an application-level version column doing OCC by hand.

Chapters

  1. 0:00Intro
  2. 0:12Three ways to stay serializable
  3. 0:35No control at all: a lost update
  4. 1:06Basic timestamp ordering
  5. 1:44A read that arrives too late
  6. 2:40Writes too late, and the Thomas write rule
  7. 3:26What timestamps cost
  8. 3:52Optimistic concurrency control
  9. 4:18Validation catches the stale read
  10. 5:19The code: backward validation
  11. 5:49Postgres: the first updater wins
  12. 6:37OCC in your app: a version column
  13. 7:22Trade-offs
  14. 7:55Recap
  15. 8:25Next: MVCC