PostgreSQL Transaction Isolation Levels — Read Committed, Repeatable Read, Serializable
Fast recall
Quick revision
Core answer
40001 on conflict, so applications must retry.Whether you understand MVCC as the mechanism beneath every isolation level, know which anomalies each level prevents, recognize write skew as the signature problem Serializable solves, and can configure isolation correctly in Spring Boot with retry logic.
Using SERIALIZABLE for every transaction because it 'feels safer' — it forces a retry loop for every serialization_failure, which is significant overhead, and Read Committed is the correct default for the vast majority of OLTP workloads.
Distinguish PostgreSQL Repeatable Read from MySQL Repeatable Read (no gap locks; snapshot also blocks phantoms), explain SSI vs lock-based Serializable, describe long-running transaction bloat, and show FOR UPDATE vs advisory locks as pessimistic-locking tools.
Interview-ready guide
Interview answer
PostgreSQL supports three isolation levels on top of MVCC: Read Committed, Repeatable Read, and Serializable. The headline property of MVCC is that readers never block writers and writers never block readers — each transaction reads from a snapshot instead of taking shared locks.
Read Committed — the default
- Each statement gets a fresh snapshot of committed data, so two reads of the same row in one transaction can return different values
- This is the right default for most OLTP workloads; use FOR UPDATE or optimistic locking inside a Read Committed transaction when you need a read-then-write to be atomic
Repeatable Read
- Takes one snapshot at transaction start and holds it for the entire transaction
- Unlike the SQL standard and unlike MySQL, PostgreSQL Repeatable Read also prevents phantom reads because the snapshot cannot see rows inserted after it
- Perfect for reports that run multiple queries and need them all to see the same view of the world
Serializable
- Prevents write skew, the anomaly where two transactions read overlapping state, each makes a valid independent change, and both commit into a state no serial execution could produce
- PostgreSQL implements it with Serializable Snapshot Isolation, which detects dangerous read-write dependencies and aborts one transaction with SQLSTATE 40001
- Every caller must have a retry loop — in Spring,
@Retryable(CannotSerializeTransactionException.class)with exponential backoff
The operational pitfall
- Long-running transactions hold back the xmin horizon and prevent VACUUM from reclaiming dead row versions, which causes table and index bloat
- Keep transactions short, configure
idle_in_transaction_session_timeout, and monitorpg_stat_activityfor stuck sessions
PostgreSQL Transaction Isolation Levels — Read Committed, Repeatable Read, Serializable
Direct Answer
PostgreSQL supports Read Committed (the default), Repeatable Read, and Serializable. All three are implemented on MVCC, so readers never block writers and writers never block readers. Read Committed takes a new snapshot per statement; Repeatable Read takes one snapshot at transaction start and — unlike the SQL standard — also prevents phantom reads. Serializable uses Serializable Snapshot Isolation (SSI) to detect dangerous read-write dependency cycles and aborts one transaction with 40001 on conflict, so applications must retry.
Code Example
-- Classic write skew: two on-call doctors. Policy says at least one must stay on call.
-- Table:
CREATE TABLE doctors (
id BIGINT PRIMARY KEY,
on_call BOOLEAN NOT NULL
);
INSERT INTO doctors VALUES (1, TRUE), (2, TRUE);
-- Two concurrent transactions under Repeatable Read
-- Both see two on-call doctors and each decides it is safe to go off-call
-- Session A -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call; SELECT count(*) FROM doctors WHERE on_call;
-- returns 2 -- returns 2
UPDATE doctors SET on_call = FALSE WHERE id = 1; UPDATE doctors SET on_call = FALSE WHERE id = 2;
COMMIT; COMMIT;
-- Both commit. Result: zero doctors on call. Write skew achieved under RR.
-- Same scenario under Serializable — SSI aborts one
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... same steps ...
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT: The transaction might succeed if retried.
-- SQLSTATE: 40001
-- FOR UPDATE — pessimistic alternative under Read Committed
-- Serializes access to the specific rows without using SERIALIZABLE
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Row-level exclusive lock held until COMMIT; prevents concurrent modification
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Inspect MVCC state
SELECT xmin, xmax, * FROM accounts WHERE id = 1;
-- Find long transactions that block VACUUM
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;
-- Guardrail — terminate idle-in-transaction sessions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();
Common Mistakes & Pitfalls
Using SERIALIZABLE for every transaction because it 'feels safer' — it forces a retry loop for every serialization_failure, which is significant overhead, and Read Committed is the correct default for the vast majority of OLTP workloads.
Check your understanding
Answer these without looking back at the article.
- How does PostgreSQL's Repeatable Read differ from MySQL's Repeatable Read, and why does MySQL still need gap locks?
- What is write skew and why is Serializable the only level that prevents it?
- What does Serializable Snapshot Isolation (SSI) detect, and how do you handle SQLSTATE 40001 in Spring?
- Why do long-running transactions cause table bloat, and how do you mitigate it?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.