Correct code can fail when two copies run together#
A single-threaded reading of each request looks correct. The interleaving violates the invariant that one seat can be sold once.
Name the observable failure#
| Anomaly | Concrete behavior |
|---|---|
| Dirty read | Read data another transaction has not committed. |
| Non-repeatable read | Read one row twice and observe different committed values. |
| Phantom read | Repeat a predicate query and observe new matching rows. |
| Lost update | Two writers derive updates from old state and one overwrites the other. |
Actual behavior and isolation-level names vary by database. State the forbidden outcome and verify the chosen system's guarantee rather than relying only on a label.
Optimistic and pessimistic coordination#
UPDATE seats
SET status = 'booked', version = 8
WHERE id = 42 AND version = 7 AND status = 'available';Optimistic concurrency assumes conflicts are uncommon and makes the update conditional; zero affected rows means retry or report a conflict. Pessimistic concurrency locks the resource before changing it, reducing conflicting work but introducing waiting, deadlocks, and contention.
Serializable transactions can provide behavior equivalent to some serial ordering, with system-specific retry and performance costs. A single atomic conditional update may be the simpler answer for one seat row.
Use these prompts to extend the design. The useful answer is a reasoned change, not a memorized sentence.
- What if 10,000 users want one seat?
- How is a failed conditional update surfaced?
- What does the lock cover and for how long?
- Which operations can safely occur outside the transaction?
Related practice problems
Use the manifest-backed prompts below to apply this lesson in a complete design.
- Transaction isolationPostgreSQL Documentation