Art of Codeartofcode
Lesson 5 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

17.4 What can a transaction see that it shouldn't?

3 min read+10 XP

On the train: Three ways a clerk's view of the chart can mislead them while another clerk is working: seeing a booking that's not final yet, seeing seat counts change while they're still adding them up, or seeing new bookings appear in a list they just checked.

In the system: These are the classic read anomalies. Each isolation level (17.5) is defined by which of them it prevents.

dirty read, non-repeatable read and phantom read, each in four lines.

Figure: three things a transaction can see that it shouldn't.

17.4.1 Dirty read

On the train: A clerk sees seat 14 marked "sold" by a colleague who then cancels before finishing. The first clerk turned someone away for nothing.

In the system: Reading data another transaction has written but not committed. If it rolls back, you acted on a value that never really existed (a price, a stock level). Almost every database prevents this by default.

17.4.2 Non-repeatable read

On the train: A clerk counts free seats in row A (5), turns away, counts again (3). Same row, two answers, inside one task.

In the system: Reading the same row twice in one transaction and getting different values, because another transaction committed a change in between. It breaks reports and calculations that read the same data more than once.

17.4.3 Phantom read

On the train: A clerk checks "any bookings for seat 14?" (none), then another clerk books it, and the same check now shows one. The set of matching rows changed.

In the system: Running the same query twice and getting different rows, because another transaction inserted or deleted matching rows. This is exactly the double-booking pattern: "check there's no booking for seat 14, then insert one" (the Level 4 bug).

A fourth one to know by name: write skew, where two transactions each read overlapping data, make decisions that are fine individually, and write different rows, breaking a rule together (two doctors each going off call because "the other one is still on call").

Memory code: "Unfinished, changed, or newly appeared: three ways to read a lie."