# Isolation levels and anomalies

> Read committed, repeatable read and serializable — and the dirty reads, lost updates and write skew each one allows.

Concept · Transactions & indexes · https://levelcode.org/concepts/isolation-levels

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 `WHERE` twice and get a different *set* of rows.
- **Lost update:** two transactions read `stock = 5`, both write `stock = 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](https://levelcode.org/concepts/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 > 0` has 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](https://levelcode.org/concepts/optimistic-vs-pessimistic-locking).
- **Use `SERIALIZABLE`** for 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 error `40001`.

These are guarantees within one database. Between replicas and services, the related idea is [consistency models](https://levelcode.org/concepts/consistency-models).