thesev1database — Evidence-first PostgreSQL error & incident reference

Diagnose

See it live on the server

Run these against the affected instance to confirm the diagnosis before you act.

Standard triage — not specific to this error

These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.

This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.

What is running right now

Active backends, how long each has been running, and what it is waiting on.

SELECT pid,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS running_for,
       left(query, 80)     AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
  AND  pid <> pg_backend_pid()
ORDER  BY running_for DESC NULLS LAST;

Who is blocking whom

Turn raw blocking PIDs into the actual queries on both sides of the wait.

SELECT blocked.pid    AS blocked_pid,
       blocked.query  AS blocked_query,
       blocking.pid   AS blocking_pid,
       blocking.query AS blocking_query
FROM   pg_stat_activity AS blocked
JOIN   LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN   pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

Locks that are still waiting

Every lock a backend has requested but not yet been granted.

SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       COALESCE(c.relname, l.transactionid::text) AS object
FROM   pg_locks l
LEFT   JOIN pg_class c ON c.oid = l.relation
WHERE  NOT l.granted
ORDER  BY l.pid;

Why it happens

What PostgreSQL is telling you

The mechanism behind the error, grounded in the official manual — not paraphrased.

PostgreSQL 16 Documentation — §13.2.2 Repeatable Read Isolation Level

But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began. When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning. [...] Note that only updating transactions might need to be retried; read-only transactions will never have serialization conflicts.

Read the full section on postgresql.org →

Session A

Session A read balance = 1000.00 under REPEATABLE READ, which fixes its snapshot at that moment. While it slept, session B changed the same row and committed. When session A finally tried to write, PostgreSQL's own error — "could not serialize access due to concurrent update" — is its way of refusing to let session A commit a change based on data that is no longer current. PostgreSQL cancels the transaction instead of silently overwriting session B's committed update.

Session B

Session B never touched a stale snapshot: it began, updated the row, and committed immediately. From PostgreSQL's perspective this transaction did nothing wrong, which is exactly why it succeeds — the conflict is entirely attributed to session A's outdated read.

Reproduce & verify

A real, two-session PostgreSQL reproduction

A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.

1Open session A and start a REPEATABLE READ transaction.
2Read the target row in session A and keep the transaction open.
3Open session B, update the same row, and commit.
4Return to session A and attempt to update the row.

Setup runs first, then session A begins, then session B begins while session A is still open.

Schema setup

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id integer primary key,
  balance numeric(10,2) not null,
  updated_at timestamptz default now()
);
INSERT INTO accounts (id, balance) VALUES (1, 1000.00);

Session A (first client)

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE id = 1;
SELECT pg_sleep(3);
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Session B (second client)

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

What PostgreSQL actually returned

Schema setup output

DROP TABLE
CREATE TABLE
INSERT 0 1

Session A output

BEGIN
 id | balance
----+---------
  1 | 1000.00
(1 row)

 pg_sleep
----------

(1 row)

ERROR:  could not serialize access due to concurrent update
ROLLBACK

Session B output

BEGIN
UPDATE 1
COMMIT

PremiumTested in the lab, not just recommended

The textbook recovery path — rollback and retry — is executed against this exact incident and the resulting data is shown, proving the recovery actually restores correctness. This is recovery after the fact, not a way to prevent the conflict from occurring.

After session A's transaction rolled back, the same logical update (add 100 back to account 1) was resubmitted as a fresh transaction, with no isolation-level tricks or retry framework — just the plain retry any application would perform after catching SQLSTATE 40001.

Without this

Above: session A's transaction was cancelled and made no data change; session B's update had already committed.

With this, tested

Below: the same update, resubmitted as a fresh transaction after rollback — committed cleanly, no error, no lost update.

What Pro unlocks here

The exact prevention SQL — copy-paste ready
Raw psql output captured from the Docker lab
A senior-DBA action list to take it further
Live monitoring queries to catch it in production
The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence

Unlock with Pro — $24.99/moor $199/yr — save ~$100

Could not serialize access due to concurrent update

What to do right now