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
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 READorSERIALIZABLEtransactions update the same row; the second to commit fails. - A
SERIALIZABLEtransaction 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
-
Retry. Code that uses
REPEATABLE READorSERIALIZABLEmust catch SQLSTATE40001and rerun the whole transaction, including its reads, a few times with a short backoff. -
Keep serializable transactions short and touch as few rows as possible.
-
If you don't need serializability for this work, the default
READ COMMITTEDlevel 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;
-
Under
SERIALIZABLE, declare read-only transactions as such;SERIALIZABLE READ ONLY DEFERRABLEnever 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
40P01deadlock detected25P02current transaction is aborted55P03lock not available3960SQL Server: Snapshot isolation update conflictORA-08177Oracle: can't serialize access for this transaction1213MySQL: Deadlock found when trying to get lock
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.
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.