Transaction isolation levels and the bugs they prevent

When many transactions run at once, they can see each other's half-finished work. Isolation levels control how much. Stronger isolation means fewer surprises, but usually more waiting or retries.

The anomalies

• Dirty read: you read data another transaction has changed but not committed. If it rolls back, you saw data that never existed.
• Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed a change between the reads.
• Phantom read: you run the same query twice and the set of rows changes, because someone inserted or deleted matching rows.
• Lost update: two transactions read the same value, both change it, and one overwrites the other.
• Write skew: two transactions each read overlapping data, make decisions based on it and write different rows, breaking a rule together. Example: two doctors both go off call because each saw that the other was still on call.

The four standard levels

Level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	possible	possible	possible
Read committed	prevented	possible	possible
Repeatable read	prevented	prevented	possible (in the standard)
Serializable	prevented	prevented	prevented

Real databases differ from the textbook table. For example, PostgreSQL's repeatable read also prevents phantoms through snapshots, but allows write skew.

Common defaults

• PostgreSQL: read committed.
• MySQL (InnoDB): repeatable read.
• SQLite: effectively serialised writes, one writer at a time.

Check your own database's documentation, since behaviour is not identical.

How the databases do it

Two main techniques:

• Locking: readers and writers wait for each other.
• MVCC (multi-version concurrency control): each transaction sees a snapshot, so readers do not block writers. PostgreSQL and MySQL InnoDB use this.

Which level should you use

• Read committed is a good default for most web apps.
• Use repeatable read or serializable for logic like balances, inventory and scheduling, where decisions depend on what you read. Serializable may abort transactions with a serialisation error, so be ready to retry.
• Never choose read uncommitted unless you truly accept wrong data.

Other tools for correctness

• SELECT ... FOR UPDATE to lock rows you will change.
• Optimistic locking with a version column: update only if the version still matches.
• Atomic updates: UPDATE stock SET qty = qty - 1 WHERE qty > 0 instead of read-then-write.
• Unique constraints to stop duplicates.

Takeaway: know your default level, use stronger isolation or explicit locks where decisions depend on reads, and always be ready to retry.

---

Backend & Database series, Part 1 (SQL and transactions): 4 of 4. Previous: Database transactions and ACID, explained.