Isolation Levels
How strictly a database stops one transaction from seeing another's in-progress changes — and the anomaly each level still allows.
Overview
An isolation level is a database's configurable setting for exactly how strictly it prevents one transaction from seeing another transaction's in-progress or conflicting changes — ranging from Read Uncommitted (almost no protection) to Serializable (transactions behave as if run one at a time), with a specific, named anomaly still possible at every level except the strictest.
Why It Exists
Perfect isolation (Serializable) is the safest option, but also the most expensive in terms of concurrency and throughput — achieving it usually means heavier locking or forcing some transactions to abort and retry. Real engines offer weaker levels as a deliberate trade-off, so an application that can genuinely tolerate a specific anomaly gets meaningfully better performance instead of paying for a guarantee it doesn't need.
Real World Example
Two concurrent transactions both read a product's stock count as 10, and both independently decide there's enough stock to fulfill their own order, then both proceed to deduct from it. Under a weak isolation level, this can oversell the product — a classic anomaly. A stronger isolation level would force one of the two transactions to see the other's update, or abort and retry, before either could complete.
Example Data
A dirty read under Read Uncommitted — Transaction A sees a value that never actually commits
| Time | Transaction A | Transaction B |
|---|---|---|
| t1 | — | UPDATE accounts SET balance = 0 WHERE id = 1 (not yet committed) |
| t2 | SELECT balance FROM accounts WHERE id = 1 → reads 0 | — |
| t3 | — | ROLLBACK (the update never actually happened) |
| t4 | Transaction A already acted on a balance of 0 that never existed | — |
The Four Standard Levels, Weakest to Strongest
Read Uncommitted
A transaction can see another transaction's uncommitted changes, which might still get rolled back. This is the weakest level, permits every anomaly, and is rarely used in practice — some engines (like Postgres) don't even implement it as a distinct level.
Read Committed
A transaction only ever sees data that's actually been committed — no dirty reads. But reading the same row twice within one transaction can return different values if another transaction committed a change to it in between. This is the default isolation level in Postgres, Oracle, and SQL Server.
Repeatable Read
Re-reading the same row is guaranteed to return the same value for the entire transaction. A new row matching a previous query's filter can still appear if inserted by another transaction — a 'phantom' row. (Note: Postgres's implementation of Repeatable Read is actually snapshot isolation, which also prevents phantoms in practice — stricter than the SQL standard's minimum requirement for this level. Always check your specific engine's actual behavior, not just the textbook definition.)
Serializable
Transactions behave exactly as if they ran one after another in some order — zero anomalies possible. Achieved via extensive locking or conflict detection, which can mean a transaction is aborted and must be retried rather than blocked.
Diagram
Each step up trades some concurrency/throughput for one fewer possible anomaly
Read Uncommitted
weakest — dirty reads possible
Read Committed
non-repeatable reads possible
Repeatable Read
phantom reads possible (per the SQL standard)
Serializable
strongest — no anomalies possible
Common Mistakes
Assuming the engine's default isolation level is Serializable
Why: Most engines default to something weaker for performance reasons — Postgres, Oracle, and SQL Server default to Read Committed; MySQL/InnoDB defaults to Repeatable Read. Code written assuming full isolation can have real, rare-but-real concurrency bugs.
Fix: Check and explicitly set the isolation level for any transaction where a specific anomaly would genuinely cause a problem.
Reaching for Serializable everywhere 'to be safe'
Why: It's the most expensive level, often implemented via heavy locking or by aborting transactions that would violate ordering — applying it universally can significantly reduce throughput for logic that never needed that level of protection.
Fix: Pick the weakest isolation level that still prevents the specific anomaly your logic actually can't tolerate.
Not handling serialization failures at the Serializable level
Why: True serializability is often achieved by aborting a transaction that would have violated ordering, rather than blocking it — application code that doesn't specifically catch and retry that error surfaces it as an unexpected failure to the user.
Fix: Wrap Serializable transactions in retry logic that specifically catches serialization-failure errors and retries the transaction.
Interview Questions
Name the four standard SQL isolation levels, from weakest to strongest.
Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
What's the difference between a non-repeatable read and a phantom read?
A non-repeatable read is re-reading the exact same row within a transaction and getting a different value, because another transaction updated it in between. A phantom read is re-running the same filtered query and getting a different set of rows, because another transaction inserted (or deleted) a row matching that filter in between.
Two concurrent transactions both read a stock count and both proceed to sell the last unit. Which isolation level(s) would prevent this, and what's the trade-off?
Serializable would prevent it outright, by ensuring the two transactions can't both act on the same stale read as if they ran in isolation — one would be forced to see the other's update or be aborted and retried. The trade-off is reduced throughput and the need for retry logic. A cheaper alternative for this specific case is often an explicit row lock or a conditional update (`UPDATE ... WHERE stock > 0`) at a weaker isolation level, which solves the same problem without paying for full serializability everywhere.
Production Best Practices
Do
✓Know your engine's actual default isolation level rather than assuming Serializable.
✓Choose the weakest isolation level that still prevents the specific anomaly your logic can't tolerate.
✓Add retry logic around Serializable transactions to handle serialization failures.
Don't
✗Don't assume the SQL-standard anomaly table applies exactly to every engine — check the specific engine's real behavior (e.g. Postgres's Repeatable Read).
✗Don't default to Serializable everywhere without measuring the throughput cost.
✗Don't ignore serialization-failure errors as if they were unexpected bugs — they're an expected part of using Serializable.
Comparison
| Dirty Reads | Non-Repeatable Reads | Phantom Reads | |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (per standard) |
| Serializable | Prevented | Prevented | Prevented |