Start here: the level is a promise about anomalies#
TL;DRthe 30-second version
- Run transactions concurrently and they can interfere in named ways: a dirty read, a non-repeatable read, a phantom, and a write skew. Each one is a specific way that a schedule of interleaved reads and writes gives a result you would never get running the transactions one at a time.
- An isolation level is a promise about which of these anomalies the database will not let you observe. It is not a promise about how the database is built, only about what you are allowed to see.
- The levels form a ladder. Read Uncommitted allows everything. Read Committed removes dirty reads. Repeatable Read also removes non-repeatable reads. Snapshot Isolation also removes phantoms. Serializable removes write skew as well, so nothing is left.
- Each rung up the ladder buys safety with cost: more locking, more version retention, or more transactions aborted and retried. The skill is picking the lowest rung that still protects the rule your application actually depends on.
A transaction is a group of reads and writes that should behave as one unit. Isolation, the I in ACID, is the guarantee that concurrent transactions do not trip over each other. The strongest possible form of that guarantee is serializability: the result of running many transactions at once is identical to running them one after another in some order. That is what you would draw on a whiteboard, and it is what your application logic almost always assumes.
The trouble is that serializability is expensive. Enforcing it fully means transactions wait for each other or get thrown out and retried, and under heavy load that waiting dominates. So databases let you relax the guarantee in controlled steps. Each weaker level permits a specific, named anomaly in exchange for letting more work run in parallel. The level does not describe the engine underneath. It describes the contract: here is the set of anomalies you might see, and everything outside that set will never happen.
The four anomalies, each as a two-transaction story#
There are four anomalies worth knowing by name. Each is a short story about two transactions, T1 and T2, sharing rows. Read each one as a schedule from top to bottom.
A dirty read is reading a value that has not committed. T1 writes a balance of 500 but has not committed. T2 reads the balance and sees 500. Then T1 rolls back, so 500 never truly existed. T2 has now made a decision on a value the database will pretend was never written. This is the worst anomaly, because the data T2 acted on is not merely stale, it is fictional.
T1: write bal = 500 (uncommitted) T2: read bal -> 500 <-- sees uncommitted data T1: rollback (bal is 100 again) T2: acted on 500, which never existed
A non-repeatable read is when a single row changes value under you. T1 reads the balance and sees 100. T2 then writes the balance to 150 and commits. T1 reads the same row again and now sees 150. Within one transaction, the same row gave two different answers, because someone committed a change in between. Any logic in T1 that assumed the balance was stable is now wrong.
A phantom is the same problem one level up, at the level of a set of rows rather than a single row. T1 runs a range query, say every account with a balance of at least 100, and counts three rows. T2 inserts a fourth matching account and commits. T1 runs the very same query again and now counts four. The extra row is the phantom: it appeared in a result set that T1 expected to be stable. Freezing the rows T1 already read does not help, because the new row was never one of them.
A write skew is the subtle one, and it needs no reads to change at all. Two doctors are both on call, and the rule is that at least one must stay on call. T1 checks that the other doctor is on call, sees yes, and takes itself off. At the same time T2 checks that the first doctor is on call, sees yes, and takes itself off. Each read a row the other was about to change, each wrote a different row, and each decision was correct in isolation. Both commit, and now nobody is on call. Neither transaction did anything wrong alone; the schedule as a whole broke the rule.
The ladder: each rung closes one more anomaly#
Line the anomalies up from cheapest to prevent to hardest, and the isolation levels fall out as a staircase. Each level forbids everything the level below it forbids, plus one more anomaly. The rule that changes at each rung is always the same kind of rule: what a read is allowed to see.
Read Committed removes dirty reads by a small change: a read returns only the latest committed value, never an in-flight one. It re-reads live, though, so a value can still change between two reads in the same transaction. That is why it still allows a non-repeatable read.
Repeatable Read goes further by fixing each row the first time a transaction reads it. Read the balance once and every later read in that transaction returns the same value, whatever else commits. But fixing individual rows says nothing about rows that did not exist yet, so a fresh range query can still turn up a newly inserted row. The phantom survives.
Snapshot Isolation closes the phantom by freezing not individual rows but the entire database as the transaction saw it at begin. Every read, including a range scan, is answered from that one frozen picture, so no new row can slip into a result. This is the level most MVCC databases actually give you, and it prevents all three read anomalies. The one gap it leaves is write skew, because write skew is not about what a read sees changing — each read stayed perfectly stable — but about two transactions making crossing decisions.
The top rung: catching write skew#
Write skew is what separates Snapshot Isolation from true Serializable, so it is worth seeing exactly how the top rung catches it. Go back to the two doctors. T1 read the row for the other doctor and wrote its own row. T2 did the mirror image. Draw the dependencies and you get a cycle: T1 read a row that T2 wrote, and T2 read a row that T1 wrote. That crossing pair of read-write dependencies is the fingerprint of a schedule that cannot be reproduced by running the transactions one at a time.
A serializable engine watches for that fingerprint. The common modern technique, Serializable Snapshot Isolation, runs transactions on snapshots exactly like Snapshot Isolation but tracks the read-write dependencies between concurrent transactions. When it detects the dangerous crossing pattern at commit, it aborts one of the transactions and the application retries it. In the doctors example, one commit succeeds and the other is turned away, so at least one doctor stays on call and the rule holds. The cost is real: a workload with genuine write skew will see aborts and retries under Serializable that it never saw under Snapshot Isolation.
PredictTwo transactions each read a shared counter that reads 100, and each writes to a different row based on that read. Under Snapshot Isolation both commit. What changes under Serializable, and how does the database know to act?
Hint: Neither read changed value, and the two writes touched different rows. So what is left for the database to detect?
Under Serializable one of the two transactions aborts. The engine tracks read-write dependencies between concurrent transactions. Here T1 read a row that T2 is writing and T2 read a row that T1 is writing, which forms a cycle of read-write dependencies. That cycle means the schedule is not equivalent to running the two transactions one after another, so the engine aborts the second one to commit and the application retries it. The retry re-reads the now-updated state and makes a decision consistent with the transaction that already committed. Under Snapshot Isolation there is no such tracking, the two writes land on different rows so no write-write conflict fires, and both commit, leaving the joint rule broken. The difference is entirely in the extra dependency checking, not in what any single read returned.
What each rung costs#
Stronger isolation is never free, and the cost takes a different shape at different rungs. At the locking end, a level like Serializable implemented with strict two-phase locking makes transactions hold locks until they commit, so a reader and a writer of the same row wait for each other and throughput drops under contention. At the snapshot end, the cost moves elsewhere: keeping old versions alive so readers can see a consistent past means storage grows and a background process must reclaim the dead versions, and Serializable adds the extra bookkeeping to detect dependency cycles, which shows up as aborts.
Aborts are the cost that surprises people most. A transaction that ran cleanly for a hundred milliseconds can be thrown out at the very end because a conflicting transaction committed first. The application has to be written to expect a serialization failure and retry the whole transaction. On a low-contention workload almost nothing aborts and the stronger level is nearly free. On a hot row or a hot rule that many transactions touch at once, retries pile up and you can spend more time redoing work than doing it.
The names lie: what databases actually give you#
The single biggest source of confusion is that the level names come from the 1992 SQL standard, which defined the levels by which anomalies they forbid, and real databases map their own behaviour onto those names loosely. Two systems set to the same named level can behave differently, and one system's name can hide a stronger guarantee than the standard requires.
| Setting | What the SQL standard says | What a real engine often does |
|---|---|---|
| Read Committed | No dirty reads; non-repeatable reads and phantoms allowed | PostgreSQL default: a fresh snapshot per statement |
| Repeatable Read | Also no non-repeatable reads; phantoms allowed | PostgreSQL: full Snapshot Isolation, so no phantoms either |
| Repeatable Read | Same standard definition | MySQL InnoDB default: MVCC snapshot plus next-key locks |
| Serializable | No anomalies at all | PostgreSQL: Serializable Snapshot Isolation, detects write skew |
The lesson is to reason about the anomalies you must prevent, then verify what your specific database does at a given setting, rather than trusting the label. PostgreSQL's Repeatable Read is really Snapshot Isolation and stops phantoms, which the standard does not require. The word Serializable meant strict locking for decades and now often means snapshot isolation with cycle detection, which behaves the same to your application but fails in a different way, through aborts rather than waiting. Same name, different machine.
One caveat about the clean staircase above: it is drawn for MVCC engines, where each rung freezes a bit more of your snapshot, so Snapshot Isolation sits neatly one step above Repeatable Read. The classic lock-based Repeatable Read behaves differently — its long-held read locks actually prevent write skew, the very anomaly Snapshot Isolation allows. So standard (lock-based) Repeatable Read and Snapshot Isolation are not nested; they are incomparable, each forbidding an anomaly the other permits. The ladder is the MVCC picture, not a universal law — another reason to check your specific engine.
In the wild
- PostgreSQL defaults to Read Committed. Its Repeatable Read is Snapshot Isolation (no phantoms), and its Serializable adds Serializable Snapshot Isolation to catch write skew, at the price of serialization-failure retries.
- MySQL's InnoDB defaults to Repeatable Read, built on MVCC snapshots plus next-key gap locks that prevent many phantoms the bare standard would allow.
- Oracle offers Read Committed and Serializable; its Serializable is actually snapshot isolation, so it does not catch every write skew the way a truly serializable engine does.
- SQL Server offers the lock-based standard levels and also a separate Snapshot isolation built on row versioning, letting you pick locking or MVCC semantics per transaction.
- Distributed databases such as CockroachDB and Google Spanner default to serializable isolation over MVCC timestamps, treating a snapshot as the database as of a specific time and paying for it with cross-node coordination.
Pitfalls & gotchas
Is Snapshot Isolation the same as Serializable?
No. Snapshot Isolation prevents dirty reads, non-repeatable reads, and phantoms, but it still allows write skew, where two transactions read overlapping data and each write a different row so that together they break an invariant neither broke alone. Serializable also forbids write skew, usually by detecting the read-write dependency cycle and aborting one transaction.
Why does my counter lose updates even at Read Committed?
Read Committed re-reads live but does not stop two transactions from reading the same value, each adding one, and each writing the result, so one increment is lost. Fix it by making the update atomic in the database (update the column with an expression that reads and writes in one statement), by taking an explicit row lock with a locking read, or by moving to a level that detects the conflict and forces a retry.
Does a higher isolation level slow everything down?
It depends on contention. With little contention, a stronger level costs almost nothing because few transactions ever conflict. With hot rows or hot rules that many transactions touch at once, a stronger level causes more waiting under locking engines and more aborts and retries under snapshot engines. The cost is proportional to how often transactions actually collide.
Two databases are both set to Repeatable Read. Will they behave the same?
Not necessarily. The names come from the SQL standard, which defines only a minimum. PostgreSQL's Repeatable Read is full Snapshot Isolation and prevents phantoms, while a strict reading of the standard allows them. Always confirm which anomalies your specific engine prevents at a given setting rather than trusting the shared label.
QuizA transaction under Repeatable Read reads a customer's balance, and later in the same transaction runs a range query for all accounts over 1000. Between the two, another transaction inserts a new account over 1000 and commits. What can the first transaction observe?
- Nothing changes; Repeatable Read freezes the whole database at begin.
- The balance it already read may change, but the range query is stable.
- The balance it already read stays the same, but the range query can return the new account as a phantom.
- It gets an error because the data changed underneath it.
Show answer
The balance it already read stays the same, but the range query can return the new account as a phantom. — Repeatable Read freezes rows the transaction has already read, so the balance it read earlier stays stable and cannot show a non-repeatable read. But it freezes individual rows, not the set of rows a predicate matches, so a newly inserted account matching the range can appear on the second query. That extra row is a phantom, which only Snapshot Isolation or Serializable would prevent by freezing the whole read view rather than individual rows.
In an interview
Lead with the framing: an isolation level is a promise about which anomalies you will never see, and the levels form a ladder where each rung closes one more. Then name the anomalies in order and the level that closes each, because that ordering is the whole subject compressed into one sentence.
- Name the four anomalies as short stories: dirty read is seeing uncommitted data, non-repeatable read is a row changing value under you, phantom is a new row appearing in a repeated range query, and write skew is two crossing decisions that jointly break a rule.
- Give the ladder: Read Uncommitted allows all, Read Committed stops dirty reads, Repeatable Read stops non-repeatable reads, Snapshot Isolation stops phantoms, Serializable stops write skew.
- Explain the one rule that changes at each rung: what a read is allowed to see, from uncommitted data, to latest committed, to a frozen row, to a frozen whole-database snapshot.
- State the cost honestly: stronger isolation means more locking and waiting on lock-based engines, or more aborts and retries on snapshot engines, and it only bites under real contention.
- Flag the naming trap: the SQL standard defines the levels by anomalies, but real engines map their behaviour on loosely, so PostgreSQL's Repeatable Read is actually Snapshot Isolation.
PredictAn interviewer says: 'Our booking system occasionally double-books the last seat even though every transaction checks availability first. We run at Read Committed. What is happening and what would you change?' What is the strong answer?
Hint: Both transactions check first and both pass the check. Which anomaly has that exact shape, and which level closes it?
This is a write skew or a lost update hiding behind a check-then-act pattern. Two transactions both read that one seat is free, both pass the availability check, and both write a booking for that seat, each on its own new row. At Read Committed nothing stops them, because neither read was dirty and the two inserts do not conflict on a single row. The fix depends on how strong a guarantee you want. The targeted fix is to make the check and the write conflict on a real row: take an explicit lock on the seat or the event row before booking (a locking read), or add a unique constraint on seat so the second insert fails outright. The broader fix is to raise that transaction to Serializable so the database detects the read-write cycle and aborts one booking, then retry it. The framing that shows understanding is that a check-then-act across two transactions is exactly the shape of write skew, and Read Committed was never going to prevent it.
References
- Berenson et al. — A Critique of ANSI SQL Isolation Levels (1995) — The paper that showed the standard's levels were underspecified and named snapshot isolation and write skew.
- PostgreSQL — Transaction Isolation — Exactly which anomalies each level prevents, including that Repeatable Read is Snapshot Isolation.
- MySQL — InnoDB Transaction Isolation Levels — How InnoDB implements each level with MVCC and gap locking.
- Cahill, Röhm, Fekete — Serializable Isolation for Snapshot Databases (2008) — The Serializable Snapshot Isolation technique that catches write skew on top of MVCC.
- Designing Data-Intensive Applications, Ch. 7 — Kleppmann's treatment of the anomalies, the levels, and why the names mislead.
Ready to try it?
The simulator is a real, deterministic implementation — pick a scenario and step through it, scrubbing the timeline forward and backward through every change.