Transactions and isolation levels

What it is

A transaction is a group of statements that either all take effect or none do. ACID names four promises: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions do not corrupt each other), Durability (committed means survived).

Three of those are mostly free. Isolation is the one with a dial, and the dial is not turned all the way up by default.

Why it bites

Your database defaults to READ COMMITTED (Postgres, Oracle, SQL Server) or REPEATABLE READ (MySQL/InnoDB). Almost nobody changes it, and almost nobody knows which anomalies their default still permits. The result is a class of bug that only appears under concurrency, never reproduces locally, and gets closed as "could not reproduce".

The anomalies

Dirty read — you see another transaction's uncommitted write, which may then roll back. Prevented at READ COMMITTED and above. You will not meet this one in practice.

Non-repeatable read — you read a row twice in one transaction and get different values, because someone committed a change in between. Allowed at READ COMMITTED.

Phantom read — you run the same WHERE twice and get a different set of rows, because someone inserted one that matches. Allowed at READ COMMITTED and (in the standard) REPEATABLE READ.

Lost update — two transactions read, modify and write the same row; one overwrite is silently discarded. This is the one that costs real money, and READ COMMITTED does not stop it.

Write skew — two transactions each read a set, each check a constraint that currently holds, and each write something that individually looks fine but jointly violates the rule. The classic: two on-call doctors both request leave, each check "is at least one other doctor on shift?", both see yes, both commit, nobody is on shift. Only SERIALIZABLE prevents this.

The levels

Level	Dirty read	Non-repeatable	Phantom	Write skew
READ UNCOMMITTED	possible	possible	possible	possible
READ COMMITTED	no	possible	possible	possible
REPEATABLE READ	no	no	possible*	possible
SERIALIZABLE	no	no	no	no

* Postgres's REPEATABLE READ uses snapshot isolation and does prevent phantoms; the SQL standard does not require it.

What to actually do

Do not reach for SERIALIZABLE first. In order of preference:

1. Make the database enforce it. A UNIQUE constraint beats any isolation level for "no duplicate emails". A CHECK (balance >= 0) beats application logic.
2. Write atomic statements. UPDATE ... SET n = n - 1 WHERE n >= 1 cannot lose an update.
3. Lock explicitly with SELECT ... FOR UPDATE when you truly must read-then-write.
4. Raise the isolation level for the specific transaction that needs it — and be ready to retry, because SERIALIZABLE aborts transactions it cannot order.

Gotchas

• Keep transactions short. An open transaction holds locks and blocks vacuum/cleanup.
• Never make an HTTP call inside a transaction. The lock is held for the whole call, and a hung call holds it forever.
• Higher isolation means more aborts, so any code using SERIALIZABLE needs a retry loop.
• Your ORM probably opens a transaction per request. Know where it begins and commits.
• MySQL DDL is not transactional — a failed migration can leave you half-migrated.

Prove you know it

Open two psql sessions. In both, BEGIN, then read the same row's balance, then both write balance - 10, then both COMMIT. Watch one update disappear. Now redo it with UPDATE accounts SET balance = balance - 10 and watch it behave. That five-minute exercise is worth more than the table above.