Learn the theory
SQL Isolation Levels Lab: how it works
5 min read · Updated
Databases serve many clients at once, and those clients' transactions overlap in time. Isolation — the “I” in ACID — defines how much one transaction is allowed to see of another transaction's in-flight work. Stronger isolation means fewer surprising results but more waiting or more aborted transactions, so every database lets you pick a level. This lab runs two real interleavings side by side so you can watch each anomaly happen, and watch the right level prevent it.
In short
- Isolation is a trade-off: stricter levels prevent more anomalies but cost concurrency (locks, retries).
- The classic anomalies are dirty read, non-repeatable read, phantom read, lost update and write skew.
- READ COMMITTED is the default in PostgreSQL, Oracle and SQL Server; REPEATABLE READ is MySQL InnoDB's default.
- Snapshot-based REPEATABLE READ still allows write skew; only SERIALIZABLE stops it — by aborting a transaction.
- With stricter levels, your application must be ready to retry failed transactions.
What isolation means
A transaction is a group of statements that should behave as one unit. Atomicity makes it all-or-nothing and durability makes commits permanent. Isolation answers a different question: while my transaction is running, what can I see of everyone else's?
The ideal is serializability: the result is the same as if the transactions had run one after another. That is the safest model, but enforcing it for every workload is expensive, so databases offer weaker levels that allow specific, well-defined anomalies in exchange for speed.
The anomalies
| Anomaly | What happens | Example |
|---|---|---|
| Dirty read | You read data another transaction hasn't committed (and may roll back). | A sets balance = 0 but rolls back; B already saw 0. |
| Non-repeatable read | Reading the same row twice in one transaction gives different values. | B reads balance = 100; A commits 50; B reads 50. |
| Phantom read | Re-running a range query returns a different set of rows. | B counts accounts ≥ 100 → 2; A inserts one; B counts → 3. |
| Lost update | Two read-modify-write cycles overwrite each other. | Both read 100; A writes 70, B writes 50; A's change vanishes. |
| Write skew | Two transactions read overlapping data, then write different rows, breaking a rule neither alone would break. | Two on-call doctors each check “someone else is on call” and both leave. |
The four standard levels
| Level | Dirty read | Non-repeatable | Phantom* | Lost update | Write skew |
|---|---|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible | possible | possible |
| READ COMMITTED | prevented | possible | possible | possible | possible |
| REPEATABLE READ | prevented | prevented | prevented† | prevented† | possible |
| SERIALIZABLE | prevented | prevented | prevented | prevented | prevented |
*The SQL standard permits phantoms at REPEATABLE READ. †PostgreSQL and MySQL InnoDB use snapshots, which hide phantoms from plain SELECTs and (in PostgreSQL) abort conflicting updates. Always check your engine's documentation: level names are shared, behavior isn't.
How databases implement it
There are two main strategies, often mixed:
- Locking. Readers take shared locks and writers take exclusive locks; conflicting transactions wait. Simple to reason about, but readers and writers block each other.
- MVCC (multi-version concurrency control). Every write creates a new version of the row. Each transaction reads the version that was current at its snapshot. Readers never block writers. PostgreSQL, Oracle and InnoDB work this way.
| Engine | Default level | Notes |
|---|---|---|
| PostgreSQL | READ COMMITTED | READ UNCOMMITTED behaves as READ COMMITTED. SERIALIZABLE uses Serializable Snapshot Isolation (SSI). |
| MySQL (InnoDB) | REPEATABLE READ | Uses MVCC plus next-key locks, which also block many phantoms. |
| SQL Server | READ COMMITTED | Optional row-versioning (RCSI) and SNAPSHOT isolation. |
| Oracle | READ COMMITTED | No true READ UNCOMMITTED; SERIALIZABLE is snapshot-based. |
Write skew and SERIALIZABLE
Write skew is the sneaky one. Each transaction sees a consistent snapshot, writes a different row, and no row-level conflict is detected — yet together they break an invariant (“at least one doctor on call”). REPEATABLE READ cannot see the problem because the two writes never touch the same row.
SERIALIZABLE closes the gap. PostgreSQL tracks read/write dependencies between concurrent transactions and aborts one when no serial order could explain the outcome. You'll see an error such as could not serialize access due to read/write dependencies (SQLSTATE 40001). That is not a bug: it is the database protecting your data and asking you to retry.
Choosing a level in practice
- Start with your database's default (READ COMMITTED or REPEATABLE READ) and think about which invariants span multiple rows.
- For read-modify-write on a single row, lock it explicitly with
SELECT … FOR UPDATE, or do the arithmetic in SQL (SET balance = balance - 30). - For cross-row rules (like on-call), use SERIALIZABLE, an explicit lock on a parent row, or a constraint the database can enforce.
- Whatever you choose, wrap transactions in a retry loop for serialization failures and deadlocks.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = 1; -- must stay >= 1
UPDATE doctors SET on_call = 0 WHERE id = 2;
COMMIT; -- may fail with SQLSTATE 40001: retry the whole transactionFrequently asked questions
- What are the four SQL isolation levels?
- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, ordered from weakest to strongest. Each prevents more anomalies than the one before it.
- What is a dirty read?
- Reading data written by a transaction that hasn't committed yet. If that transaction rolls back, you acted on data that never officially existed. Only READ UNCOMMITTED allows it.
- What is the difference between a non-repeatable read and a phantom read?
- A non-repeatable read means an existing row you read earlier now has a different value. A phantom read means the set of rows matching your query changed because rows were inserted or deleted.
- What is write skew?
- An anomaly where two concurrent transactions read overlapping data and then update different rows, violating an invariant that each transaction alone would have preserved. Snapshot isolation allows it; SERIALIZABLE prevents it.
- Is REPEATABLE READ enough to prevent lost updates?
- In PostgreSQL, yes: a transaction that tries to update a row changed after its snapshot is aborted. In MySQL InnoDB, plain updates read the latest committed row, so use
SELECT … FOR UPDATEor atomic updates. - Why does SERIALIZABLE throw errors?
- To guarantee serializable outcomes without locking everything, the database lets transactions run concurrently and aborts one if their combined effect couldn't have happened in any serial order. Your code should retry the transaction.