How does PostgreSQL implement transaction isolation levels?
PostgreSQL offers Read Committed (the default), Repeatable Read and Serializable. Dirty reads never happen, even if you ask for Read Uncommitted. Read Committed takes a new snapshot per statement, Repeatable Read keeps one snapshot for the whole transaction, and Serializable adds checks that abort a transaction if the result could not have come from running transactions one at a time.
Explain it simply
Imagine reading the IRCTC seat chart for a train. Under Read Committed, every time you glance at the chart you see the latest confirmed bookings. Under Repeatable Read, you take a photo of the chart when you start and keep looking at the photo, so it never changes under you. Under Serializable, the system also checks at the end: "Would this have worked if everyone had booked one after another?" If not, your booking is rejected and you try again.
Isolation is about how much one transaction sees of other transactions running at the same time.
Example
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT seats_left FROM trains WHERE train_no = 12951; -- 10
-- Session B (autocommit)
UPDATE trains SET seats_left = 9 WHERE train_no = 12951;
-- Session A
SELECT seats_left FROM trains WHERE train_no = 12951; -- still 10 (same snapshot)
UPDATE trains SET seats_left = seats_left - 1 WHERE train_no = 12951;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK; -- the application should retry the whole transactionUnder Read Committed, the second SELECT in session A would return 9, and the UPDATE would succeed using the latest value.
| Level in PostgreSQL | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|---|
| Read Uncommitted (behaves as Read Committed) | No | Possible | Possible | Possible |
| Read Committed (default) | No | Possible | Possible | Possible |
| Repeatable Read | No | No | No | Possible |
| Serializable | No | No | No | No |
How it works
All levels are built on MVCC snapshots.
- Read Committed: each statement gets a fresh snapshot, so it sees everything committed before that statement began. If an UPDATE finds that its target row was changed by a transaction that has since committed, it re-checks the WHERE condition on the newest version and updates that.
- Repeatable Read: one snapshot for the whole transaction. This is stricter than the SQL standard requires, because it also prevents phantom reads. If the transaction tries to update a row that someone else changed after the snapshot, it fails with a serialization error instead of overwriting their change.
- Serializable: uses Serializable Snapshot Isolation (SSI). It runs like Repeatable Read but also tracks which rows each transaction read. If two transactions read and write in a pattern that no serial order could produce, one of them is aborted with SQLSTATE
40001.
Real-world use
- Most web apps run on Read Committed and use
SELECT ... FOR UPDATEor atomic statements likeSET balance = balance - 100for money and stock counts. - Repeatable Read suits reports that run several queries and need them all to agree, such as month-end totals.
- Serializable suits complex business rules, for example "at least one doctor must be on call", where two concurrent changes can each look valid alone.
Common mistakes
- Saying PostgreSQL can show dirty reads. It cannot, at any level.
- Using Repeatable Read or Serializable without retry logic. Serialization failures are expected, and the client must rerun the whole transaction.
- Reading a value into the application, changing it in code, then writing it back under Read Committed. Two users can overwrite each other (a lost update). Use an atomic UPDATE or lock the row with
FOR UPDATE.
What to say in the interview
"PostgreSQL supports Read Committed, Repeatable Read and Serializable, and never allows dirty reads; Read Uncommitted behaves like Read Committed. Read Committed is the default and takes a new snapshot per statement. Repeatable Read keeps one snapshot for the whole transaction, which in PostgreSQL also prevents phantoms, and it raises a serialization error on conflicting updates. Serializable uses SSI to detect anomalies like write skew and aborts one transaction. With the two higher levels the application must catch error 40001 and retry."
Follow-up questions
- What is write skew, and which level prevents it?
- What is the difference between
FOR UPDATEandFOR SHARE? - How would you write retry logic for serialization failures?
Go deeper
Read these once the basics above feel easy.
Write skew is the classic anomaly Repeatable Read allows. Two doctors are on call. Each transaction checks "are at least two on call?", sees yes, and takes itself off call. They update different rows, so there is no write conflict, and both commit, leaving nobody on call. Under Serializable, SSI notices the read-write dependency between the two transactions and aborts one. SSI uses predicate locks that never block anyone; they only record what was read, so Serializable adds some overhead and more retries but not extra waiting. Declaring read-only transactions with READ ONLY helps SSI reduce false aborts.