Notestransactions🌱 seedling
Isolation Level
Also: Transaction Isolation Level · Isolation Levels · ANSI Isolation Levels · Weak Isolation
An isolation level is a contract about which concurrency anomalies a database allows. Weaker levels admit more anomalies in exchange for less blocking and fewer aborts; only [[Serializability]] admits none.
Why it exists
Serializability costs throughput or aborts, so databases offer weaker levels and default to one of them. The application must know which anomalies remain and design around them.
How it works
| Level | Prevents | Still allows |
|---|---|---|
| Read Committed | Dirty Reads, dirty writes | read skew, Lost Updates, Write Skew, Phantom Reads |
| Snapshot Isolation | the above + read skew; lost updates in most engines | write skew, phantom-driven write skew |
| Serializable | everything | nothing |
- SQL-92 defined four levels by three phenomena (dirty read, non-repeatable read, phantom). Those definitions are ambiguous and miss dirty writes, lost updates and write skew, and cannot describe snapshot isolation.
- The precise modern definitions treat each level as forbidding certain cycles in the graph of dependencies between transactions (write-write, write-read, read-write).
Trade-offs
| Weaker level | Stronger level |
|---|---|
| more concurrency, fewer aborts | fewer anomalies to reason about |
| invariants enforced by hand: locks, constraints | blocking or retries on conflict |
In practice
- Names lie: Postgres "Repeatable Read" is snapshot isolation; MySQL's default "Repeatable Read" is weaker than snapshot isolation and allows lost updates; Oracle's "Serializable" is snapshot isolation.
- Defaults: Read Committed in Postgres, Oracle and SQL Server; Repeatable Read in MySQL; Serializable in CockroachDB.
Pitfalls
- Choosing a level by name across databases. Test the anomalies you care about.
- Replicas can provide weaker isolation than the primary, especially for reads routed to them.
Connections
- Part of
- Transactions
- Builds on this
- MVCC
- In real systems
- PostgreSQLMySQL
- Mentioned
- CockroachDB
Recall
These cards are also in your review deck, scheduled with spaced repetition.