Transaction isolation levels in Postgres — what anomalies does each level prevent?

Q: Transaction isolation levels in Postgres — what anomalies does each level prevent?

**Target Audience:** Developers who know the four standard isolation levels and want to know exactly how PostgreSQL behaves at each one, and candidates preparing for "what does your app have to do at SERIALIZABLE?". **Difficulty:** Medium **Engines:** PostgreSQL 16.15. Every two-session timeline on this page was run with two concurrent `psql` sessions against a scratch database seeded from the schema above, and the values and error messages quoted are what those sessions printed. The two runnable examples use SQLite 3.45.0 (sql.js 1.10.2) to show end states; SQLite itself is always serializable. The level-independent basics (what a dirty read or a phantom is) are on [What are transaction isolation levels?](/interview-question/what-are-transaction-isolation-levels) > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced b

The problem

Question presented to candidate: "In PostgreSQL specifically, walk me through READ COMMITTED, REPEATABLE READ and SERIALIZABLE. For each one, which anomalies can still happen, what does the database do when two transactions collide, and what does my application have to handle?"

What a strong answer should cover:

PostgreSQL implements three distinct levels; READ UNCOMMITTED behaves as READ COMMITTED, so dirty reads never happen
READ COMMITTED (default): a new snapshot per statement; non-repeatable reads, phantoms, lost updates in read-modify-write code and write skew are possible; a blocked UPDATE waits, then re-checks its WHERE on the newest row version
REPEATABLE READ: one snapshot taken at the first statement (not at BEGIN); no non-repeatable reads and, stricter than the standard, no phantoms; a concurrent update of the same row aborts with SQLSTATE 40001; write skew still possible
SERIALIZABLE: Serializable Snapshot Isolation (SSI, since 9.1) tracks read/write dependencies with SIRead predicate locks and aborts one transaction of a dangerous pattern with 40001, so write skew is prevented; reads never block
Application duties: retry the whole transaction on 40001, keep serializable transactions short, use READ ONLY DEFERRABLE for long reports, or use explicit locks (FOR UPDATE) at READ COMMITTED instead

Clarifying questions expected:

Which invariant are we protecting — one row (counter, balance) or a rule across several rows (on-call roster, no overlapping bookings)?
Can the application retry a transaction safely (no external side effects inside it)?

Code / implementation expected: Optional — a two-session timeline for write skew, and the fix with SERIALIZABLE plus retry or with FOR UPDATE.

Schema used by the examples:

isolation
transactions
postgres

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know the four standard isolation levels and want to know exactly how PostgreSQL behaves at each one, and candidates preparing for "what does your app have to do at SERIALIZABLE?". Difficulty: Medium Engines: PostgreSQL 16.15. Every two-session timeline on this page was run with two concurrent psql sessions against a scratch database seeded from the schema above, and the values and error messages quoted are what those sessions printed. The two runnable examples use SQLite 3.45.0 (sql.js 1.10.2) to show end states; SQLite itself is always serializable. The level-independent basics (what a dirty read or a phantom is) are on What are transaction isolation levels?

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you al

The solution is hidden so you can try first

15 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLWhat write skew leaves behind: both guarded updates applied, nobody on call

SQLThe same two requests run one after the other: the guard keeps one doctor on call

PostgreSQL: a read under SERIALIZABLE leaves an SIRead predicate lock

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- read the rows the rule depends on (PERFORM runs the query and discards the result)
DO $$ BEGIN PERFORM COUNT(*) FROM doctors WHERE on_call = 1; END $$;
-- the predicate lock that read left behind
SELECT l.locktype, c.relname, l.mode
FROM pg_locks AS l
JOIN pg_class AS c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid() AND l.mode = 'SIReadLock'
ORDER BY c.relname;
COMMIT;

PostgreSQL: the lock-based fix at READ COMMITTED locks every on-call row first

BEGIN;
SELECT name FROM doctors WHERE on_call = 1 ORDER BY name FOR UPDATE;
UPDATE doctors SET on_call = 0 WHERE name = 'Alice';
COMMIT;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.