Isolation levels
Read committed, repeatable read, serializable — what each protects against.
The I in ACID stands for Isolation — the property that concurrent transactions don't interfere with each other. Perfect isolation (serializable) is expensive, so the SQL standard defines four levels, each allowing some specific anomalies in exchange for higher concurrency: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Understanding which anomalies each level prevents is one of the most-asked database interview questions and one of the most common sources of production bugs. Get it wrong and you double-charge customers, lose posts, ship duplicate orders. Get it right and your application scales without exotic locking gymnastics.
The catch: every database interprets these levels differently. Postgres's REPEATABLE READ actually implements snapshot isolation. Oracle's READ COMMITTED implements a per-statement snapshot. MySQL InnoDB's REPEATABLE READ has a special quirk called "phantom row avoidance via gap locks." The SQL-92 standard is a rough guideline; the actual behavior is per-database. This is why Berenson et al. (1995) wrote a famous paper "A Critique of ANSI SQL Isolation Levels" showing the standard was underspecified.
The papers
- Gray et al. (1976) — the original ACID paper. Coined the term.
- ANSI SQL-92 (1992) — standardized the 4 isolation levels + 3 anomalies.
- Berenson et al. (1995) — "A Critique of ANSI SQL Isolation Levels." SIGMOD. Showed the standard is underspecified and introduced snapshot isolation formally.
- Adya et al. (2000) — "Generalized Isolation Level Definitions." ICDE. Cleaner formal treatment.
The four anomalies you must know
The four levels and what they prevent
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Write skew |
|---|---|---|---|---|
| Read Uncommitted | ❌ possible | ❌ possible | ❌ possible | ❌ possible |
| Read Committed | ✅ prevented | ❌ possible | ❌ possible | ❌ possible |
| Repeatable Read | ✅ prevented | ✅ prevented | ❌ possible* | ❌ possible |
| Serializable | ✅ prevented | ✅ prevented | ✅ prevented | ✅ prevented |
Read the table like this: at Read Committed, you're protected from dirty reads but not from anything else. At Repeatable Read, dirty reads AND non-repeatable reads are prevented. At Serializable, all four anomalies are gone.
Interactive: watch each anomaly happen
Per-database reality check
| Database | Default level | Strongest level | Note |
|---|---|---|---|
| PostgreSQL | Read Committed | Serializable (SSI) | Its 'Repeatable Read' is actually snapshot isolation |
| MySQL InnoDB | Repeatable Read | Serializable (locks a lot) | Gap locks prevent phantoms at RR |
| Oracle | Read Committed | Serializable (snapshot iso, admits write skew) | No 'Repeatable Read' at all |
| SQL Server | Read Committed | Serializable | Optional SNAPSHOT and READ_COMMITTED_SNAPSHOT modes |
| CockroachDB | Serializable | Serializable (only option) | Retryable errors — apps must retry |
| Spanner | Snapshot reads by default | External consistency | Uses TrueTime for global serializability |
| MongoDB (v4.0+) | Session-based snapshot | readConcern:'snapshot' | Pre-4.0 had no multi-doc transactions |
| DynamoDB | Read Committed | Serializable (transaction API) | Transaction API limited to 25 items |
Notice how the same isolation level means different things in different databases. Postgres's REPEATABLE READ actually gives you snapshot isolation — not what the SQL standard defines. Oracle's REPEATABLE READ doesn't exist at all. MongoDB pre-4.0 didn't even have multi-document transactions. Always test what your specific database does; don't trust the level name alone.
Applied in real systems
Postgres — Read Committed default, Serializable via SSI
Default is Read Committed (per-statement snapshot). Repeatable Read = full snapshot isolation. Serializable (since 9.1) = Serializable Snapshot Isolation via predicate locks — expect occasional serialization failures that you retry.
MySQL InnoDB — Repeatable Read default
Default is Repeatable Read with gap locks that suppress phantoms (unusual choice — most others default to Read Committed). Serializable available but rarely used in practice (locks everything).
Oracle — Read Committed + Serializable only
Oracle only supports Read Committed (default) and Serializable. No Repeatable Read. Their Read Committed is actually per-statement snapshot (not the SQL standard flavor). Their Serializable is snapshot isolation, which admits write skew.
CockroachDB — Serializable by default
CockroachDB defaults to Serializable — the strongest level. Achieved via optimistic MVCC + write intents + retry-on-conflict. App developers must handle retryable serialization errors. Cost: higher latency; benefit: no write-skew.
MongoDB — snapshot isolation via readConcern majority
MongoDB 4.0+ supports multi-document transactions. readConcern:"snapshot" gives snapshot isolation. Isolation levels aren't exposed in SQL-standard terms — it's all snapshot- based with explicit majority write concern for linearizability.
DynamoDB — Read Committed + Transactional API
Standard reads = Read Committed. Transactional API (TransactWriteItems, TransactGetItems) provides atomic multi-item operations with serializable isolation (limited to 25 items per transaction).
SQL Server — 5 levels including SNAPSHOT
Supports Read Uncommitted, Read Committed, Repeatable Read, Serializable, and SNAPSHOT (their name for MVCC snapshot isolation, off by default — enable via ALTER DATABASE).
Spanner — external consistency (stronger than serializable)
Uses TrueTime + Paxos to give external consistency: if T1 commits before T2 starts (real-time), every observer sees T1 before T2. This is strictly stronger than serializable. Bounded to the atomic-clock uncertainty (~7ms).
Key takeaways
- 4 SQL standard levels: Read Uncommitted → Read Committed → Repeatable Read → Serializable. Each prevents progressively more anomalies.
- 4 anomalies: dirty read, non-repeatable read, phantom read, write skew. First three are in the SQL standard; write skew was recognized later.
- Every database interprets these differently. Postgres RR = snapshot isolation. MySQL RR = SI + gap locks. Oracle has no RR.
- Default isolation varies wildly: Postgres + Oracle default Read Committed; MySQL defaults Repeatable Read; CockroachDB defaults Serializable.
- Write skew is the killer anomaly that standard Serializable doesn't always catch — snapshot isolation allows it. Postgres SSI + CockroachDB use predicate locks to prevent it.
- For interviews: know what each level prevents, be able to construct a phantom-read scenario, and understand why Serializable costs the most.
References
- Berenson et al. (1995) — A Critique of ANSI SQL Isolation Levels. The definitive practical treatment.
- Adya et al. (2000) — Generalized Isolation Level Definitions.
- Kleppmann (2017) — Designing Data-Intensive Applications, Chapter 7 is the modern reference.
- Postgres, MySQL, Oracle, CockroachDB docs — always check the actual behavior for your engine.
Practice what you just read
Every foundation concept has a companion quiz to close the loop.