Transactions, isolation, and the anomalies in between
ACID is a promise with a dial. Know which dial position your workload needs.
What you will be able to do
- Map isolation levels to the anomalies they allow
- Recognise a lost update and a write skew in a schema
- Choose optimistic or pessimistic concurrency control with a reason
- Explain ACID versus BASE without slogans
The anomalies, named
| Anomaly | What happens | Allowed at |
|---|---|---|
| Dirty read | You read a value another transaction has not committed — and may roll back | Read uncommitted |
| Non-repeatable read | The same row returns a different value twice in one transaction | Read committed |
| Phantom read | A re-run query returns new rows because someone inserted matching ones | Repeatable read (in some engines) |
| Lost update | Two transactions read, both write, one overwrites the other | Read committed |
| Write skew | Two transactions read overlapping data and write disjoint rows, breaking a shared invariant | Repeatable read (even snapshot) |
Write skew is the one that surprises people. A doctor on-call rule says at least one doctor must remain scheduled. Two doctors each see two on call, each takes themselves off, each write is valid in isolation — and now nobody is on call. Snapshot isolation cannot see the other's uncommitted write, so it permits both.
-- Both transactions run this, both commit, invariant broken BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM on_call WHERE shift = 'night'; -- 2 DELETE FROM on_call WHERE doctor_id = 7 AND shift = 'night'; COMMIT;
Levels and their price
| Level | Blocks / aborts | Use when |
|---|---|---|
| Read uncommitted | Nothing | Analytics that can tolerate garbage; almost never for writes |
| Read committed | Writes block writes to the same row | Default for most application traffic |
| Repeatable read (snapshot) | Concurrent writers to the same row; reads never block | Reports and multi-statement reads that must be consistent |
| Serialisable | Conflicting transactions abort and retry | Money, inventory, booking — anything with an invariant across rows |
The cost of serialisable is retries under contention. At low conflict rates, retrying a handful of aborted transactions is cheaper than coordinating locks. At high contention, the abort rate explodes and you need to reduce the conflict surface: split the hot row, queue the writes, or accept a single-writer path for that aggregate.
Two ways to control concurrency
UPDATE seats SET taken = true WHERE id = 5 AND taken = false and check the affected row count. The database does the check atomically.ACID versus BASE, without the slogan
ACID describes a single database's guarantees across a transaction: atomic, consistent, isolated, durable. BASE describes a system that accepts temporary inconsistency — reads may be stale, writes may conflict, and reconciliation happens later. Neither is better in the abstract. The question is whether the business can tolerate a window where two nodes disagree, and how long that window is. A like counter can be eventually consistent forever; an account balance cannot.
- Cross-service transactions are usually sagas: a sequence of local transactions with compensating actions.
- Distributed transactions (two-phase commit) give atomicity at the cost of coordination latency and blocking on coordinator failure.
- Idempotent handlers make retries safe, which is what most saga steps need.
- Outbox tables turn a database write and a message publish into one atomic local transaction.
Worth remembering
- Read committed is the usual default and allows non-repeatable reads and lost updates.
- Serialisable is the only level that removes write skew; it costs retries or blocking.
- Optimistic control wins on low contention; pessimistic wins where conflicts are frequent.
- A uniqueness constraint or a conditional update is often cheaper than a heavier isolation level.