DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 40001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 40001: Serialization Failure — What It Is and How to Fix It

PostgreSQL error code 40001 (serialization_failure) occurs when two or more concurrent transactions conflict in a way that prevents them from being executed in a serializable order. PostgreSQL intentionally aborts one of the conflicting transactions to preserve data consistency, returning this error to signal that the transaction must be retried. This is expected behavior, not a bug — your application must be designed to handle it gracefully.

Top 3 Causes

1. SSI Conflicts in SERIALIZABLE Isolation Level

PostgreSQL's Serializable Snapshot Isolation (SSI) detects read/write dependencies between transactions. If transaction A reads a range while transaction B modifies data within that range and commits first, transaction A gets rolled back with error 40001.

-- Transaction A (session 1)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE branch_id = 5;
-- Meanwhile, Transaction B commits an INSERT into the same branch...
INSERT INTO accounts (branch_id, balance) VALUES (5, 500); -- session 2 commits
-- Now Transaction A's UPDATE will trigger 40001
UPDATE branch_totals SET total = 10500 WHERE branch_id = 5;
COMMIT; -- ERROR: 40001 serialization_failure
Enter fullscreen mode Exit fullscreen mode

2. Concurrent UPDATE on the Same Row (REPEATABLE READ)

When two transactions attempt to modify the same row simultaneously under REPEATABLE READ or higher, PostgreSQL allows only one to succeed. The second transaction detects that its snapshot is no longer valid after the first commits.

-- Both sessions run concurrently targeting the same row
-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT; -- succeeds

-- Session 2 (started before Session 1 committed)
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT; -- ERROR 40001: serialization_failure
Enter fullscreen mode Exit fullscreen mode

3. Long-Running Transactions Increasing Conflict Window

The longer a transaction stays open, the higher the probability of conflicting with another transaction. Performing non-database work (API calls, file I/O) inside a transaction is a common anti-pattern that triggers 40001 at scale.

-- Anti-pattern: transaction held open too long
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders WHERE user_id = 1;
-- Imagine waiting 5 seconds for an external API here...
UPDATE orders SET status = 'processed' WHERE user_id = 1;
COMMIT; -- High chance of 40001 under load

-- Find long-running transactions right now
SELECT pid,
       now() - query_start AS duration,
       state,
       query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > INTERVAL '10 seconds'
ORDER BY duration DESC;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Implement retry logic — this is the primary fix recommended by PostgreSQL documentation:

-- PL/pgSQL retry wrapper example
DO $$
DECLARE
  attempts INTEGER := 0;
  done     BOOLEAN := FALSE;
BEGIN
  WHILE attempts < 5 AND NOT done LOOP
    BEGIN
      BEGIN ISOLATION LEVEL SERIALIZABLE;
        UPDATE accounts SET balance = balance - 100 WHERE id = 1;
        UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      COMMIT;
      done := TRUE;
    EXCEPTION
      WHEN serialization_failure THEN
        attempts := attempts + 1;
        PERFORM pg_sleep(0.05 * (2 ^ attempts)); -- exponential backoff
    END;
  END LOOP;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Use SELECT FOR UPDATE to convert optimistic conflicts into explicit locks when SERIALIZABLE is not strictly required:

-- Explicit locking prevents the conflict upfront
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42 AND quantity > 0;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always implement retry logic with exponential backoff at the application or middleware layer. Treat 40001 as a retryable signal, not a fatal error.
  • Keep transactions as short as possible. Fetch all required data before opening the transaction, perform only the necessary writes inside it, and commit immediately. Avoid mixing external I/O with open transactions.
  • Downgrade isolation level when appropriate. Use READ COMMITTED (PostgreSQL's default) unless your business logic explicitly requires SERIALIZABLE or REPEATABLE READ. Fewer guarantees mean fewer conflicts.
  • Monitor regularly with pg_stat_activity and pg_locks to catch long-running transactions before they cascade into frequent 40001 errors.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)