Postgres Isolation Levels, in depth: per-statement snapshots, the UPDATE re-check, 40001 errors, SERIALIZABLE in practice and a coupon-cap worked example

The three real levels

PostgreSQL accepts all four standard level names but implements three behaviours; Read Uncommitted is treated as Read Committed. All three are built on snapshots: a record of which transactions had committed at a moment, which decides which row versions are visible. You can see one directly with SELECT pg_current_snapshot(); (PostgreSQL 13 and later), which prints the lowest still-running transaction id, the next id to be assigned, and the list in between that were in progress.

Where the levels differ is when the snapshot is taken and what happens when your transaction tries to change a row that a concurrent transaction changed after your snapshot. Reads never block writes and writes never block reads at any level; only writers to the same row wait for each other.

Read Committed: a fresh snapshot for every statement

At Read Committed each statement takes a new snapshot when it starts. Two SELECTs in the same transaction can therefore see different data if another transaction commits between them. That alone surprises people who assume a transaction is a frozen view, but the more important behaviour is what happens on updates.

When an UPDATE, DELETE or SELECT FOR UPDATE finds a row that matches its WHERE clause but that another uncommitted transaction is already modifying, it waits. When the other transaction finishes, if it rolled back, the waiting statement proceeds with the original row. If it committed, the waiting statement looks at the new version of that row, re-evaluates its WHERE clause against it, and updates it only if it still matches. It does not re-run the whole query; rows that did not match at the snapshot but match now are not picked up. The PostgreSQL documentation illustrates this with a page-hits table:

-- Session A                                  -- Session B
BEGIN;
UPDATE website SET hits = hits + 1;
-- (row with hits = 9 becomes 10;
--  the old row with hits = 10 becomes 11)
                                              DELETE FROM website WHERE hits = 10;
                                              -- blocks on the row A is updating
COMMIT;
                                              -- B wakes, re-checks the NEW row version:
                                              -- hits is now 11, not 10, so nothing is deleted

A row had hits = 10 both before and after A's update, yet B deletes nothing: it skipped the row that was 9 in its snapshot, and the row it waited for became 11. This is per-statement consistency, not per-transaction consistency.

So a single statement that reads and writes, such as UPDATE account SET balance = balance - 50 WHERE id = 7, is safe at Read Committed. A read followed by a write computed in the application is not: two transactions both read 100, both write 50, and one decrement is lost. Keep the arithmetic in the UPDATE, or lock the row first with SELECT FOR UPDATE.

Repeatable Read: one snapshot, and errors instead of re-checks

At Repeatable Read the snapshot is taken at the start of the first statement in the transaction that is not a transaction-control command, not at BEGIN. That detail matters when a transaction begins, waits on application logic, and then queries: the view is fixed from the first query onward. Every later statement in the transaction sees exactly that state, plus its own changes. PostgreSQL's implementation is snapshot isolation, which also prevents phantom reads, so it is stronger than the standard's minimum for this level.

When a Repeatable Read transaction tries to update or lock a row that a concurrent transaction modified and committed after the snapshot, it cannot do the Read Committed re-check, because that would mix two snapshots. Instead it fails with SQLSTATE 40001 and the message could not serialize access due to concurrent update. Lost updates through read-modify-write are therefore impossible: one of the two transactions errors and must be retried from the beginning.

What Repeatable Read still allows is write skew: two transactions read overlapping data, each writes a different row based on what it read, and together they break a rule that each one checked. No row is updated by both, so there is no conflict to detect. The next section is a concrete case.

Worked example: a coupon redemption cap

A promotion allows a coupon to be used 100 times. Each checkout counts existing redemptions, compares with the cap and inserts a new redemption if there is room.

CREATE TABLE coupon (code text PRIMARY KEY, max_uses int NOT NULL);
CREATE TABLE redemption (
    id bigserial PRIMARY KEY,
    code text NOT NULL REFERENCES coupon,
    order_id bigint NOT NULL UNIQUE
);
CREATE INDEX ON redemption (code);

-- The naive transaction, run by every checkout:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM redemption WHERE code = 'LAUNCH100';   -- returns 99
SELECT max_uses FROM coupon WHERE code = 'LAUNCH100';       -- returns 100
-- application: 99 < 100, so allow it
INSERT INTO redemption (code, order_id) VALUES ('LAUNCH100', 5001);
COMMIT;

Two checkouts run at the same time when 99 redemptions exist. Both take snapshots that show 99 rows; both conclude there is room; both insert. They insert different rows, so neither waits for the other and Repeatable Read raises no error. The coupon now has 101 uses. At Read Committed the result is the same. This is write skew, and it is the most common real-world isolation bug: any rule of the form "count or sum some rows, then insert or update a different row" is exposed to it.

There are three standard fixes, and they have different costs.

-- Fix 1: serialize on the parent row (works at READ COMMITTED)
BEGIN;
SELECT max_uses FROM coupon WHERE code = 'LAUNCH100' FOR UPDATE;   -- second checkout waits here
SELECT count(*) FROM redemption WHERE code = 'LAUNCH100';          -- fresh snapshot after the wait
INSERT INTO redemption (code, order_id) VALUES ('LAUNCH100', 5002);
COMMIT;

-- Fix 2: make the invariant a single conditional write (keep a counter column)
ALTER TABLE coupon ADD COLUMN used int NOT NULL DEFAULT 0;
BEGIN;
UPDATE coupon SET used = used + 1
 WHERE code = 'LAUNCH100' AND used < max_uses
RETURNING used;                         -- zero rows: cap reached, ROLLBACK and reject
INSERT INTO redemption (code, order_id) VALUES ('LAUNCH100', 5002);   -- only if a row came back
COMMIT;

-- Fix 3: leave the logic as written and run it at SERIALIZABLE, retrying on 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...the same two SELECTs and INSERT as the naive version...
COMMIT;

Fix 1 locks the parent coupon row, so concurrent checkouts for the same coupon queue up. At Read Committed the count after the lock uses a new snapshot and sees the other checkout's insert. Do not use this pattern at Repeatable Read: the count would use the transaction's original snapshot, and the lock would wait without helping. Fix 2 moves the invariant into a single conditional UPDATE on one row, which Read Committed's re-check makes correct; it is the fastest and needs no retry loop, but it changes the schema. Fix 3 leaves the application logic alone and runs it at Serializable, which detects the dangerous pattern and aborts one transaction with 40001. It is the most general, because it also protects rules you did not notice, at the cost of retries and some tracking overhead.

Serializable in practice

PostgreSQL's Serializable level runs on the same snapshots as Repeatable Read and adds tracking of what each transaction read, using predicate locks that appear in pg_locks with mode SIReadLock. These locks never block anything; they exist to detect read-write dependencies that could form a cycle. When one would, a transaction fails with SQLSTATE 40001 and the message could not serialize access due to read/write dependencies among transactions. The failure can arrive at any statement or at COMMIT, and it may abort a transaction that looks innocent, so every Serializable transaction needs a retry wrapper.

Four settings and habits control how many false-positive failures you get. A sequential scan always takes a relation-level predicate lock, so any concurrent write to that table can conflict; the documentation suggests encouraging index scans. In the coupon example the index on redemption(code) keeps the predicate lock narrow. Lock promotion happens when one transaction locks too many tuples or pages: max_pred_locks_per_page (default 2) promotes tuple locks to a page lock, max_pred_locks_per_relation (default -2, meaning max_pred_locks_per_transaction divided by 2) promotes to a relation lock, and the shared table is sized by max_pred_locks_per_transaction (default 64, server-start only). Coarser locks mean more failures. Declare read-only transactions with READ ONLY, which lets the system reduce tracking. Keep transactions short, since a long one keeps its read set live and widens the window for conflicts.

For long reporting queries there is a dedicated mode: BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. It may wait at the start until it can obtain a snapshot that is guaranteed safe, then runs without predicate-lock overhead and cannot fail with a serialization error. It is ideal for consistent exports and reconciliation jobs.

One operational limit: Serializable is not available on a hot standby. An attempt to set it on a read replica raises an error, so read-only traffic routed to replicas must use Repeatable Read or lower, and a check that depends on Serializable must run on the primary.

Retry code you can copy

A retry must re-run the whole transaction, including every read, because the reads are what the database judged stale. Anything computed from them in application memory must be recomputed. Retry SQLSTATE 40001 (serialization failure) and 40P01 (deadlock detected); be careful with unique-violation errors, which can be a symptom of a concurrency race but can also be genuine duplicate input.

import random, time
import psycopg
from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)   # SQLSTATE 40001, 40P01

# conn is opened with autocommit=True, so conn.transaction() issues BEGIN itself
def run_txn(conn: psycopg.Connection, work, isolation="SERIALIZABLE", attempts=5):
    for attempt in range(attempts):
        try:
            with conn.transaction():
                conn.execute(f"SET TRANSACTION ISOLATION LEVEL {isolation}")
                return work(conn)            # must re-read everything; no cached values
        except RETRYABLE:
            if attempt == attempts - 1:
                raise
            time.sleep(random.uniform(0, 0.05 * 2 ** attempt))   # jittered backoff
            metrics.increment("db.txn.retry", tags={"isolation": isolation})

Keep external side effects (emails, payments, messages to other services) out of the retried block, or make them idempotent and record them in the same transaction with an outbox table. Otherwise each retry repeats the side effect. Add jitter to the backoff for the same reason as anywhere else: two transactions that conflicted once will conflict again if they restart in lockstep. Deadlocks, which also produce rollbacks, are covered in deadlock detection.

Observing isolation in production

PostgreSQL counts deadlocks per database but not serialization failures, so count 40001 retries in the application, tagged by transaction name and level. A retry rate under one or two percent is usually harmless; a sudden rise points to a new hot row, a lost index causing sequential scans, or a long-running transaction.

-- The current snapshot (PostgreSQL 13+): xmin:xmax:list of in-progress xids
SELECT pg_current_snapshot();

-- Who is holding the oldest snapshot (blocks vacuum, inflates SSI bookkeeping)
SELECT pid, state, backend_xmin, now() - xact_start AS xact_age, left(query, 60)
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC
 LIMIT 5;

-- Predicate locks held by serializable transactions, by granularity
SELECT locktype, count(*) FROM pg_locks WHERE mode = 'SIReadLock' GROUP BY 1;

-- Deadlocks are counted by the server; serialization failures are not, so count them in the app
SELECT datname, deadlocks, xact_rollback FROM pg_stat_database WHERE datname = current_database();

Alert on long transactions at every level: an old snapshot keeps dead row versions from being cleaned up (see vacuum and bloat). Set idle_in_transaction_session_timeout and alert on the oldest backend_xmin.

Choosing a level per transaction

Set the level per transaction with BEGIN ISOLATION LEVEL ... or SET TRANSACTION before the first query, rather than changing default_transaction_isolation globally, unless every code path already has retries. A global switch to Serializable without a retry wrapper turns occasional anomalies into frequent user-visible errors.

What to do next

1. List every place your application reads data, decides in code, then writes; those are your exposure to lost updates and write skew.
2. For each, choose a fix: move the logic into one conditional statement, lock the parent row at Read Committed, or run at Serializable.
3. Wrap every Repeatable Read and Serializable transaction in a retry helper for 40001 and 40P01 with jittered backoff, and move side effects outside it or behind an outbox.
4. Reproduce the coupon race in two psql sessions on a scratch database at each level, so the team has seen each behaviour once.
5. Check plans for Serializable transactions and add indexes so they avoid sequential scans; watch SIReadLock counts by lock type.
6. Add metrics for retry rate per transaction and alerts on idle-in-transaction sessions and the oldest backend_xmin.