PostgreSQL transaction isolation levels in practice

Updated July 5, 2026

PostgreSQL implements three distinct isolation levels — Read Committed, Repeatable Read and Serializable. (READ UNCOMMITTED exists for SQL-standard compatibility but behaves exactly like Read Committed: PostgreSQL never shows you uncommitted data.) The differences only matter under concurrency, which is exactly when they're hardest to debug — so it pays to know them before the incident.

Read Committed: the default, and its blind spot

Each statement sees a snapshot of the database as of the moment that statement began. Two consequences:

• Two identical SELECTs inside one transaction can return different results if someone commits in between (non-repeatable read).
• The classic lost update: two transactions both read a balance of 100, both compute 100 − 10, both write 90. One withdrawal vanishes. Read Committed does not prevent this pattern when the read and the write are separate statements.

The idiomatic fixes at this level:

-- make the read-modify-write atomic in one statement:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- or lock the row at read time:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

A useful detail: when an UPDATE at Read Committed finds a row already changed by a concurrent committed transaction, it re-evaluates its WHERE clause on the new version and proceeds. You don't get an error — which is convenient, and occasionally the source of very subtle bugs.

Repeatable Read: one snapshot for the whole transaction

The snapshot is taken at the first query and kept until commit: every statement sees the same data. In PostgreSQL this also prevents phantom reads (stronger than the SQL standard requires). The price appears on writes:

ERROR:  could not serialize access due to concurrent update

If you try to modify a row that a concurrent transaction changed and committed after your snapshot was taken, PostgreSQL cannot pretend both orders happened — it aborts you. Your application must be prepared to retry the whole transaction. Repeatable Read is the natural level for consistent multi-query reads: reports, exports, backups (pg_dump runs at this level).

Serializable: as if transactions ran one at a time

Serializable in PostgreSQL is Repeatable Read plus SSI (serializable snapshot isolation): the engine tracks read/write dependencies between concurrent transactions and aborts one whenever a cycle would make the outcome impossible under any serial order. It catches anomalies Repeatable Read cannot — the textbook case is write skew:

-- Two doctors, rule: at least one must stay on call.
-- T1: SELECT count(*) FROM oncall;  -- sees 2, so it's safe to leave
-- T2: SELECT count(*) FROM oncall;  -- sees 2, so it's safe to leave
-- T1: DELETE FROM oncall WHERE doctor = 'alice'; COMMIT;
-- T2: DELETE FROM oncall WHERE doctor = 'bob';   COMMIT;
-- Repeatable Read: both commit, zero doctors on call.
-- Serializable: one of them gets error 40001 and must retry.

Costs: some CPU and memory for dependency tracking, and — importantly — false positives: transactions can be aborted "just in case". All transactions in the workload must run Serializable for its guarantees to hold, and every one of them needs retry logic.

Retrying correctly

• Retry on SQLSTATE 40001 (serialization_failure) and 40P01 (deadlock_detected).
• Retry the entire transaction, including its reads — the values you read the first time are exactly what may no longer be true. Re-running only the failed statement is wrong.
• Keep retried transactions short and bounded (limited attempts, small backoff). A transaction that reads half the database will keep colliding forever.
• Don't wrap side effects (emails, HTTP calls) inside a transaction you intend to retry.

Choosing in practice

Set the level per transaction (BEGIN ISOLATION LEVEL SERIALIZABLE) or per session; mixing levels is fine except that, as noted, Serializable's guarantees require all related transactions to use it.