Two successful transactions, one impossible result
Two customers receive a confirmation for seat 14C. Both requests ran inside database transactions. Neither transaction failed.
In this hypothetical booking service, the rule is simple: a seat can have at most one owner. Your first task is to find how two individually reasonable operations broke that rule.
Read the evidence before changing the isolation level
The first implementation reads available, checks it in application code, and writes the buyer's name. Both transactions read before either writes.
| Step | Buyer A | Buyer B | Stored owner |
|---|---|---|---|
| 1 | Reads no owner | none | |
| 2 | Reads no owner | none | |
| 3 | Writes A and commits | A | |
| 4 | Writes B and commits | B |
The final row has only one owner. Why is the system still wrong? Would wrapping these statements in BEGIN and COMMIT fix it?
The smallest useful change moves the decision into the same atomic operation as the write.
sql-- PostgreSQL example; seats(flight_id, seat_no) is a primary key. UPDATE seats SET owner_id = :buyer WHERE flight_id = :flight AND seat_no = '14C' AND owner_id IS NULL RETURNING seat_no;
At PostgreSQL Read Committed, a competing updater waits, then rechecks its predicate against the updated row. Only the winner receives a row. The application confirms a booking only after that transaction commits. Zero returned rows means this attempt did not acquire the seat. A lost commit response is a different problem: the outcome is unknown, which we revisit in lesson 18.
This mechanism is conditional mutation. It eliminates the time between āis this allowed?ā and āmake it happen.ā A unique constraint on an active reservation can enforce the same invariant in a different schema. A process mutex cannot protect a second application instance.
The next rule does not fit in one row
The booking team adds a support rota. At least one of two engineers must remain on call. Both are currently available. Each transaction counts two on-call engineers and marks its own engineer off call.
Each update targets a different row. Both transactions read a consistent snapshot. Can both commit without violating that snapshot's internal consistency? What happens to the business rule?
The one-row fix taught us where to put the decision. It did not prove that every invariant fits one row. Here are three ways to make the whole decision conflict:
| Design | Why it works | Cost or boundary |
|---|---|---|
| Serializable transactions | Successful transactions behave like some serial ordering | Retry the entire aborted transaction; contention can cause repeated aborts |
| Lock a shared rota row, then inspect/update the members | Every writer participates in one serialization point | All relevant writers must follow the protocol |
| Reshape data so the invariant is enforceable by a database constraint | The engine rejects an invalid state transition | Only possible for constraints the schema and engine can express |
Do not assume SELECT ... FOR UPDATE on an empty result locks a future reservation. For that case, use a unique/exclusion constraint, a stable parent row, or suitable serializable behavior. Locking the rows that happen to exist is not necessarily locking the predicate.
Stable reads have a price
MVCC lets a normal read see a version appropriate to its snapshot while another transaction creates newer versions. But visibility also depends on which transactions committed or were in progress; it is not simply āchoose the highest transaction ID below mine.ā
A long-lived snapshot can pin old versions. Space reclamation falls behind, scans visit more dead versions, and the system slows even though the application is mostly reading. Watch transaction age, lock waits, dead tuples or undo growth, and retry counts. Bound transaction duration before blaming storage hardware.
The anomaly names are useful only when you can produce the schedule:
| Observation | Name | What to investigate |
|---|---|---|
| Read a value whose writer later aborts | Dirty read | Visibility of uncommitted changes |
| Read the same row twice and see a committed change | Non-repeatable read | Statement versus transaction snapshot |
| Repeat a predicate query and see a different matching set | Phantom | Predicate/snapshot protection |
| Two stale read-modify-writes overwrite each other | Lost update | Atomic update or conflict detection |
| Disjoint writes break a shared rule | Write skew | Predicate-level serialization |
The exact protection behind āRepeatable Readā depends on the engine. PostgreSQL prevents phantoms at that level but still allows serialization anomalies; Serializable adds dependency checking and may abort transactions. These are documented guarantees, not a universal isolation hierarchy. PostgreSQL transaction isolation.
Make a retry safe before making it automatic
An aborted serializable transaction must restart from its reads. Reusing the old decision and retrying only the final statement preserves the mistake. Keep external effects such as sending a receipt outside the retriable transaction; a database rollback cannot unsend a message.
For pessimistic locking, acquire multiple rows in a stable order to reduce deadlocks. Still handle deadlock victims: sorted locking is a protocol, not proof that every other writer follows it. Keep large deletes bounded; engines differ in lock escalation, and even without escalation a large transaction can create substantial log and cleanup work.
Mental model: a transaction boundary is a container. The invariant tells you what must be decided together; isolation and constraints determine whether the container protects it.
Transfer: the last two units
A warehouse has two units. Three clients each execute UPDATE stock SET units = units - 1 WHERE units > 0 RETURNING units. Every successful caller then sends a shipment request over HTTP.
Which failure has been solved? Which remains if one HTTP call times out? Add a second warehouse whose stock participates in the same order: what changes?
Source trail: 21-database-isolation-levels-acid-and-concurrency-anomalies. See the source map for consolidation and reference-retained material.