Transaction isolation levels

Every transaction runs at one of four isolation levels. The level decides one thing: how much of what other, concurrent transactions are doing your transaction is allowed to see.

Postgres implements only three of them for real — READ UNCOMMITTED is accepted for standards compliance and silently upgraded. Here's what each one actually guarantees, from the official docs:

Isolation level	What your transaction sees
READ UNCOMMITTED	Accepted, but treated exactly like READ COMMITTED. Postgres has no dirty reads.
READ COMMITTED (default)	Every statement re-reads the latest committed data, even mid-transaction.
REPEATABLE READ	The whole transaction sees one snapshot, frozen at its first query.
SERIALIZABLE	Same frozen snapshot, plus conflict detection: as if transactions ran one at a time.

The scenario

Alice and Bob are both on call. The rule: at least one engineer must always be on call. Before going off call, each of them checks "is someone else still covering?" — and only steps down if the answer is yes.

Check, then act

Run alone, Alice's script is completely ordinary: check, then update, then commit.

Loaded Database:transaction-isolation-levels.database-init

BEGIN;

-- Step 1: Check
SELECT count(*) AS other_on_call
FROM engineer
WHERE on_call = true AND name <> 'Alice';

-- Step 2: Update — nothing re-checks that count before writing. This just
-- trusts the Step 1 result and never looks at anyone else's row again.
UPDATE engineer SET on_call = false WHERE name = 'Alice';

COMMIT;

SELECT name, on_call FROM engineer ORDER BY name;

The race

Now imagine Bob's transaction interleaved with Alice's, in this order:

Alice starts her transaction and runs Step 1: Check — sees 1 (Bob is on call).
Bob starts his own transaction and runs his Step 1: Check — sees 1 (Alice is on call).
Bob runs Step 2: Update and commits — Bob is off call.
Alice, still holding her Step 1 answer, runs Step 2: Update and commits — Alice is off call too.

This playground can't run two sessions at once — the engine underneath is single-connection — so the diagram and scripts below show what two real, concurrent sessions would do, the same way the Postgres docs present two-session examples: as a script to read, not to run.

READ UNCOMMITTED / READ COMMITTED

Both behave the same way here: neither Alice nor Bob ever look again after their first check, so both go off call.

-- SESSION A (Alice)
BEGIN ISOLATION LEVEL READ UNCOMMITTED;

-- Step 1: Check — gets its own fresh snapshot, taken right now (READ
-- COMMITTED's per-statement guarantee).
SELECT count(*) AS other_on_call
FROM engineer
WHERE on_call = true AND name <> 'Alice';
-- => 1  (Bob is on call, looks safe to go off call)

-- Meanwhile, SESSION B (Bob) runs the identical two steps for himself and
-- commits FIRST:
--
--   SESSION B (Bob):
--   BEGIN ISOLATION LEVEL READ UNCOMMITTED;
--   -- Step 1: Check
--   SELECT count(*) AS other_on_call
--   FROM engineer
--   WHERE on_call = true AND name <> 'Bob';
--   -- => 1  (Alice is on call, looks safe to go off call)
--   -- Step 2: Update
--   UPDATE engineer SET on_call = false WHERE name = 'Bob';
--   COMMIT;

-- Step 2: Update — gets its own fresh snapshot too, same as Step 1 — but
-- it only writes Alice's row and never reads on_call for anyone else, so
-- Bob's already-committed change is invisible to it anyway.
UPDATE engineer SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Both commits succeed. Final state:
SELECT name, on_call FROM engineer ORDER BY name;
-- Alice    | false
-- Bob      | false
--
-- Nobody is on call. The bug isn't about visibility — Step 2 never looked
-- at Bob's row, fresh snapshot or frozen. It's about acting on a decision
-- (Step 1's result) that nothing re-verified before commit.

READ COMMITTED (default)

Same outcome — the default isolation level does not help here.

-- SESSION A (Alice)
BEGIN ISOLATION LEVEL READ COMMITTED;

-- Step 1: Check — gets its own fresh snapshot, taken right now (READ
-- COMMITTED's per-statement guarantee).
SELECT count(*) AS other_on_call
FROM engineer
WHERE on_call = true AND name <> 'Alice';
-- => 1  (Bob is on call, looks safe to go off call)

-- Meanwhile, SESSION B (Bob) runs the identical two steps for himself and
-- commits FIRST:
--
--   SESSION B (Bob):
--   BEGIN ISOLATION LEVEL READ COMMITTED;
--   -- Step 1: Check
--   SELECT count(*) AS other_on_call
--   FROM engineer
--   WHERE on_call = true AND name <> 'Bob';
--   -- => 1  (Alice is on call, looks safe to go off call)
--   -- Step 2: Update
--   UPDATE engineer SET on_call = false WHERE name = 'Bob';
--   COMMIT;

-- Step 2: Update — gets its own fresh snapshot too, same as Step 1 — but
-- it only writes Alice's row and never reads on_call for anyone else, so
-- Bob's already-committed change is invisible to it anyway.
UPDATE engineer SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Both commits succeed. Final state:
SELECT name, on_call FROM engineer ORDER BY name;
-- Alice    | false
-- Bob      | false
--
-- Nobody is on call. The bug isn't about visibility — Step 2 never looked
-- at Bob's row, fresh snapshot or frozen. It's about acting on a decision
-- (Step 1's result) that nothing re-verified before commit.

REPEATABLE READ

This is the surprising one. Freezing the snapshot doesn't stop the bug — it just means neither transaction would have noticed the change even if it had looked again. The invariant still breaks.

-- SESSION A (Alice)
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Step 1: Check — takes the transaction's ONE snapshot, frozen from this
-- moment on; every later statement in this transaction reuses it.
SELECT count(*) AS other_on_call
FROM engineer
WHERE on_call = true AND name <> 'Alice';
-- => 1  (Bob is on call, looks safe to go off call)

-- Meanwhile, SESSION B (Bob) runs the identical two steps for himself and
-- commits FIRST:
--
--   SESSION B (Bob):
--   BEGIN ISOLATION LEVEL REPEATABLE READ;
--   -- Step 1: Check
--   SELECT count(*) AS other_on_call
--   FROM engineer
--   WHERE on_call = true AND name <> 'Bob';
--   -- => 1  (Alice is on call, looks safe to go off call)
--   -- Step 2: Update
--   UPDATE engineer SET on_call = false WHERE name = 'Bob';
--   COMMIT;

-- Step 2: Update — reuses the frozen snapshot from Step 1, but that
-- makes no difference here: it only writes Alice's row and never reads
-- on_call for anyone else.
UPDATE engineer SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Both commits succeed. Final state:
SELECT name, on_call FROM engineer ORDER BY name;
-- Alice    | false
-- Bob      | false
--
-- Nobody is on call. The bug isn't about visibility — Step 2 never looked
-- at Bob's row, fresh snapshot or frozen. It's about acting on a decision
-- (Step 1's result) that nothing re-verified before commit.

SERIALIZABLE

The only level that catches it. Postgres notices both transactions read a row set the other one wrote to, and refuses the second commit.

-- SESSION A (Alice)
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Step 1: Check — same frozen snapshot as REPEATABLE READ, but Postgres
-- also starts tracking which rows this transaction's reads depended on.
SELECT count(*) AS other_on_call
FROM engineer
WHERE on_call = true AND name <> 'Alice';
-- => 1  (Bob is on call, looks safe to go off call)

-- Meanwhile, SESSION B (Bob) runs the identical two steps for himself and
-- commits FIRST:
--
--   SESSION B (Bob):
--   BEGIN ISOLATION LEVEL SERIALIZABLE;
--   -- Step 1: Check
--   SELECT count(*) AS other_on_call
--   FROM engineer
--   WHERE on_call = true AND name <> 'Bob';
--   -- => 1  (Alice is on call, looks safe to go off call)
--   -- Step 2: Update
--   UPDATE engineer SET on_call = false WHERE name = 'Bob';
--   COMMIT;  -- succeeds, Bob is now off call

-- Step 2: Update — still never reads Bob's row directly. SERIALIZABLE
-- isn't relying on that: it tracks that Step 1 depended on rows Bob just
-- wrote to, and checks that dependency at commit time.
UPDATE engineer SET on_call = false WHERE name = 'Alice';
COMMIT;
-- ERROR:  could not serialize access due to read/write dependencies
--         among transactions
-- SQLSTATE: 40001
--
-- Postgres refused this commit — not because Step 2 read something
-- stale, but because it detected the read/write conflict between the two
-- transactions as a whole. The app is expected to catch 40001 and retry —
-- on retry, Alice's Step 1 would see Bob is already off call and correctly
-- refuse to go off call herself.

Choosing a level

Stricter isolation doesn't just add guarantees — it adds cost, usually in the form of more aborted transactions your application has to retry.

Isolation level	Protects against	Cost
READ UNCOMMITTED	Nothing extra — identical to READ COMMITTED in Postgres.	None. It's free, but also pointless to ask for.
READ COMMITTED (default)	Nothing beyond "don't read uncommitted data."	Cheapest: no extra locking, never aborts a transaction for isolation reasons.
REPEATABLE READ	Non-repeatable reads, phantom reads.	Holds a snapshot open for the whole transaction; a concurrent write to a row you also wrote aborts your transaction immediately.
SERIALIZABLE	Everything above, plus write skew — a real "as if run one at a time" guarantee.	Highest overhead: tracks read/write dependencies across every concurrent SERIALIZABLE transaction. More aborts (SQLSTATE 40001) — your app must catch and retry them.

Default to READ COMMITTED unless you have a specific reason not to — it's what most queries run at anyway, including in this lesson's own scenario, where it made no difference. Reach for REPEATABLE READ when you need a consistent multi-statement read, like a report or an export. Reach for SERIALIZABLE only when you have a genuine cross-row invariant like this lesson's write-skew case — and only if your application actually retries on 40001, since without that, SERIALIZABLE just trades silent corruption for silent failure.

What We Learned

READ COMMITTEDPostgres's default. Each statement gets a fresh view of committed data, even inside an open transaction.
REPEATABLE READThe whole transaction sees one snapshot taken at its first query — later commits from other transactions stay invisible to it.
SERIALIZABLEAdds real conflict detection on top of REPEATABLE READ's snapshot: if the result wouldn't match some one-at-a-time ordering of the transactions, Postgres rejects one of the commits instead of allowing it.
Write skewTwo transactions each read a shared fact, each act on what they read, and each commit successfully — but only because neither saw the other's change. Individually valid, together wrong.
SQLSTATE 40001The error code for a SERIALIZABLE conflict. An application using SERIALIZABLE is expected to catch it and retry the transaction.
SET TRANSACTION ISOLATION LEVELSets the isolation level for the current transaction; also settable as BEGIN ISOLATION LEVEL ....