We often think of isolation levels as checkboxes in a database configuration such as Read Committed, Repeatable Read, Serializable. But these labels are not arbitrary.
They’re the database world’s answer to a simple question: What can possibly go wrong if two transactions run together?
Each isolation level exists to prevent a certain class of anomalies i.e., strange, counter-intuitive behaviours that appear when concurrent transactions interact. Understanding isolation, then, is not about memorising levels, it’s about tracing the anomalies they eliminate.
Let’s walk up this ladder of anomalies.
1. Dirty Read: Resolved By Read Committed
The Anomaly
A dirty read happens when a transaction reads data written by another that hasn’t committed yet.
| Step | Transaction T1 | Transaction T2 |
|---|---|---|
| 1 | - | UPDATE accounts SET balance = balance - 100 WHERE id = 1; (Uncommitted) |
| 2 | SELECT balance FROM accounts WHERE id = 1; (Sees -100) | - |
| 3 | - | ROLLBACK |
T1 saw a value that never really existed. When T2 rolled back, that read became meaningless, like glancing at a draft that was never published.
The Resolution
Read Committed ensures every read sees only committed data. It doesn’t stop other anomalies, but it removes this most obvious one.
| Isolation Level | Prevents | Still Possible |
|---|---|---|
| Read Committed | Dirty Read | Non-repeatable Read, Phantom Read, Write Skew |
2. Non-Repeatable Read: Resolved By Repeatable Read
The Anomaly
A non-repeatable read occurs when a transaction reads the same row twice and gets different results because another transaction updated it in between.
| Step | Transaction T1 | Transaction T2 |
|---|---|---|
| 1 | SELECT salary FROM employees WHERE id = 7; (Sees 1000) | - |
| 2 | - | UPDATE employees SET salary = 1200 WHERE id = 7; COMMIT |
| 3 | SELECT salary FROM employees WHERE id = 7; (Sees 1200) | - |
T1’s world changed mid-conversation. The same question now gives a different answer.
The Resolution
Repeatable Read ensures that once a transaction reads a row, it will always see the same version of that row even if others modify it later. This is typically achieved through locks or snapshots.
| Isolation Level | Prevents | Still Possible |
|---|---|---|
| Repeatable Read | Dirty, Non-repeatable | Phantom, Write Skew |
3. Phantom Read: Resolved By Serializable
The Anomaly
A phantom read happens when a transaction re-executes a query and finds new rows that match its criteria because rows got inserted or deleted by another transaction.
| Step | Transaction T1 | Transaction T2 |
|---|---|---|
| 1 | SELECT COUNT(*) FROM orders WHERE amount > 100; (Sees 10) | - |
| 2 | - | INSERT INTO orders VALUES (id=11, amount=200); COMMIT |
| 3 | SELECT COUNT(*) FROM orders WHERE amount > 100; (Sees 11) | - |
The “phantom” order didn’t exist the first time T1 looked.
The Resolution
Serializable isolation ensures that the final outcome is identical to some serial (one-after-another) execution of all transactions. No phantoms, no re-reads, no surprises, just pure serial behaviour.
| Isolation Level | Prevents | Still Possible |
|---|---|---|
| Serializable | All anomalies | None |
4. Write Skew: Resolved By Snapshot Isolation and Serializable Snapshot Isolation
The Anomaly
Now for a subtler problem i.e., write skew, the hidden flaw of modern snapshot-based systems.
Imagine a hospital scheduling system:
Rule: At least one doctor must be on duty.
| Step | Doctor A (T1) | Doctor B (T2) |
|---|---|---|
| 1 | Reads all duty shifts -> sees B on duty | Reads all duty shifts -> sees A on duty |
| 2 | Updates self to “off duty” | Updates self to “off duty” |
| 3 | Both commit | - |
Result: No one on duty, even though both transactions saw a consistent world at the time of their reads. No single write conflicts with the other, so both commits succeed, yet the invariant breaks.
The Resolution
Snapshot Isolation (SI) gives each transaction a consistent snapshot of the database at start time, preventing dirty and non-repeatable reads. But it still allows write skew.
Serializable Snapshot Isolation (SSI) extends SI by tracking dependencies between transactions. If it detects a dependency cycle (where two transactions’ reads and writes influence each other), it aborts one, guaranteeing serializability without heavy locking.
| Isolation Level | Prevents | Still Possible |
|---|---|---|
| Snapshot Isolation | Dirty, Non-repeatable, Phantom | Write Skew |
| Serializable Snapshot Isolation | All | None |
The Isolation Ladder: From Weak to Strong
| Isolation Level | Prevents | Still Possible |
|---|---|---|
| Read Uncommitted | None | Dirty, Non-repeatable, Phantom, Write Skew |
| Read Committed | Dirty | Non-repeatable, Phantom, Write Skew |
| Repeatable Read | Dirty, Non-repeatable | Phantom, Write Skew |
| Snapshot Isolation | Dirty, Non-repeatable, Phantom | Write Skew |
| Serializable | All | None |
| Serializable Snapshot Isolation | All | None |
Conclusion
Every isolation level defines a trade-off between how much concurrency we allow and how much correctness we preserve.
Weak levels maximise throughput by relaxing truth; strong ones restore truth by constraining interleaving. In between lies the practical world of databases, where performance and precision must coexist. Isolation, then, is not a wall but a contract, a promise that even in parallel worlds, meaning remains intact.
In the next post, we’ll look beneath these promises to see how databases actually keep them through the concurrency-control mechanisms that make isolation real.