Transaction Isolation Levels
the dial that decides which "impossible" bugs are possible
Databasesdatabases / concurrency
Most devs pick a database and never touch the isolation level. Then two transactions overlap in prod and you get a number that "can't happen." Isolation level is the dial controlling which concurrency anomalies your DB allows.
The 3 classic anomalies
- Dirty read — you read another txn's uncommitted change; it later rolls back. You acted on data that never existed.
- Non-repeatable read — you read row X twice in one txn and get two different values (someone committed an UPDATE in between).
- Phantom read — you run the same WHERE twice and new rows appear the second time (someone committed an INSERT matching your filter).
What each level blocks (✅ = anomaly can still happen)
Level | Dirty | Non-repeat | Phantom
Read Uncommitted | ✅ | ✅ | ✅
Read Committed | ❌ | ✅ | ✅
Repeatable Read | ❌ | ❌ | ✅*
Serializable | ❌ | ❌ | ❌
Higher = safer, but more locking/aborts = less concurrency.
Gotchas that bite intermediates
- Defaults DIFFER. PostgreSQL default = Read Committed. MySQL/InnoDB default = Repeatable Read. Same code, different behavior across engines.
- That table is the ANSI spec, not your engine. Postgres "Repeatable Read" is snapshot-based and actually blocks phantoms too — always test your real DB, don't trust the spec.
- Serializable ≠ "runs one at a time." Postgres uses SSI: txns run concurrently, then one gets ABORTED at commit with error 40001. Your app MUST retry on that error, or Serializable silently loses writes under load.
The overdraft example
Balance = 100. Two withdrawals of 100 fire at the same instant.
- Read Committed: both read 100, both see "enough funds," both commit → balance -100. Overdraft.
- Serializable: one commits; the other gets a serialization error → you retry it, it now reads 0, and correctly rejects.
Set it explicitly + retry:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- read balance, insert withdrawal
COMMIT;Wrap that in a loop that retries on SQLSTATE 40001 (serialization_failure).
Your exercise
Open two psql/mysql sessions.
Session A: BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1; (do NOT commit)
Session B (Read Committed): SELECT balance FROM accounts WHERE id = 1;
Does B block, or return the OLD value? Now redo the whole thing at Serializable — what error (if any) does one session get, and which one?