17. Transactions and Locking
1
The picture to keep in your head2
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 breaks15
Practice: five levels16
Interview questions17
Solutions18
Sources19
Code lab17. Transactions and Locking
17.3 What is a lost update?
3 min read+10 XP
On the train: Two clerks each look at the board: "100 seats left". Each sells one and writes "99" on the board. Two seats were sold, but the board only went down by one.
In the system: A lost update happens when two transactions read the same value, compute a new value from it, and both write. The second write overwrites the first, and one change is lost.
Figure: read-modify-write in application code loses updates. Let the database do it in one step.
python
Fixes, from simplest to most general:
- Atomic update in the database:
UPDATE events SET seats_left = seats_left - 1 WHERE id = 1 AND seats_left > 0. The database reads and writes in one step, and theWHEREstops it going below zero. In the chapter's code, 130 booking attempts for 100 seats sell exactly 100. - Lock the row first (17.6).
- Check a version when writing (17.7).
- A stronger isolation level that detects the conflict (17.5).
Memory code: "Don't read, think and write back; let the database subtract."