Isolation levels in Postgres
READ COMMITTED, REPEATABLE READ, and SERIALIZABLE are escalating promises about which anomalies concurrent transactions can see. Only SERIALIZABLE stops write skew — and it makes you write retry logic.
A hospital scheduling system enforced one rule: at least one doctor must stay on call. Two doctors, each on call, both clicked “go off duty” at the same second. Each transaction read “2 on call, fine” and removed itself. Result: zero doctors on call — a rule that every individual transaction respected, broken by their interleaving. No bug in either query; the bug was the isolation level. This anomaly has a name — write skew — and exactly one level stops it.
The levels as a ladder of promises
When you pick an isolation level you’re not tuning speed — you’re choosing which class of bugs you accept. Pick too low and you get silent data corruption; pick too high and you write retry loops. Knowing the exact boundary of each level is what separates a senior who prevents incidents from one who debugs them afterward.
An isolation level is not a performance dial; it is a contract about which anomalies concurrent transactions are allowed to observe. Postgres offers three usable levels (it accepts READ UNCOMMITTED but treats it as READ COMMITTED — dirty reads are never possible in Postgres).
- READ COMMITTED (the default) — you only ever see committed data, but each statement takes a fresh snapshot at statement start. So within one transaction, two
SELECTs of the same row can return different values if another transaction committed in between. Prevents: dirty reads. Allows: non-repeatable reads, phantoms, lost updates, write skew. - REPEATABLE READ — the transaction takes one snapshot at its first statement and uses it for the whole transaction. Every read sees the database as of that instant. Prevents: dirty reads, non-repeatable reads, and (in Postgres) phantoms. Allows: write skew. A write that conflicts with another transaction’s commit aborts with a serialization error.
- SERIALIZABLE — behaves as if every transaction ran one after another in some serial order. Implemented via Serializable Snapshot Isolation (SSI): it tracks read/write dependencies and aborts a transaction whose commit would create a non-serializable schedule. Prevents: everything, including write skew. Cost: more serialization failures you must retry.
Per-statement snapshot vs transaction snapshot
The single most consequential difference between the two common levels is when the snapshot is taken.
-- READ COMMITTED: each statement re-snapshots.
BEGIN; -- default level
SELECT qty FROM inventory WHERE sku = 'A'; -- sees 10
-- ... another session commits qty = 3 here ...
SELECT qty FROM inventory WHERE sku = 'A'; -- now sees 3 (non-repeatable read)
COMMIT;-- REPEATABLE READ: one snapshot for the whole transaction.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT qty FROM inventory WHERE sku = 'A'; -- sees 10
-- ... another session commits qty = 3 here ...
SELECT qty FROM inventory WHERE sku = 'A'; -- still sees 10 (stable read)
COMMIT;READ COMMITTED is right for short OLTP statements where you want the freshest committed data. REPEATABLE READ is right for a report or multi-statement read that must be internally consistent — a balance sheet that doesn’t shift under you mid-query.
The internals — how snapshots, xmin/xmax, and visibility actually work — are the databases track’s MVCC chapter; here we stay at the SQL contract level. Cross-reference databases/04-mvcc-isolation/03-hot-updates-and-isolation-levels for the row-version mechanism and databases/04-mvcc-isolation/06-ssi-and-production-tuning for how SSI tracks conflicts.
Write skew and why only SERIALIZABLE catches it
Write skew is the anomaly that surprises seniors. Two transactions read an overlapping set of rows, each checks an invariant that currently holds, then each writes a different row. Neither write conflicts with the other (different rows), so REPEATABLE READ’s first-updater-wins check never fires — but together they break the invariant.
-- The on-call invariant, made safe with SERIALIZABLE:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true; -- must stay >= 1
-- app checks count >= 2 before allowing this:
UPDATE doctors SET on_call = false WHERE id = 17;
COMMIT; -- if a concurrent txn broke serializability, this raises 40001Under SERIALIZABLE one of the two concurrent transactions commits; the other gets ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001). Your application must catch 40001 and retry the whole transaction. On retry it re-reads count = 1 and correctly refuses. Without retry logic, SERIALIZABLE just converts silent corruption into visible errors your users see.
▸Edge cases
SERIALIZABLE is not free and not magic. SSI keeps predicate-lock bookkeeping (SIReadLocks) for every read, which costs memory and can escalate from row to page to relation granularity under load, raising false-positive aborts. The production rule of thumb: use SERIALIZABLE for the specific transactions that carry a cross-row invariant (booking, inventory reservation, on-call rules), keep retry logic with capped exponential backoff around them, and keep the rest of your traffic on READ COMMITTED. Reserving rows with an explicit SELECT ... FOR UPDATE lock is often the cheaper, more predictable alternative — the next lesson.
A transaction at REPEATABLE READ runs two identical SELECTs of inventory.qty for the same sku, and another session commits a change in between. What does the second SELECT return?
You need to guarantee 'at least one row in a set stays true' across concurrent transactions that each modify a different row. Which level, and what must you add?
Fill in the blank: the anomaly where two transactions each read an overlapping set, each verify an invariant that holds, then each write a different row and together break the invariant, is called write _______.
- 01What is the key behavioural difference between READ COMMITTED and REPEATABLE READ?
- 02What is write skew, and which isolation level is required to prevent it?
- 03If you switch a transaction to SERIALIZABLE, what new responsibility does your application code take on?
An isolation level is a contract over which anomalies concurrent transactions may observe, not a speed setting. READ COMMITTED, Postgres’s default, takes a fresh snapshot per statement: it blocks dirty reads but allows non-repeatable reads, phantoms, lost updates, and write skew. REPEATABLE READ takes one snapshot for the whole transaction, giving stable reads and (in Postgres) no phantoms, but it still allows write skew because two transactions writing different rows never conflict. SERIALIZABLE uses Serializable Snapshot Isolation to behave as if transactions ran one at a time; it is the only level that prevents write skew, at the price of more serialization failures (SQLSTATE 40001) that your application must catch and retry. The production discipline: keep most traffic on READ COMMITTED, escalate only the transactions carrying a cross-row invariant to SERIALIZABLE with retry logic, or reserve rows explicitly with the locks you’ll learn next. The MVCC machinery behind all of this — snapshots, row versions, SSI predicate locks — lives in the databases track’s MVCC chapter; this lesson is the SQL-level promise. Now when you see a bug report where “two concurrent updates broke an invariant neither one violated alone,” you’ll reach for SERIALIZABLE and a retry loop rather than adding another lock row.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.