READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and Anomaly Prevention

Learning outcomes

MVCC answers how several row versions can coexist. Isolation answers a different question: which committed state is each statement allowed to observe, and what must PostgreSQL do when concurrent histories cannot satisfy the requested guarantee? ServiceHub will test the three distinct PostgreSQL behaviors—Read Committed, Repeatable Read, and Serializable—with real concurrent histories rather than definitions alone.

1. Isolation is a contract about observable histories

PostgreSQL accepts all four SQL names but internally has three distinct implementations: Read Uncommitted behaves like Read Committed. PostgreSQL Repeatable Read is snapshot isolation and is stronger than the SQL standard minimum because it does not permit phantoms. Serializable builds on snapshot isolation with SSI conflict detection.

2. Disposable isolation dataset

DROP TABLE IF EXISTS app.ch07_iso_counter;DROP TABLE IF EXISTS app.ch07_on_call;CREATE TABLE app.ch07_iso_counter (    counter_id integer PRIMARY KEY,    value integer NOT NULL);INSERT INTO app.ch07_iso_counter VALUES (1, 0);CREATE TABLE app.ch07_on_call (    doctor_id text PRIMARY KEY,    on_call boolean NOT NULL);INSERT INTO app.ch07_on_call VALUES ('alice', true), ('bob', true);

3. READ UNCOMMITTED still cannot dirty-read

Session A writes but does not commit. Session B explicitly requests Read Uncommitted. PostgreSQL can report the requested isolation label, but the visibility behavior is Read Committed: the uncommitted value is not visible.

BEGIN;UPDATE app.ch07_iso_counter SET value = 10 WHERE counter_id = 1;SELECT value FROM app.ch07_iso_counter WHERE counter_id = 1;-- Keep open; Session A sees its own value 10.

BEGIN ISOLATION LEVEL READ UNCOMMITTED;SHOW transaction_isolation;SELECT value FROM app.ch07_iso_counter WHERE counter_id = 1;-- Expect 0, never A's uncommitted 10.

Now commit A and issue another SELECT in B. Because this mode behaves as Read Committed, the second statement obtains a fresh statement snapshot and can see 10.

-- Session ACOMMIT;-- Session BSELECT value FROM app.ch07_iso_counter WHERE counter_id = 1;COMMIT;-- Second SELECT expects 10.

4. READ COMMITTED refreshes the snapshot for each command

Read Committed is PostgreSQL's default. A plain SELECT sees data committed before that SELECT began (plus the transaction's own prior writes). Two SELECTs in one explicit transaction can therefore see different committed states.

UPDATE app.ch07_iso_counter SET value = 0 WHERE counter_id = 1;-- Session BBEGIN ISOLATION LEVEL READ COMMITTED;SELECT value FROM app.ch07_iso_counter WHERE counter_id = 1; -- 0-- Session A, in another terminalUPDATE app.ch07_iso_counter SET value = 20 WHERE counter_id = 1; -- autocommit-- Session BSELECT value FROM app.ch07_iso_counter WHERE counter_id = 1; -- 20COMMIT;

This is a nonrepeatable read by the SQL-standard vocabulary. It is allowed at Read Committed and often exactly what applications want.

5. REPEATABLE READ freezes one transaction snapshot

At Repeatable Read, the snapshot used for ordinary reads is fixed at the transaction's first non-transaction-control statement. Later commits by other transactions do not appear inside that transaction.

UPDATE app.ch07_iso_counter SET value = 0 WHERE counter_id = 1;-- Session BBEGIN ISOLATION LEVEL REPEATABLE READ;SELECT value, pg_current_snapshot()::textFROM app.ch07_iso_counter WHERE counter_id = 1; -- value 0-- Session AUPDATE app.ch07_iso_counter SET value = 30 WHERE counter_id = 1; -- commit-- Session BSELECT value, pg_current_snapshot()::textFROM app.ch07_iso_counter WHERE counter_id = 1; -- still 0

If Session B now tries to update that row based on its old snapshot, PostgreSQL cannot silently pretend the concurrently changed version was the same starting point. The update can fail with SQLSTATE 40001.

UPDATE app.ch07_iso_counterSET value = value + 1WHERE counter_id = 1;-- Expected error class: SQLSTATE 40001 serialization_failure.\errverboseROLLBACK;

6. Snapshot isolation can still permit write skew

ServiceHub requires at least one on-call responder. Alice and Bob each run the same logic: “if at least two responders are on call, I may take myself off call.” Under Repeatable Read, each transaction can see the same stable two-person snapshot, update a different row, and both commit because they do not directly overwrite the same tuple.

UPDATE app.ch07_on_call SET on_call = true;SELECT count(*) FILTER (WHERE on_call) AS on_call_countFROM app.ch07_on_call;-- Expect 2.

-- Session ABEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*) FROM app.ch07_on_call WHERE on_call; -- 2UPDATE app.ch07_on_call SET on_call = false WHERE doctor_id = 'alice';-- Session B (before A commits)BEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*) FROM app.ch07_on_call WHERE on_call; -- 2UPDATE app.ch07_on_call SET on_call = false WHERE doctor_id = 'bob';-- Then commit both transactions.-- Both can commit under Repeatable Read because they changed different rows.

SELECT doctor_id, on_call FROM app.ch07_on_call ORDER BY doctor_id;SELECT count(*) FILTER (WHERE on_call) AS on_call_count FROM app.ch07_on_call;-- Possible result after both commits: 0 on-call responders.

The database remained transactionally consistent at the row level, but the application invariant was not serializable. This is the canonical shape of write skew.

7. SERIALIZABLE Snapshot Isolation detects the dangerous dependency structure

Reset both responders to true and repeat the same interleaving using Serializable. Both transactions can initially read 2 and update different rows. PostgreSQL tracks read/write dependencies using predicate-lock metadata. To preserve serializability, at least one transaction is forced to abort; the exact victim and exact statement/COMMIT at which the error surfaces are not an application contract.

UPDATE app.ch07_on_call SET on_call = true;-- Session ABEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*) FROM app.ch07_on_call WHERE on_call; -- 2UPDATE app.ch07_on_call SET on_call = false WHERE doctor_id = 'alice';-- Session BBEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*) FROM app.ch07_on_call WHERE on_call; -- 2UPDATE app.ch07_on_call SET on_call = false WHERE doctor_id = 'bob';-- Commit both. One transaction must not be allowed to complete this history.-- The loser reports SQLSTATE 40001 serialization_failure.

ERROR:  could not serialize access due to read/write dependencies among transactionsSQLSTATE: 40001The exact DETAIL/HINT text and which session becomes the victim can vary.

Retry the failed whole business operation. On retry it observes that only one responder remains on call and should decline the state transition. Serializable gives you the serializable outcome; application logic still defines what to do with the newly observed state.

8. Predicate locks are evidence, not blocking mutexes

BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*) FROM app.ch07_on_call WHERE on_call;SELECT locktype, mode, relation::regclass, page, tuple, grantedFROM pg_locksWHERE pid = pg_backend_pid()  AND mode = 'SIReadLock'ORDER BY locktype, relation::regclass::text, page, tuple;ROLLBACK;

You may see relation-, page-, or tuple-level SIReadLock entries depending on plan shape and predicate-lock promotion. They do not block writers and cannot cause deadlocks. Their purpose is to detect read/write dependency patterns relevant to SSI.

9. Isolation choice is an invariant-design decision, not a “safety slider”

Higher isolation is not automatically better for every workload. A single-row uniqueness rule is often best represented by a UNIQUE constraint rather than Serializable application logic. A conditional state transition can often be expressed as one atomic UPDATE ... WHERE ... RETURNING. Foreign keys and CHECK constraints protect other classes of invariants directly. Serializable is most valuable when the invariant depends on a predicate or relationship spanning rows that cannot be expressed sufficiently with ordinary constraints and atomic statements.

Isolation does not make every PostgreSQL object transactional in the same way

Sequences are the important counterexample already introduced in Chapter 04. Sequence increments are immediately visible and are not rolled back if the calling transaction aborts. Do not use sequence behavior as evidence that MVCC isolation is broken; sequences intentionally have different semantics so concurrent key allocation does not serialize every caller.

CREATE TEMP SEQUENCE ch07_demo_seq;BEGIN ISOLATION LEVEL REPEATABLE READ;SELECT nextval('ch07_demo_seq') AS allocated;ROLLBACK;SELECT nextval('ch07_demo_seq') AS next_after_rollback;-- The second value advances; the rolled-back allocation is not reused.DROP SEQUENCE ch07_demo_seq;

Serializable failure is a correctness mechanism

A serialization failure is not PostgreSQL “randomly failing under load.” It is the mechanism by which SSI refuses a concurrent history that cannot be proven equivalent to a serial order. Track its rate because a spike can indicate a workload-shape change, larger transactions, changed plans/predicate-lock granularity, or increased contention. But do not “fix” the metric by disabling Serializable before understanding which invariant depended on it.

Likewise, explicit row/table locks can enforce invariants, but they trade optimistic abort/retry behavior for blocking and deadlock risk. PostgreSQL's own documentation recommends evaluating both approaches. If you choose explicit locking, define the exact rows/relations and lock acquisition order; if you choose Serializable, define whole-transaction retry semantics. Correctness comes from the complete protocol, not the isolation keyword alone.

10. Cleanup and checks

ROLLBACK;DROP TABLE IF EXISTS app.ch07_on_call;DROP TABLE IF EXISTS app.ch07_iso_counter;

11. Production judgment and bridge

Choose isolation from correctness requirements, not folklore. Read Committed is often sufficient when constraints/atomic statements protect invariants. Repeatable Read is useful for a stable transactional view but does not guarantee serializability. Serializable can simplify cross-row integrity reasoning when the application implements robust retries. The next lesson separates isolation from explicit locking and shows why “a lock exists” does not automatically mean “someone is blocked.”

Authoritative references

• PostgreSQL 18 — Transaction Isolation
• PostgreSQL 18 — Serializable Consistency
• PostgreSQL 18 — Serialization Failure Handling
• PostgreSQL 18 — pg_locks
• PostgreSQL 18 — Error Codes