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.6 What is pessimistic locking?
3 min read+10 XP
Locking
On the train: The clerk puts a hand on seat 14 the moment you ask for it. Anyone else who wants seat 14 waits until you've paid or walked away.
In the system: Pessimistic locking locks the rows you intend to change before you change them, so nobody else can change them meanwhile:
sql
Figure: lock first when conflicts are common; check last when they're rare.
- Guarantees no conflict at the end: nobody else could touch the row.
- Costs: others wait while you hold the lock, so hold it briefly; and locks can deadlock (17.8).
- Watch out for a hot single row. If every booking locks one
eventsrow to decrementseats_left, and each transaction holds it for about 50 ms, the whole event can only sell about 20 tickets a second, however many servers you have. Lock per seat instead, or use an atomic update.
Memory code: "Hand on the seat before you sit."