Isolation levels
Two people press "Book" on the last seat of a tatkal train at the same instant. Both requests read the seat as available. Both write a booking. Both see a confirmation. One passenger turns up at the station with a ticket for a seat that is already taken.
Every statement in that story ran inside a transaction, and the transactions were atomic and durable. What failed was isolation: how much two transactions running at the same time are allowed to affect each other. Databases do not give you full isolation by default, because it is expensive, and most of the bugs in this chapter come from not knowing that.
Two transactions each read a balance of 1,000, then one adds 500 and the other subtracts 200, each writing back what it calculated. What is the final balance, and what should it be?
What can go wrong#
Each of these has a name, and interviewers use the names.
Dirty read. You read a change another transaction has made but not committed. If it rolls back, you acted on data that never existed.
Non-repeatable read. You read a row twice in the same transaction and get two different answers, because someone committed a change in between.
Phantom read. You run the same query twice, SELECT count(*) FROM bookings WHERE seat = 'S4-32', and new rows have appeared, because someone inserted them in between.
Lost update. Two transactions read the same value, both compute a new value from it, and both write. The second write silently overwrites the first. This is the question at the top, and it is the one that costs money.
Write skew. Two transactions each check a condition, each change a different row, and together break the condition. The classic example: a hospital rule that at least one doctor must be on call. Two doctors on call both ask to go off at once, each sees the other still on call, and both are approved.
Here is the lost update, step by step.
The four standard levels#
The SQL standard defines four levels, each preventing more of the problems above, at more cost.
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | possible | possible | possible |
| Read committed | prevented | possible | possible |
| Repeatable read | prevented | prevented | possible in the standard |
| Serializable | prevented | prevented | prevented |
Real databases differ from the textbook table in ways worth knowing. Postgres defaults to read committed. MySQL's InnoDB defaults to repeatable read, and uses locks on ranges of the index to block many phantoms. Postgres's repeatable read is actually snapshot isolation, which prevents phantoms on reads but still allows write skew. Only serializable promises the result is the same as if the transactions had run one after another, and both databases implement it with machinery that sometimes aborts a transaction and expects you to retry it.
Notice that lost updates are not in the table at all. At read committed, the default in Postgres, the example above happens exactly as shown.
How databases do this without stopping everything: MVCC#
The obvious way to isolate transactions is locking: readers wait for writers. That would make every report block every checkout. Instead, Postgres, MySQL InnoDB and most modern databases use multi-version concurrency control. An update does not overwrite a row in place. It writes a new version and keeps the old one for a while. Each transaction reads from a snapshot: the versions that were committed when its snapshot was taken.
The result is the most useful property in databases: readers never block writers, and writers never block readers. The cost is old versions piling up, which is why Postgres runs VACUUM to clean them out, and why a transaction left open for hours causes bloat.
Fixing lost updates in practice#
You rarely need serializable. You need one of these, and you need to pick deliberately.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Read committed | Fast, few aborts, and every statement sees the latest committed data. | Lost updates and write skew are possible if you read then write in app code. | The default for most workloads, combined with atomic updates or FOR UPDATE where it matters. |
| Repeatable read or snapshot | A whole transaction sees one consistent snapshot, which is ideal for reports. | Write skew is still possible, and some conflicting writes abort. | Multi query reads that must agree with each other. |
| Serializable | As if transactions ran one at a time. No anomalies to reason about. | More aborts under contention, and your code must retry them. | Complex invariants across rows, like the on call doctors, where reasoning about anomalies is too risky. |
IRCTC style booking systems get this right with conditional updates on the seat row, not with a heavier isolation level. The full design, including the version that double books, is in Design IRCTC tatkal booking.
Checkpoint#
1. Two transactions read balance 1,000, one writes 1,500 and the other writes 800. What is this called?
2. What does MVCC buy you?
3. The simplest correct way to stop two users booking the same seat?
- Postgres defaults to read committed, MySQL InnoDB to repeatable read.
- Read then write in app code loses updates at the default level.
- MVCC: readers never block writers. Old versions need cleaning up.
- Fix with atomic UPDATE, a guarded WHERE, FOR UPDATE, or a version column before reaching for serializable.
Isolation is how much concurrent transactions can affect each other. The anomalies are dirty reads, non-repeatable reads, phantoms, lost updates and write skew. The standard levels go from read uncommitted to serializable, Postgres defaults to read committed and MySQL to repeatable read, and only serializable rules out everything, at the cost of aborts you must retry. Modern databases use MVCC, keeping row versions so readers see a snapshot and never block writers. In practice the bug that bites is the lost update from reading in app code and writing back, so I would do arithmetic in one UPDATE, put conditions in the WHERE clause, use SELECT FOR UPDATE, or optimistic version checks, and only reach for serializable for invariants across rows.