Transactions and Isolation: Reading Nothing Weird

"It's in a transaction" isn't a safety guarantee — it's a menu. Postgres defaults to read committed, which means each statement sees data committed when the statement started. Two statements in the same transaction can see different worlds, and that's by design.

The anomalies, in plain language

• Dirty read (impossible in Postgres at any level): seeing uncommitted data.
• Non-repeatable read (allowed at read committed): SELECT the same row twice, get different values because someone committed between your statements.
• Phantom read (allowed at read committed, prevented at repeatable read): a WHERE clause matches new rows on re-run.
• Serialization anomaly (only serializable prevents): two transactions each make a decision that's valid alone but contradictory together — the doctor-on-call problem where two "at least one doctor on call" checks both pass and both doctors go off call.

Picking the level

BEGIN ISOLATION LEVEL REPEATABLE READ;   -- snapshot taken at first statement
BEGIN ISOLATION LEVEL SERIALIZABLE;      -- anomaly detection, may abort

• Read committed (default): right for most request-scoped work — each statement sees fresh data.
• Repeatable read: for anything that reads, thinks, then writes — reports, financial ops, check-then-act logic.
• Serializable: when correctness outweighs the retry rate — transactions abort with serialization_failure and your code must retry.

Locks are the cheaper tool

For "one at a time" invariants, explicit locks beat isolation gymnastics:

SELECT * FROM accounts WHERE id = $1 FOR UPDATE;   -- lock the row
UPDATE accounts SET balance = balance - $2 WHERE id = $1;

SELECT ... FOR UPDATE locks the row in place; FOR UPDATE SKIP LOCKED is the foundation of every Postgres-backed job queue (workers grab different rows instead of blocking on each other). Advisory locks (pg_advisory_lock) give you application-level mutexes without touching table rows.

Field notes

• Deadlocks are detected automatically — the loser transaction gets deadlock detected and rolls back. Order your updates consistently to avoid them.
• ON CONFLICT (upsert) handles the most common race — concurrent inserts — without you thinking about isolation at all.