Caching & Databases · lesson 8 of 8 · about 30 min
What a database promises when many things happen at once: atomicity through the log, and then the part everyone gets wrong, isolation. Replay dirty reads, lost updates, phantoms and write skew against four isolation levels in a real tiny engine, read the grid of what each level allows, see what stronger isolation costs in retries, and learn the cheap fixes. Tap any for a plain-language card.
What a transaction is
Atomicity and durability come from the : before touching any data pages the database writes what it is about to do to a log on disk. After a crash it replays the committed entries and discards the uncommitted ones. Without a transaction the crash leaves the debit applied and the credit missing, and 100 has vanished.
Crash between the two steps with no transaction: the total drops from 1,000 to 900. Wrap it in a transaction and the crash changes nothing.
Crash after the debit, before the credit: A is 400 and B is 500, so 100 has disappeared. Two separate statements give no all-or-nothing guarantee.
A deterministic illustration with starting balances 500 and 500 and a transfer of 100.
Isolation
This is a real (tiny) transaction engine. Choose an anomaly and an isolation level, and it replays the same schedule of steps. Read the timeline, then the verdict. The levels are, from weakest to strongest: read uncommitted, read committed (the PostgreSQL default), repeatable read (snapshot isolation) and serializable.
Pick “Lost update” and step through the levels: lost at read committed, refused at repeatable read. Then try “Write skew”, which only serializable stops.
Two requests each add to a counter: read 100, then write read + 10 and read + 20.
Read committed: Final value 120: one of the two additions (+10) was silently lost, 130 was expected.
The engine implements read uncommitted (reads see other transactions' tentative writes), read committed (latest committed value), snapshot isolation (a fixed snapshot plus first-committer-wins) and a serializable check that aborts a writing transaction whose reads changed under it.
The grid
Three things to notice. First, the ANSI names are a rough guide, not a precise one: real databases differ. PostgreSQL's repeatable read is , which also prevents phantoms but not write skew; MySQL's InnoDB default is repeatable read with different details. Second, write skew is the quiet one that survives snapshot isolation. Third, serializable is the only level that makes the whole table safe, at the price of retries.
Scan the write-skew row: it is the last anomaly to disappear, and only at serializable.
| Scenario | Read uncommitted | Read committed | Repeatable read | Serializable |
|---|---|---|---|---|
| Dirty read | ANOMALY | safe | safe | safe |
| Non-repeatable read | ANOMALY | ANOMALY | safe | safe |
| Lost update | ANOMALY | ANOMALY | aborts one | aborts one |
| Write skew | ANOMALY | ANOMALY | ANOMALY | aborts one |
| Phantom row | ANOMALY | ANOMALY | safe | safe |
Read each row from left to right: every stronger level removes anomalies the weaker one allowed. Defaults in practice: PostgreSQL, Oracle and SQL Server use read committed, MySQL InnoDB uses repeatable read. Neither is serializable by default, so write skew and lost updates are your code's problem unless you ask for more.
Generated by running the five schedules at all four levels. “aborts one” = the database aborted a transaction with a conflict error.
The price
The simulation runs groups of concurrent transactions that each read two rows and write one, with some accesses concentrated on a few hot rows, and counts how many would be aborted at each level. Read committed never aborts (it just allows anomalies); snapshot isolation aborts on write-write conflicts; serializable also aborts on read-write conflicts.
Concentrate half of the traffic on five hot rows: serializable aborts a third of transactions. Spread it out and the aborts nearly disappear.
With 10 transactions overlapping and 30% of accesses landing on 5 hot rows, 17.9% of transactions must be retried under serializable against 6.4% under snapshot isolation. Every retry is wasted work and extra latency, and retries can collide again.
3,000 seeded rounds. A transaction aborts under snapshot isolation if a concurrent committed transaction wrote a row it wrote; under serializable also if one wrote a row it read. 1,000 ordinary rows plus the hot ones.
Fixing it in code
The same options appear everywhere: compare-and-set in key-value stores, conditional writes in DynamoDB, ETags in HTTP. Choose one and watch what the two concurrent additions (+10 and +20 on a counter of 100) produce.
Start with the bug (120), then try each of the three fixes (130).
Read, compute in the app, write back (the bug): final value 120. Both transactions read 100 before either wrote, so the later write replaces the earlier one: 120 instead of 130.
Deterministic outcome of two concurrent additions under read committed; the explanations follow standard PostgreSQL behaviour.
Which fix?
Use the weakest tool that is actually safe for the problem. Each answer is worth 20 XP.
Ask: can the change be one statement? is it one row or a rule across rows? is a stale read acceptable?
Increment a page-view counter from many servers.
Tip: press 1 to 4 to answer, Enter for the next one.
Who lives by this
Four published sources on isolation.
'A Critique of ANSI SQL Isolation Levels'
The paper showed that the SQL standard's definitions of isolation levels were ambiguous and incomplete, and defined snapshot isolation and anomalies such as write skew that the standard's phenomena did not capture.
read committed, plus true serializable
PostgreSQL's default is read committed; its repeatable read is snapshot isolation; and since version 9.1 its serializable level uses serializable snapshot isolation, which detects dangerous patterns of read-write dependencies and aborts a transaction, requiring applications to retry.
repeatable read with MVCC
InnoDB defaults to repeatable read, using multi-version concurrency control for consistent reads and gap locks to limit phantoms for locking reads, so the exact behaviour differs from PostgreSQL's snapshot isolation even though the level has the same name.
external consistency
Spanner provides serializable transactions with external consistency, meaning that if one transaction commits before another starts, the timestamps reflect that order, using TrueTime commit wait to achieve it across data centres.
Interview angle
Six questions, each worth 20 XP. Isolation levels are among the most common database interview topics.
Name the anomaly, name the level that prevents it, and mention that stronger levels mean retries.
What does atomicity guarantee?
Tip: press 1 to 3 to answer, Enter for the next one.
Break it
The same shape hides in many real rules: two bookings for one room, two withdrawals from joint accounts that together overdraw, two usernames claimed at once. Fixes: use serializable isolation and be ready to retry; lock the rows you read (SELECT … FOR UPDATE on the whole set); or enforce the rule with a database constraint or a single row that represents the invariant (so there is a write-write conflict). Set the numbers.
Two doctors, two requests, rule of at least one: snapshot isolation leaves zero on call. Switch to serializable.
2 requests each saw 2 doctors on call, passed the check and committed on different rows. 0 remain on call but the rule needs 1: the invariant is broken and the database reported no error.
Snapshot isolation: every concurrent request passes its own check, so the remaining count is n − k (not below zero). Serializable: requests run as if one at a time, so the count stops at the minimum.