Art of Codeartofcode
Lesson 18 of 19

17. Transactions and Locking

1
The picture to keep in your head
2
17.1 What is a transaction?
3
17.2 What does ACID mean in depth?
4
17.3 What is a lost update?
5
17.4 What can a transaction see that it shouldn't?
6
17.5 What are isolation levels?
7
17.6 What is pessimistic locking?
8
17.7 What is optimistic locking?
9
17.8 What is a deadlock?
10
17.9 What are distributed locks, and why are they tricky?
11
17.10 What is two-phase commit?
12
17.11 What is a saga?
13
17.12 How do seat holds with expiry work?
14
Memory card and where the analogy breaks
15
Practice: five levels
16
Interview questions
17
Solutions
18
Sources
19
Code lab
17. Transactions and Locking

Sources

3 min read+10 XP

Source notes

  • SQL standard isolation levels and anomalies; PostgreSQL behaviour (Read Committed default, Repeatable Read as snapshot isolation preventing phantoms, Serializable via serializable snapshot isolation with serialization failures to retry): PostgreSQL documentation, "Transaction Isolation". MySQL InnoDB default isolation level Repeatable Read: MySQL documentation.
  • Critique of isolation levels and write skew: Hal Berenson et al., "A Critique of ANSI SQL Isolation Levels", SIGMOD 1995.
  • Sagas: Hector Garcia-Molina and Kenneth Salem, "Sagas", SIGMOD 1987.
  • Two-phase commit and XA: X/Open XA specification; standard database textbooks.
  • Distributed locking, lease expiry and fencing tokens: Martin Kleppmann, "How to do distributed locking", 2016.
  • All signing-event numbers are illustrative assumptions; arithmetic computed 2026-10-08.
  • Level 3 and Level 4 code: tested 2026-10-08 (code/ch17_transactions.py, all checks pass; Level 4 uses real sqlite3 constraints).