Isolation Levels
Read uncommitted through serializable: which anomalies you tolerate for concurrency.
Level: advanced
What is it?
Transaction isolation defines which effects of concurrent transactions a transaction may observe. SQL isolation levels describe guarantees around anomalies such as dirty reads, non-repeatable reads, phantoms, and serialization anomalies, but exact semantics differ by database implementation.
Why does it matter?
Concurrency increases throughput but can expose inconsistent observations if transaction reads and writes interleave. Isolation provides a defined compromise between consistency, concurrency, blocking, and retry cost.
Mental model
Imagine two transactions reading and updating overlapping data. The isolation contract determines whether one can observe another's uncommitted data, repeat a read and get a changed value, or commit a result that could not correspond to a serial execution.
How it works
- Read Uncommitted permits the weakest isolation in systems that implement it distinctly; some engines treat it like a stronger level.
- Read Committed commonly ensures each statement sees committed data but can allow different statements to observe different committed snapshots.
- Repeatable Read strengthens repeat-read behavior, though phantom and serialization semantics are database-specific.
- Serializable aims for results equivalent to some serial ordering, sometimes using locks or abort-and-retry validation.
- Apps must handle serialization failures and retries, and should not rely on a level name without knowing the engine's implementation.
Steps
- Transaction A reads a balance.
- Transaction B updates and commits the balance.
- Transaction A reads the balance again; whether it sees the old or new value depends on isolation and database semantics.
- Choose the lowest level that preserves the business invariant, and handle conflicts explicitly.
Real-world usage
- Preventing lost updates and inconsistent financial computations.
- Choosing transaction isolation in web services.
- Explaining anomalies with interleaving schedules.
Common mistakes
- Assuming all databases implement every isolation level identically.
- Assuming Repeatable Read universally prevents every anomaly called a phantom.
- Using a stronger isolation level without handling retries or increased contention.
Interview questions
What is a dirty read?
A transaction reads data written by another transaction that has not committed; the writer may still roll back.
What does Serializable aim to guarantee?
The committed result should be equivalent to some serial execution of transactions, though the engine may block or abort transactions to enforce it.