Transactions & Consistency
Isolation Levels
How strictly the database hides concurrent transactions from each other — stricter isolation prevents more anomalies at the cost of throughput.
Perfect isolation means every transaction behaves as if it ran completely alone. In practice, full isolation is expensive, so SQL defines several weaker levels that each allow specific anomalies in exchange for better concurrency.
Fig. 7 — Which anomalies each isolation level allows
Prevented at this level
Still possible
The anomalies, concretely
- Dirty read — reading a row another transaction has written but not yet committed. If that transaction rolls back, you read a value that never really existed.
- Non-repeatable read — reading the same row twice in one transaction and getting different values, because another transaction committed a change in between.
- Phantom read — re-running the same filtered query twice and getting a different set of rows, because another transaction inserted or deleted a matching row in between.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance < 0;
-- ... application logic ...
COMMIT;