Concept · Transactions & indexes
Isolation levels and anomalies
Read committed, repeatable read and serializable — and the dirty reads, lost updates and write skew each one allows.
4 minREAD COMMITTEDREPEATABLE READSERIALIZABLEwrite skew
Running transactions one at a time would be perfectly safe and far too slow. Isolation levels let them overlap, and each level names which anomalies, or interleaving bugs, it still allows.
The anomalies
- Dirty read: you see another transaction's uncommitted write, and it later rolls back.
- Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed in between.
- Phantom: you run the same
WHEREtwice and get a different set of rows. - Lost update: two transactions read
stock = 5, both writestock = 4, and one sale vanishes. - Write skew: two transactions each check a condition ("at least one doctor on call"), each update a different row, and together they break the condition.
The levels in PostgreSQL
| Level | What it guarantees | Still possible |
|---|---|---|
READ COMMITTED (default) | Each statement sees data committed before it started | Non-repeatable reads, phantoms, lost updates (read-then-write), write skew |
REPEATABLE READ | The whole transaction sees one snapshot; a conflicting concurrent update aborts it | Write skew |
SERIALIZABLE | The result equals some one-at-a-time order; dangerous patterns abort a transaction | Nothing — but you must retry aborts |
PostgreSQL builds these on MVCC snapshots, so it never allows dirty reads, even at its lowest level. Other databases differ. MySQL's InnoDB defaults to REPEATABLE READ, with different locking behaviour. Always check your database's documentation rather than the SQL standard's table.
Choosing
- Stay on read committed and make updates atomic when you can.
UPDATE items SET stock = stock - 1 WHERE id = $1 AND stock > 0has no lost-update window, because the read and the write are one statement. - Lock or version the row for read-modify-write logic that can't be a single statement. See optimistic vs pessimistic locking.
- Use
SERIALIZABLEfor invariants that span several rows, such as balances, bookings and on-call rules, where write skew is the real risk. Wrap the transaction in a retry loop on error40001.
These are guarantees within one database. Between replicas and services, the related idea is consistency models.