SQLSTATE / CLASS 40 · TRANSACTION ROLLBACK

40001

serialization_failure

SQLSTATE: 40001
Condition name: serialization_failure
Class: Transaction Rollback
Source macro: ERRCODE_T_R_SERIALIZATION_FAILURE
Evidence: Observed at runtime in the source evidence

Analysis & operational context

English SQLSTATE atlas: authored explanations and source/runtime evidence are separate from the manual definitions. View source ↗

At a glance

40001 is PostgreSQL's serialization_failure condition in Class 40, transaction_rollback. It tells the client that the transaction's observed ordering cannot be serialized with a concurrent transaction. PostgreSQL aborts the conflicting transaction so the client can retry.

The representative case starts two SERIALIZABLE transactions that read the same value. The first updates and commits. The stale transaction then receives could not serialize access due to concurrent update, enters INERROR, and returns to IDLE only after ROLLBACK. A new serializable transaction reads the committed value, applies the registry's fixed SET value = 2 operation, commits, and produces the final result; this is a transaction-boundary proof, not a test of an application business calculation.

Run 40001-manual-boundary-final-20260909 passed on PostgreSQL 18.6 and isolated PostgreSQL 10.21. The per-target assertions and structured observations are retained in the public evidence JSON.

Meaning and trigger paths

At SERIALIZABLE isolation PostgreSQL uses predicate and tuple-conflict tracking to reject a transaction when its result would depend on an order that cannot be made serial. A concurrent update of a row read by a stale transaction is one concrete path. The SQLSTATE describes the transaction outcome; it does not identify the business operation that should be retried.

The executor's update path reports ERRCODE_T_R_SERIALIZATION_FAILURE with the source message could not serialize access due to concurrent update. Other serialization conflicts can use a different message or a recovery-conflict detail while retaining the class's transaction-rollback meaning. Preserve the complete diagnostic and the transaction's read/write set in application logs.

40001 differs from 23505: a duplicate requested by the user is not automatically a serialization failure, even though some concurrent key-selection designs can surface a unique violation that the application treats as retryable. Decide the retry policy from the operation's semantics and the complete transaction history.

Messages and diagnostics

The case schedule below must use two sessions for the two initial snapshots. The final block is a new connection and a new serializable snapshot; it is the required complete retry.

CREATE TABLE serial_rows(id integer PRIMARY KEY, value integer NOT NULL);
INSERT INTO serial_rows VALUES (1, 0);

-- Open both serializable snapshots before the first commit.
BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Session first reads 0; session stale reads the same 0.
SELECT value FROM serial_rows WHERE id = 1;
SELECT value FROM serial_rows WHERE id = 1;

-- Session first writes 1 and commits; the stale session then writes from its old snapshot.
UPDATE serial_rows SET value = 1 WHERE id = 1;
COMMIT;
UPDATE serial_rows SET value = 2 WHERE id = 1;
-- The UPDATE reports 40001 and the transaction becomes INERROR.
ROLLBACK;

-- New retry transaction: read the fresh value, apply the operation, and commit.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT value FROM serial_rows WHERE id = 1;
UPDATE serial_rows SET value = 2 WHERE id = 1;
COMMIT;
SELECT value FROM serial_rows WHERE id = 1;

PostgreSQL 18.6 returned:

SQLSTATE: 40001
severity: ERROR
message_primary: could not serialize access due to concurrent update
message_detail: <none>
source: nodeModifyTable.c / ExecUpdate / line 2604

PostgreSQL 10.21 produced the same primary message with its version-specific nodeModifyTable.c line. message_detail is absent in this path; other conflict origins may attach additional fields. The SQLSTATE and the aborted transaction state are the stable retry signals.

Diagnosis

Capture SQLSTATE, severity, primary message, detail, hint, context, isolation level, the statements that formed the snapshot, and transaction status. Determine which transaction committed first and which reads became stale. The runner asserted both initial reads were 0, the first commit returned IDLE, and the stale transaction was INERROR before rollback.

Do not issue a follow-up query on the failed transaction. Roll it back, start a new transaction, and repeat the complete read/decision/write sequence. Replaying only the last UPDATE can apply a decision based on a snapshot that is no longer valid.

Response and repair

Treat 40001 as a transaction retry signal when the operation is designed for it:

Roll back the entire failed transaction and release its locks.
Begin a new transaction at the required isolation level and re-read all values used by the business decision.
Apply the operation again, with a bounded backoff and a maximum retry count.
Make the operation idempotent and verify the final business result after commit.

The representative retry read 1, wrote 2, committed with status IDLE, and a separate read observed final value 2. A fixed assignment in this disposable case does not prove that an arbitrary production calculation is safe to replay; the application must recompute from the fresh snapshot.

Versions and boundaries

The catalogue has a definition-presence observation for 40001 at PostgreSQL 7.4 and through the locked 8.4.22 pre-9.0 definitions, then in every listed formal snapshot through PostgreSQL 18.6 and the PostgreSQL 19 Beta 3 preview. This is a definition-only presence boundary, not an exact implementation introduction or runtime-use claim. The class title observation between 9.0 and 9.1 is recorded in the catalogue; the condition has no other recorded definition change in the scanned range.

The stale-update case passed on PostgreSQL 18.6 and 10.21. Message source lines and conflict details vary by release and conflict type. This evidence covers a stale serializable update and its fresh complete retry; it does not claim that every 40001 source path has the same message or that every transaction can be retried safely.

Sources

Structured evidence is recorded in the public evidence JSON. Source records use PostgreSQL commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; run records retain exact target IDs and structured observations.

src.errcodes.18.6 — errcodes.txt
src.nodeModifyTable.18.6 — nodeModifyTable.c
doc.mvcc.18 — Serialization failure handling
Runtime: 40001-manual-boundary-final-20260909 on latest and pg10; structured observations are in the public evidence JSON

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

40001 is the serialization_failure condition in Class 40 transaction_rollback.

Method: Read the Class 40 section and 40001 row in the frozen errcodes.txt snapshot.

Limits: Directory identity does not identify one serialization-conflict origin.

src.errcodes.18.6

The executor update path reports ERRCODE_T_R_SERIALIZATION_FAILURE with could not serialize access due to concurrent update.

Method: Trace the ExecUpdate concurrent-update branch and compare the diagnostic from the stale serializable writer.

Limits: Other serialization conflicts can use different messages or source paths while retaining SQLSTATE 40001.

src.nodeModifyTable.18.6

The stale serializable writer entered INERROR after 40001; after ROLLBACK, a fresh serializable transaction read 1, wrote 2, committed, and left final value 2 on both targets.

Method: Open both initial serializable snapshots before the first commit, trigger the stale update, roll it back, and assert the complete fresh read/fixed assignment/write retry and final value with registry transaction boundaries executed under autocommit=True.

Limits: The fixed assignment is a controlled retry proof; it does not establish that an arbitrary business calculation is safe to replay. The run reads 1 and sets value=2; it does not test a business calculation decision.

doc.mvcc.18 case-manifest.40001 runtime.40001-manual-boundary-final-20260909.latest runtime.40001-manual-boundary-final-20260909.pg10

The locked catalogue records 40001 in every listed formal snapshot from 9.0.23 through 18.6 and in 19beta3; pre-9.0 history is not scanned.

Method: Read the manifest snapshots and definition references for the code.

Limits: The first scanned release is a lower bound, not an asserted introduction version.

manifest.40001

src/backend/utils/errcodes.txt · REL_18_6 · lines 329-333
724edf9bde9d356724ad384a2e196edc3c9f80f7
src/backend/executor/nodeModifyTable.c · REL_18_6 · lines 2595-2605
724edf9bde9d356724ad384a2e196edc3c9f80f7
doc/src/sgml/mvcc.sgml · PG18-docs · serialization failure handling
724edf9bde9d356724ad384a2e196edc3c9f80f7
doc/src/sgml/protocol.sgml · PG18-docs · ErrorResponse fields
724edf9bde9d356724ad384a2e196edc3c9f80f7
sources/manifest.lock.json · · snapshots and definition_blobs entries for the 40001 definition
verify/cases/40001/cases.json · · serializable_stale_update
verify/cases/40001/snippets.json · · serializable_stale_update ordered SQL

Message templates

explicit ERROR · message.concurrent-update

Primary

could not serialize access due to concurrent update

The template is the observed executor update path; other serialization failures may carry different text or detail.

Reproduction & repair cases

serializable_stale_update · PG 10, 18

Preconditions

Two sessions using SERIALIZABLE
Both read the same row before either writes

Trigger: Commit one writer, then update the row from the stale serializable transaction.

Expected assertions

The stale writer receives 40001
Its transaction is rolled back
The first committed value remains before retry
A fresh serializable snapshot is read, applied, and committed as a complete retry

Repair: Retry the complete serializable transaction with bounded backoff and an idempotency policy; begin from a fresh snapshot and verify the final business result.

Cleanup: Roll back workers and drop the case schema.

Recorded runtime evidence

18.6 (Homebrew) · passed

Run: 40001-registry-final-20260909

{
  "reads": {
    "first": 0,
    "retry": 1,
    "stale": 0
  },
  "statuses": {
    "retry": "IDLE",
    "stale_after_error": "INERROR",
    "first_after_commit": "IDLE",
    "stale_after_rollback": "IDLE"
  },
  "diagnostic": {
    "severity": "ERROR",
    "sqlstate": "40001",
    "source_file": "nodeModifyTable.c",
    "source_line": "2604",
    "message_primary": "could not serialize access due to concurrent update",
    "source_function": "ExecUpdate"
  },
  "final_value": 2
}

10.21 (Debian 10.21-1.pgdg90+1) · passed