skip to content

In PostgreSQL, why can a read-only SERIALIZABLE report still fail with 40001, and what does SERIALIZABLE READ ONLY DEFERRABLE change?

level: seniorimportance: nice to knowfreq 22%

answer

  1. read-only is not conflict-free
  2. batch shown closed, a detail row missing
  3. wait for a safe snapshot
  4. SafeSnapshot wait event

basics

~20 s

Under SSI a read-only transaction can be the reader that completes a dangerous chain of read/write conflicts, so it can be cancelled. READ ONLY DEFERRABLE waits for a safe snapshot, then runs without predicate locks and can neither fail nor cause failures.

solid answer

~50 s

Under PostgreSQL's SSI a read-only transaction can still sit in a dangerous chain of read/write conflicts — the manual's case is a report that sees a batch marked closed but misses one of its detail rows — so it may be cancelled with `40001`, and what it read is not trustworthy until it commits. `BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE` changes that: the transaction may block when it takes its snapshot until the server can show the snapshot is **safe**, meaning no concurrent read/write serializable transaction can still make it part of an anomaly. From then on it takes no predicate locks, adds no SSI overhead, cannot be cancelled for serialization and cannot cause others to be. The wait shows as wait event `SafeSnapshot`, and `pg_safe_snapshot_blocking_pids()` names the sessions it waits on. `DEFERRABLE` has no effect unless the transaction is also `SERIALIZABLE` and `READ ONLY`.

code

sql · 7 lines
sql
BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
-- may wait here (wait_event SafeSnapshot) before the first query runs
SELECT e.event_id, e.seats_left, count(b.booking_id) AS bookings
  FROM events e
  LEFT JOIN bookings b ON b.event_id = e.event_id
 GROUP BY e.event_id, e.seats_left;
COMMIT;

go deeper

for a junior

Recall that READ ONLY DEFERRABLE is an option for SERIALIZABLE reports that trades a possible wait at the start for never failing with 40001.

for a middle

Explain why a read-only transaction can still take part in a serialization anomaly, and what makes a snapshot safe.

for a senior

Pick the right mode for long reports under SSI, and diagnose a stuck one with the SafeSnapshot wait event and pg_safe_snapshot_blocking_pids.

for a principal

Decide where reports must match a serial order, and whether to pay with a start-up wait on the primary or accept REPEATABLE READ on replicas.

## Why read-only is not enough under SSI In PostgreSQL a **REPEATABLE READ** transaction that only reads never meets a serialization failure: there is nothing it could conflict with. **SERIALIZABLE** is different. Under **Serializable Snapshot Isolation (SSI)** a serialization anomaly is a cycle of dependencies among transactions, and a reader can be one link of that cycle. The manual's example is batch processing: a report reads a control row showing that a batch is closed, but because of when its snapshot was taken it does not see one of the detail rows that logically belong to the batch. Each writer on its own is fine; the read-only report is what makes the outcome impossible in any serial order. So SSI tracks reads by read-only serializable transactions too, and may cancel one with SQLSTATE `40001`. For an ordinary `SERIALIZABLE READ ONLY` report that means: - it can be cancelled, possibly after running for a long time, and must then be retried; - the data it has read is not guaranteed valid until it commits successfully. PostgreSQL softens this: a `READ ONLY` serializable transaction can often establish at its start that no anomaly is possible and skip predicate locks altogether, or release them early once it becomes safe. But that is opportunistic, not promised. ## What DEFERRABLE does `DEFERRABLE` turns the opportunistic check into a guarantee: 1. The transaction takes a snapshot as usual. 2. If serializable read/write transactions are running that could still make that snapshot part of an anomaly, it **waits** instead of starting its work. 3. When they finish without making the snapshot unsafe, it proceeds; if the snapshot did turn out unsafe, it discards it and tries a new one. 4. From then on it runs on a **safe snapshot**: no predicate locks, no SSI bookkeeping, no risk of being cancelled for serialization, and no risk of causing another transaction's cancellation. This is the only case in which a serializable transaction blocks where a repeatable read one would not. In exchange, the data it reads is valid as soon as it is read. ## Watching the wait A deferrable transaction waiting for its snapshot appears in `pg_stat_activity` with `wait_event_type` `IPC` and `wait_event` `SafeSnapshot`. To see what it is waiting for: ```sql SELECT pid, pg_safe_snapshot_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event = 'SafeSnapshot'; ``` The function returns the process IDs of the serializable sessions preventing a safe snapshot, or an empty array. A long-running read/write serializable transaction — or one left idle in transaction — can keep the report waiting. ## Choosing how to run a long report | How the report runs | Can it fail with 40001? | Consistent with a serial order? | Start-up wait | |---|---|---|---| | `READ COMMITTED` | no | no — each statement sees a different moment | none | | `REPEATABLE READ` | no, being read-only | stable view, but it may show an anomaly | none | | `SERIALIZABLE READ ONLY` | yes | yes, once it commits | none | | `SERIALIZABLE READ ONLY DEFERRABLE` | no | yes, as soon as it reads | possibly long | For the ticketing service, an end-of-sale settlement report that must agree with bookings committed under SERIALIZABLE is the natural candidate: a start-up wait of seconds is acceptable, a cancellation twenty minutes in is not. ## Rules and edges - `DEFERRABLE` **has no effect** unless the transaction is also `SERIALIZABLE` and `READ ONLY`. - Like the isolation level, it must be set before the first query; `default_transaction_deferrable` (default `off`) sets it for new transactions. - That default alone changes nothing for transactions that are not also serializable and read-only, so a dedicated reporting role usually gets all three defaults: `default_transaction_isolation = 'serializable'`, `default_transaction_read_only = on` and `default_transaction_deferrable = on`, set with `ALTER ROLE ... SET`. - The `DEFERRABLE` transaction mode is a PostgreSQL extension to the SQL standard, and it is unrelated to `DEFERRABLE` constraints. - A transaction that imports a snapshot with `SET TRANSACTION SNAPSHOT` must not be `READ ONLY DEFERRABLE`. - On a hot standby SERIALIZABLE is unavailable, so the mode cannot help reports that run on a replica. - `pg_dump --serializable-deferrable` uses the same mode for a dump that must be consistent with later states; the manual notes it brings nothing to a dump meant only for disaster recovery.

  • Why is SERIALIZABLE READ ONLY DEFERRABLE of no use on a PostgreSQL read replica?
    A hot standby does not support SERIALIZABLE at all: requesting it fails with `cannot use serializable mode in a hot standby`, and REPEATABLE READ is the strongest level there. A report on a standby can therefore see a state no serial order of the primary's transactions would produce. A report that needs that guarantee must run on the primary, where `DEFERRABLE` removes its cancellation risk.
  • What can keep a deferrable report waiting for a long time, and how do you find it?
    Any long read/write serializable transaction overlapping the snapshot, including one left idle in transaction, keeps the snapshot unproven. Look in `pg_stat_activity` for `wait_event = 'SafeSnapshot'` and call `pg_safe_snapshot_blocking_pids(pid)` to list the blocking sessions; ending or shortening those transactions releases the report.

  • A read-only SERIALIZABLE transaction can never be cancelled with a serialization failure.
  • DEFERRABLE on the transaction postpones the report's constraint checks to commit.
  • READ ONLY DEFERRABLE gives the same benefit at REPEATABLE READ.
  • A deferrable report can still be cancelled later if writers conflict with it.
  • A SafeSnapshot wait means the report is stuck behind a row lock.