Isolation Levels

Perfect isolation means every transaction behaves as if it ran completely alone. In practice, full isolation is expensive, so SQL defines several weaker levels that each allow specific anomalies in exchange for better concurrency.

The four standard levels

* Postgres and MySQL/InnoDB both prevent phantoms at Repeatable Read, which is stricter than the ANSI standard requires.

The anomalies, with a scenario for each

Dirty read — reading uncommitted data

A nightly revenue report runs SELECT SUM(amount) FROM payments. A large refund transaction is mid-flight: it has written a -5000 row but not committed. The report picks it up, emails a number to finance, and then the refund transaction rolls back. The report was based on a value that never existed.

Prevented at Read Committed and above — which is why essentially no one runs Read Uncommitted.

Non-repeatable read — a row changes under you

A billing job reads a subscription’s plan_price ($10), does some work, then reads it again to compute proration and gets $20 because the customer upgraded in between. The two halves of the calculation used different prices.

Prevented at Repeatable Read: within one transaction, a row you’ve read keeps the value it had.

Phantom read — the set of matching rows changes

A transaction runs SELECT COUNT(*) FROM bookings WHERE room_id = 5 AND date = '2026-07-01', sees 0, and inserts a booking. Between the count and the insert, another transaction inserted a booking for the same room and date. Now there are two — the row it was checking for didn’t exist when it looked, so the check passed.

Lost update — two read-modify-write cycles collide

Two support agents both open ticket #900, which has note_count = 4. Each app reads 4, adds a note, writes back 5. Two notes were added; the count says 5. One update was silently lost.

Write skew — each transaction is fine alone, together they break an invariant

The rule: at least one doctor must be on call. Two doctors are on call. Both simultaneously request to go off call. Each transaction checks “is there another doctor still on call?” — sees the other one — and proceeds. Now zero doctors are on call. Neither transaction violated the rule as it saw the data, but the combined result does.

Write skew is the anomaly Repeatable Read/Snapshot isolation does not stop. Only Serializable does.

The practical patterns for fixing each

Atomic write — let the database do the read-modify-write

Best fix for lost updates when you’re just incrementing/decrementing:

-- Not: read qty, subtract in app, write back
UPDATE inventory SET qty = qty - 1
WHERE sku = 'ABC-123' AND qty >= 1;
-- rows affected = 0  → there was no stock; handle it

Pessimistic lock — SELECT ... FOR UPDATE

When you must read a row, make a decision in app code, then write:

BEGIN;
SELECT seats_left FROM events WHERE id = 7 FOR UPDATE;  -- locks the row
-- app checks seats_left > 0
UPDATE events SET seats_left = seats_left - 1 WHERE id = 7;
INSERT INTO tickets (event_id, user_id) VALUES (7, 42);
COMMIT;

Other transactions block on the FOR UPDATE until you commit. Good for hot single rows (ticket sales, inventory); can serialize traffic if the locked row is very popular.

Optimistic lock — a version column

Better when conflicts are rare and you don’t want to hold locks (e.g. a user editing a form for two minutes):

UPDATE documents
SET body = $newbody, version = version + 1
WHERE id = $id AND version = $version_the_user_loaded;
-- rows affected = 0  → someone else saved first; show a merge/conflict UI

Serializable + retry loop

When the invariant spans multiple rows (the on-call doctors case), no single-row lock helps. Run the transaction at SERIALIZABLE and retry on serialization failure:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true AND shift_id = 5;
-- if count > 1, allow this doctor to go off call
UPDATE doctors SET on_call = false WHERE id = 42;
COMMIT;  -- may fail with a serialization error → catch it and retry the whole block

Choosing a level per feature