// PostgreSQL error reference

PostgreSQL 40001: could not serialize access

Under REPEATABLE READ or SERIALIZABLE, a concurrent transaction changed data this one depends on. PostgreSQL rolled it back; retry the whole transaction.

SQLSTATE
40001
Name
serialization_failure
Category
Transaction Rollback
Error message
could not serialize access due to concurrent update

What it means

PostgreSQL's higher isolation levels promise that a transaction sees a consistent snapshot. When keeping that promise is impossible because another transaction committed a conflicting change, PostgreSQL aborts one of them with 40001 rather than return a result that couldn't happen in a serial order.

The messages vary with the cause: could not serialize access due to concurrent update or concurrent delete (a row you tried to change was changed by someone else after your snapshot), and could not serialize access due to read/write dependencies among transactions (SERIALIZABLE detected a dangerous pattern). The cure is the same: run the transaction again from the start.

Common causes

  • Two REPEATABLE READ or SERIALIZABLE transactions update the same row; the second to commit fails.
  • A SERIALIZABLE transaction read rows that another transaction then changed, in a pattern that could produce an anomaly.
  • Hot rows, such as a counter or a balance, updated by many concurrent serializable transactions.

How to fix it

  1. Retry. Code that uses REPEATABLE READ or SERIALIZABLE must catch SQLSTATE 40001 and rerun the whole transaction, including its reads, a few times with a short backoff.

  2. Keep serializable transactions short and touch as few rows as possible.

  3. If you don't need serializability for this work, the default READ COMMITTED level re-reads the latest row version on update instead of failing.

    BEGIN ISOLATION LEVEL READ COMMITTED;
    UPDATE counters SET n = n + 1 WHERE name = 'visits';
    COMMIT;
  4. Under SERIALIZABLE, declare read-only transactions as such; SERIALIZABLE READ ONLY DEFERRABLE never fails with a serialization error.

    BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

Reproduce it safely

Run in two separate connections on a scratch database.

-- Session 1:
CREATE TABLE acct (id int PRIMARY KEY, bal int);
INSERT INTO acct VALUES (1, 100);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT bal FROM acct WHERE id = 1;
-- Session 2:
UPDATE acct SET bal = 50 WHERE id = 1;
-- Session 1:
UPDATE acct SET bal = bal - 10 WHERE id = 1;  -- ERROR: could not serialize access due to concurrent update
ROLLBACK;

Related errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.