Isolation Levels: Read Committed vs Serializable

Medium

Interview Question: "Which isolation level would you use in a high-traffic payment system, and why? What are the trade-offs between Read Committed and Serializable, and which anomalies does each one actually prevent?"

An isolation level is not a performance setting. It is a contract about which anomalies your transaction is allowed to observe — and the common advice, "use Read Committed with SELECT … FOR UPDATE because Serializable is too slow", gets both halves wrong. It misstates what Serializable does, and it recommends a substitute that does not cover the anomaly Serializable exists for.

The anomalies, in the order they bite

Dirty read

👀T1 reads T2's uncommitted row

T2 then rolls back — T1 acted on data that never existed

Non-repeatable read

🔁T1 reads a row twice, gets two values

T2 committed an UPDATE in between

Phantom read

👻T1 runs the same WHERE twice

T2 INSERTed a row that now matches — the result set grew

Write skew

🪓T1 and T2 read the same rows, write different ones

each is valid alone; together they break an invariant

The first three are the textbook three and every database discussion covers them. Write skew is the one that actually costs money, and it is the one almost every answer to this question omits.

What PostgreSQL actually promises

The SQL standard defines the levels by which anomalies they permit. PostgreSQL is stricter than the standard at every level, which matters because the textbook table is not the table you are working with.

	Dirty read	Non-repeatable read	Phantom read	Write skew	Lost update
Read Uncommitted	never¹	possible	possible	possible	possible
Read Committed (default)	never	possible	possible	possible	possible²
Repeatable Read	never	never	never³	possible	never⁴
Serializable	never	never	never	never	never

¹ PostgreSQL has no Read Uncommitted — asking for it gives you Read Committed. There is no mechanism to read an uncommitted row.

² A plain read-then-write loses updates. A guarded UPDATE … WHERE balance >= 100 does not, because UPDATE re-reads the row under a row lock and re-evaluates the predicate.

³ Stricter than the standard. PostgreSQL's Repeatable Read is true snapshot isolation: the whole transaction sees one frozen snapshot, so new rows cannot appear.

⁴ First updater wins; the loser gets could not serialize access due to concurrent update and must retry.

So the real choice in PostgreSQL is narrower than the four-row table suggests. Read Committed and Serializable differ in exactly one practically important way: write skew.

Write skew: the anomaly FOR UPDATE does not fix

The standard example. A hospital requires at least one doctor on call at all times. Two doctors are on call; both are tired; both click "go off call" simultaneously.

Transaction A (Alice)

1️⃣SELECT count(*) FROM doctors WHERE on_call

returns 2 — fine, I can leave

2️⃣UPDATE doctors SET on_call = false WHERE id = 'alice'

writes her own row

3️⃣COMMIT

valid in isolation

Transaction B (Bob)

1️⃣SELECT count(*) FROM doctors WHERE on_call

also returns 2 — fine, I can leave

2️⃣UPDATE doctors SET on_call = false WHERE id = 'bob'

writes his own row

3️⃣COMMIT

also valid in isolation

both commit — zero doctors on call, and neither transaction did anything wrong

Now look at why locking does not save you. Each transaction writes a different row. Alice locks Alice; Bob locks Bob. The locks never collide, so there is nothing to wait for and nothing to detect. Adding FOR UPDATE to step 1 locks the rows each transaction read — and at the moment they read, both rows exist and both are on call, so both locks are granted and both transactions proceed exactly as before.

This is the shape that makes write skew different from a lost update. A lost update is two transactions fighting over one row, and a row lock resolves it. Write skew is two transactions agreeing about a set of rows and then each changing a different member of it. There is no single row to lock.

note

FOR UPDATE can prevent write skew, but only if you lock something both transactions must touch — a parent row, a shift record, a per-ward lock row. That is the "materialise the conflict" technique, and it works by inventing a shared row so the problem becomes a lost update. It is a real option, and it is a manual, easy-to-forget one: every new code path has to remember to take the same lock.

Serializable is optimistic, not serial

The widespread claim is that Serializable makes transactions run one at a time — "the bank manager freezes the building" — and that it is therefore unusable under load. That describes two-phase locking, which is how some databases implement it. It is not how PostgreSQL implements it.

PostgreSQL uses Serialisable Snapshot Isolation (SSI). Transactions run concurrently, at full speed, taking no additional blocking locks. The engine tracks read/write dependencies between them and, when it spots a pattern that could produce a result no serial order could have produced, it aborts one of them with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).

Two-phase locking — the mental model people have

⏳readers block writers, writers block readers

contention scales with traffic, even when nothing conflicts

🐢throughput falls under load

transactions queue behind each other

SSI — what PostgreSQL actually does

🏃no extra blocking at all

transactions never wait on each other's reads

💥aborts on detected conflict, SQLSTATE 40001

cost is a retry, paid only when there is a real conflict

So the honest cost of Serializable is not latency. It is that every transaction needs a retry loop, because any of them can fail at commit through no fault of its own — plus some bookkeeping memory for predicate locks, and a higher abort rate on genuinely contended rows. Declining it for that reason is entirely reasonable. Declining it because "serialisable means serial" means declining it in the cases where it would have been both correct and cheap.

So which do you use

Not one answer for the whole database. The level is per transaction, and the question to ask is what shape is the invariant I am protecting?

Single-row invariant

✅Read Committed + guarded UPDATE

balance >= amount, stock >= qty, status = 'placed'

🔒the WHERE clause is the lock

check and write in one statement; no read-then-write window

Cross-row invariant

✅Serializable + retry loop

at least one doctor on call, total exposure under a limit, no double-booked room

🔁catch 40001, retry the whole transaction

the retry is the price; there is no cheaper correct option

A payment system is mostly the left-hand column, which is why Read Committed is the right default for it — but "mostly" is not "entirely". A portfolio-level margin rule that reads many positions to authorise one write is a cross-row invariant, and no amount of row locking makes it safe.

For the mechanics of the locks themselves — FOR UPDATE, NOWAIT, SKIP LOCKED, version columns and retry storms — see Optimistic vs Pessimistic Locking. This page is about which anomalies the level permits; that one is about the tools you reach for inside a level.

Code: reproducing write skew in two psql sessions

Setup

CREATE TABLE doctors (id TEXT PRIMARY KEY, on_call BOOLEAN NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

Session 1

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;        -- 2
UPDATE doctors SET on_call = false WHERE id = 'alice';
-- do not commit yet

Session 2

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;        -- 2
UPDATE doctors SET on_call = false WHERE id = 'bob';
COMMIT;                                            -- succeeds

Back in session 1

COMMIT;                                            -- also succeeds
SELECT count(*) FROM doctors WHERE on_call;        -- 0  ← the invariant is broken

Re-run the whole thing with BEGIN ISOLATION LEVEL SERIALIZABLE and the second COMMIT fails with SQLSTATE 40001 instead. Nothing blocked, nothing waited — one transaction was simply told to try again.

Say this in the interview

• Isolation levels are a contract about observable anomalies, not a speed dial.
• In PostgreSQL the practical gap between Read Committed and Serializable is write skew, because Repeatable Read already gives you snapshot isolation and therefore no phantom reads.
• SELECT … FOR UPDATE fixes lost updates, not write skew — two transactions writing different rows never contend for a lock.
• PostgreSQL's Serializable is optimistic (SSI): no extra blocking, aborts on conflict, and the real cost is a retry loop rather than latency.
• Choose per transaction by the shape of the invariant: single-row predicates go in a guarded UPDATE under Read Committed; cross-row invariants want Serializable.