Skip to content

Isolation levels and anomalies in Postgres

Postgres never shows uncommitted rows; the level decides when your snapshot is taken, and whether anyone checks what you decided from it.

Predict first

Before you read: predict

ExamHall books seats for an in-person sitting in room H3, which holds 30; 29 are booked. A booking transaction counts the room's bookings, inserts one booking if the count is under 30, and commits. Two bookings run at REPEATABLE READ in this exact order: B begins and counts; A begins, counts, inserts and commits; B counts again and inserts if its second count is under 30; B commits.

How does B's booking end?

Pick one

The 30-second version

An isolation level decides which transactions' commits your statements can see. Postgres never shows uncommitted changes. Read Committed gives each statement a fresh snapshot, so a reread can change. Repeatable Read keeps the first statement's snapshot throughout, but checks only rows you write, so two transactions deciding from stale reads can both commit. Serializable catches that, aborting one with 40001.

Postgres never shows uncommitted rows; the level decides when your snapshot is taken, and whether anyone checks what you decided from it.

How it works

Step 1 of 12

An anomaly is a result that no one-at-a-time order of the same transactions could produce. An isolation level promises which anomalies cannot happen.

Analogy: A newspaper's subeditors' desk

The night's pages hang on the wall as the approved proofs. Subeditors never see a colleague's unfinished edits on another desk. The chief subeditor accepts or refuses every paste-up.

At the subeditors' desk In Postgres
glancing at the wall before each task Read Committed: a new snapshot per statement (step 6)
photocopying the wall at your first task and working only from the copy Repeatable Read's snapshot (step 7)
the chief refuses your paste over a paragraph someone changed after your copy 40001 on a row changed since your snapshot (step 8)
two subs each see "two adverts on page 5, one may go" and each pulls a different one write skew: page 5 ends with none (step 9)
the chief also logs what each copy was used to decide, and sends back one of two crossed decisions Serializable: 40001 (step 10)
the chief sends back the whole paste-up, not one paragraph 40001 aborts the whole transaction (step 11)

In real terms: your statements read a snapshot; the level decides when it is taken and whether a decision made from it is checked before commit.

Breaks down when: a chief subeditor would notice an empty page 5 by looking; Postgres checks no business rule unless Serializable's tracking or a constraint catches the conflict. Wrong inference: "the database would notice a broken rule." It notices only what the level tracks.

Contrasting case: one subeditor working alone at night: whatever the level, one transaction at a time produces no anomaly, which is step 1's definition.

Examples, run for real

4.1 ExamHall: the hall left without a proctor

EXH-1210: during an in-person ECON-201 sitting, hall B was left with no proctor. The rule is that a proctor may sign off only while at least two are on duty. Amira and Bo both pressed "sign off" at once. MR !1410 runs sign-off at SERIALIZABLE, so the database refuses one of two crossed sign-offs with 40001; how the app then answers the proctor is chosen in db-lost-update-strategies.

BEGIN ISOLATION LEVEL SERIALIZABLE;             -- MR !1410; before: the default, READ COMMITTED
SELECT count(*) FROM proctors WHERE hall = 'B' AND on_duty;
UPDATE proctors SET on_duty = false WHERE name = 'Bo';   -- only if the count was 2 or more
COMMIT;                                         -- of two crossed sign-offs, one fails with SQLSTATE 40001
PostgreSQL 17.10; hall B: 2 on duty; both sign off at once
READ COMMITTED  counts A 2, B 2 · updates ok, ok · COMMIT A ok, B ok -> on duty 0
REPEATABLE READ counts A 2, B 2 · updates ok, ok · COMMIT A ok, B ok -> on duty 0
SERIALIZABLE    counts A 2, B 2 · updates ok, ok · COMMIT A ok, B ERROR 40001 -> on duty 1
   ERROR 40001 could not serialize access due to read/write dependencies among transactions

Each proctor updated only their own row, so REPEATABLE READ found nothing to refuse (step 9). SERIALIZABLE saw that each had read what the other wrote and failed the second commit, Bo's (step 10). Postgres picks which one fails; here the first committer won. Bo's whole transaction was aborted, so Bo stays on duty (step 11).

4.2 Proof domain, ticketing: every anomaly at every level

Each cell is one forced two-session schedule on a small seats and promos schema: a price changed 40 → 55, a new 60 seat inserted, a sold counter read and rewritten, and two promo discounts that must sum to at most 100.

PostgreSQL 17.10; each cell is one forced two-session schedule
anomaly             | READ UNCOMMITTED         | READ COMMITTED           | REPEATABLE READ          | SERIALIZABLE
dirty read          | prevented (read 40)      | prevented (read 40)      | prevented (read 40)      | prevented (read 40)
non-repeatable read | OCCURS (40 then 55)      | OCCURS (40 then 55)      | prevented (40 then 40)   | prevented (40 then 40)
phantom             | OCCURS (2 then 3)        | OCCURS (2 then 3)        | prevented (2 then 2)     | prevented (2 then 2)
lost update         | OCCURS (ends 11, not 12) | OCCURS (ends 11, not 12) | 40001 at UPDATE          | 40001 at UPDATE
write skew          | OCCURS (sum 120)         | OCCURS (sum 120)         | OCCURS (sum 120)         | 40001 at COMMIT (sum 90)

READ UNCOMMITTED and READ COMMITTED columns are identical (step 2). REPEATABLE READ has no phantom, although the standard would allow one (steps 7, 12). The lost update fails at the second UPDATE; the write skew fails only at COMMIT (steps 8, 10).

Picture it

two people at two separate desks, each working on their own sheet with the same line drawing. On the left, a man in a green jacket rubs part of his sheet out with an eraser beside a desk lamp. On the right, a woman in a blue suit cuts a green piece from her sheet with scissors; cut pieces and a spare sheet lie on her desk. (The asset map's alt text speaks of "photocopies of one illustrated master sheet"; no master sheet is visible, only the two copies.)
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A promoter's balance is 500. Two payout transactions at REPEATABLE READ each read 500. A sends UPDATE balances SET balance = 380 and has not committed. B then sends UPDATE balances SET balance = 300 (its 500 minus 200). Then A commits.

What happens to B's UPDATE?

Pick one

Question 2 of 5

A checkout handler runs BEGIN ISOLATION LEVEL REPEATABLE READ, then awaits the seat-map service, then reads seat K7's price and charges it. The fan was promised the price as of BEGIN. While the handler waited, the pricing tool changed K7 from 60 to 75, and the fan was charged 75.

Which is the bug?

Pick one

Question 3 of 5

Promoter p5's balance is 500. A nightly audit and a payout both run at SERIALIZABLE. The audit reads p5's balance and has not committed. The payout then sets the balance to 380 and commits; it reads and writes only balances. The audit then inserts the 500 it read into audit_log and commits.

How does the schedule end?

Pick one

Question 4 of 5

Seat K7 must never be both held and sold. The hold path checks that sales has no row for K7, then inserts into holds. The box office checks that holds has no row for K7, then inserts into sales. Both run at REPEATABLE READ, "because it has no phantoms". A fan's hold and a walk-up sale start at once; each check returns 0, and K7 ends up both held and sold.

Which is the bug?

Pick one

Question 5 of 5

A nightly reconciliation for promoter p3 runs two statements in one transaction: SUM of payments, then SUM of refunds, and reports payments minus refunds. Checkouts keep committing; between the two statements, one transaction records a payment of 100 and its full refund of 100, so the true net stays 900 at every moment.

What is the lowest isolation level at which the job reports 900?

Pick one

Go deeper

Pro tips
  1. Recognise a serialization failure by SQLSTATE 40001, never by the error text. Repeatable Read and Serializable word it differently, and the docs say the text "will vary according to the precise circumstances". When: any code path that runs at REPEATABLE READ or SERIALIZABLE (steps 8, 10, 11); what to do next is the lost-update pack's. Verify (RAN, tips.mjs, 50 runs, 1 distinct): 40001 "could not serialize access due to concurrent update" at Repeatable Read; 40001 "could not serialize access due to read/write dependencies among transactions" at Serializable.
  2. Put the level on BEGIN and check it with SHOW transaction_isolation. A SET TRANSACTION ISOLATION LEVEL sent after a query fails and aborts the transaction, and a wrapper that swallows errors hides that. When: ORMs or pools that open transactions for you (step 7). Verify (RAN, tips.mjs): 25001 "SET TRANSACTION ISOLATION LEVEL must be called before any query", then the next statement gets 25P02; BEGIN ISOLATION LEVEL SERIALIZABLE → SHOW transaction_isolation: serializable.
Source
  • PostgreSQL 17 documentation, 13.2 Transaction Isolation: https://www.postgresql.org/docs/17/transaction-iso.html · fetched 2026-09-27. Definitions: a dirty read is when "a transaction reads data written by a concurrent uncommitted transaction"; a nonrepeatable read finds data "modified by another transaction (that committed since the initial read)"; a phantom read finds "the set of rows satisfying the condition has changed"; a serialization anomaly is a result "inconsistent with all possible orderings of running those transactions one at a time" (steps 1–3). "PostgreSQL's Read Uncommitted mode behaves like Read Committed" (step 2). Table 13.1: phantom read at Repeatable Read "Allowed, but not in PG"; serialization anomaly "Possible" at every level but Serializable (steps 7, 9, 12). Read Committed "starts each command with a new snapshot" (step 6). Repeatable Read "sees a snapshot as of the start of the first non-transaction-control statement in the transaction" (step 7). A repeatable read transaction "cannot modify or lock rows changed by other transactions after the repeatable read transaction began", with "ERROR: could not serialize access due to concurrent update" (step 8; the retry advice that follows is cited by db-lost-update-strategies). Business rules at Repeatable Read "are not likely to work correctly without careful use of explicit locks" (step 9). Serializable "works exactly the same as Repeatable Read except that it also monitors for conditions" of non-serial execution; its predicate locks "do not cause any blocking"; failures "always return with an SQLSTATE value of '40001'"; it "doesn't always prevent errors from being raised that would not occur in true serial execution". The standard "specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable" (step 12). Valid as of 2026-09-27.
  • PostgreSQL 17 documentation, 13.6 Serialization Failure Handling: https://www.postgresql.org/docs/17/mvcc-serialization-failure-handling.html · fetched 2026-09-27. The message text "will vary according to the precise circumstances, but it will always have the SQLSTATE code 40001"; PostgreSQL "does not offer an automatic retry facility". The page's retry advice belongs to db-lost-update-strategies. Valid as of 2026-09-27.
  • Background from the curriculum row, not quoted here: Berenson et al., A Critique of ANSI SQL Isolation Levels (SIGMOD 1995), for write skew as a snapshot-isolation anomaly; Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012), for SSI. Neither was fetched for this lesson; every behavioural claim above rests on the Postgres documentation and the executed runs.

Related lessons

Checked against the tools and docs as of 2026-09-27.