Isolation levels in PostgreSQL

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Pick the fix

A report reads SUM(amount) twice in one transaction and gets two different totals. Smallest correct change in PostgreSQL?

OptionsRun the report at REPEATABLE READ so both reads use one snapshotAdd FOR UPDATE to both SELECTsUse READ UNCOMMITTEDRun both SELECTs in one statement with a CTE

Hint 1 of 1

How often does READ COMMITTED take a snapshot?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Run the report at REPEATABLE READ so both reads use one snapshot

Correct. One snapshot per transaction makes reads repeatable, and read-only transactions never get 40001 at this level.

READ COMMITTED takes a new snapshot for every statement, so two SELECTs can see different data. REPEATABLE READ keeps one snapshot for the whole transaction.

Why not the others?

B Add FOR UPDATE to both SELECTs. Aggregates can't lock rows, and blocking writers to run a report is a heavy price.
C Use READ UNCOMMITTED. PostgreSQL treats it as READ COMMITTED; nothing changes.
D Run both SELECTs in one statement with a CTE. Works for this pair, but isn't the general fix for multi-statement reports.

30-second answer

The standard defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, so dirty reads never happen. READ COMMITTED, the default, takes a new snapshot per statement. REPEATABLE READ keeps one snapshot for the whole transaction, which also blocks phantoms but still allows write skew. SERIALIZABLE adds conflict tracking and aborts transactions with 40001 when the result couldn't come from some serial order.

Practise saying it · 45 s. No recording, just a timer

Walk through the four isolation levels.

Say your answer out loud, then check which of these points you covered:

RU = RC in PostgreSQL
RC: snapshot per statement
RR: snapshot per transaction, write skew possible
SER: SSI, 40001 aborts
Pick by the anomalies you can't tolerate

Deep dive

READ COMMITTED: two SELECTs in one transaction can see different data; an UPDATE that finds a row changed by a committed transaction re-checks it against the newest version.

REPEATABLE READ: if two transactions update the same row, the second one fails with 40001 'could not serialize access due to concurrent update'.

SERIALIZABLE: uses predicate locks (SIReadLock) with no blocking; the cost is false-positive aborts and some memory.

MySQL InnoDB defaults to REPEATABLE READ and behaves differently (gap locks, locking reads), so don't carry rules between engines.

Level (PostgreSQL)	Dirty read	Non-repeatable	Phantom	Write skew
READ COMMITTED	No	Yes	Yes	Yes
REPEATABLE READ	No	No	No	Yes
SERIALIZABLE	No	No	No	No

Set the level per transaction Ran on PostgreSQL 17.11

SQL · PostgreSQL 17

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
COMMIT;

Common mistakes

Quoting the SQL-standard table as if it described PostgreSQL.
Using REPEATABLE READ or SERIALIZABLE without retry logic.
Setting the level after the first query in the transaction (it must be set before).

Senior take & follow-ups

Describe what each level actually does in the engine you use, and tie the choice to the anomalies your workload can tolerate.

Follow-up questions

Think of your answer first, then open the hint.

How do you set the level for one transaction?
Show hint
BEGIN ISOLATION LEVEL SERIALIZABLE; or SET TRANSACTION ... before the first query.
Is SERIALIZABLE slow?
Show hint
It doesn't block more, but it aborts more; keep transactions short and retry.

Suggest a fix if something here is wrong or out of date.