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.2 What does ACID mean in depth?
3 min read+10 XP
On the train: The railway promises four things about a ticket. It's complete or not issued. The rules hold (no seat sold twice, no negative fare). Two clerks booking at once don't trip over each other. And once printed, it survives a power cut.
In the system: Chapter 8 introduced ACID in one line each. Here's what each really involves:
| Promise | How databases deliver it | |
|---|---|---|
| Atomicity | All of the transaction or none of it | An undo or write-ahead log that can roll back partial work |
| Consistency | Constraints hold before and after | Primary keys, foreign keys, UNIQUE, CHECK; plus your application's rules |
| Isolation | Concurrent transactions don't see each other's half-done work, to a chosen degree | Locks and/or multiple versions of rows (MVCC), set by the isolation level (17.5) |
| Durability | Committed means saved, even through a crash | Writing the log to disk before confirming the commit; replicas (Chapter 14) |
Isolation is the one that surprises people: most databases don't give full isolation by default. PostgreSQL defaults to Read Committed and MySQL's InnoDB to Repeatable Read, and neither stops every race. That's why the rest of this block matters.
Memory code: "Whole, legal, private, permanent." Atomic, Consistent, Isolated, Durable.