Storage Internals
Transactions and Isolation Levels
What 'isolated' really promises, the anomalies each level allows, and how MVCC lets readers and writers stop blocking each other.
By the end of this chapter
- Name the read anomalies — dirty, non-repeatable, phantom — and the write anomalies — lost update, write skew
- Say which anomalies each isolation level permits and what preventing them costs
- Explain how MVCC serves consistent snapshots without read locks
- Choose between atomic updates, row locks, optimistic versions and serializable retries
What a transaction promises
A transaction groups several reads and writes so they succeed or fail together (atomicity) and survive a crash once committed (durability). Those two are fairly absolute. The third promise, isolation — that concurrent transactions do not see each other's half-finished work — is the one with settings, and the settings are where real bugs live.
Concept
Isolation, stated plainly
Perfect isolation means the outcome is the same as if the transactions had run one at a time, in some order. Every weaker level is a precise list of ways in which concurrent transactions are allowed to observe each other.
The anomalies, by name
| Anomaly | What happens | Example |
|---|---|---|
| Dirty read | You read another transaction's uncommitted write | You see a balance that is later rolled back |
| Non-repeatable read | The same row read twice returns different values |
Sign in to keep a note here. It is free, and nobody else can see it.
Sign inYou’ve reached the end
What this chapter was for:
- Isolation is a dial, and most databases do not default to the strictest setting.
- MVCC keeps several versions of a row so readers see a snapshot while writers proceed.
- Snapshot isolation prevents the classic read anomalies but still allows write skew.
- A read-modify-write in application code is a lost update waiting to happen unless something guards it.
- Serializable is correct by construction and pays for it in aborts that you must retry.