PostgreSQL 40000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 40000: Transaction Rollback — Causes, Fixes, and Prevention

PostgreSQL error code 40000 (transaction_rollback) is a broad error class indicating that a transaction was aborted and all its changes were rolled back. It serves as a parent class for more specific errors like 40001 (serialization failure) and 40P01 (deadlock detected). Understanding this error is essential for building resilient, high-concurrency PostgreSQL applications.

---

Top 3 Causes

1. Deadlock Detected (40P01)

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency. PostgreSQL automatically detects this and rolls back one of the transactions to break the cycle.

-- Diagnose deadlocks and lock waits
SELECT
    a.pid,
    a.usename,
    a.query,
    a.wait_event,
    now() - a.query_start AS duration
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY duration DESC;

-- Check pg_log for deadlock messages
-- log_lock_waits = on in postgresql.conf is recommended

Fix: Always access tables and rows in a consistent order across all transactions.

-- BAD: Inconsistent lock order (deadlock risk)
-- Transaction A: locks row 1, then row 2
-- Transaction B: locks row 2, then row 1

-- GOOD: Consistent lock order using ORDER BY
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

---

2. Serialization Failure (40001)

When using SERIALIZABLE or REPEATABLE READ isolation levels, PostgreSQL rolls back transactions that would violate serialization guarantees. This is expected behavior and must be handled with retry logic at the application level.

-- Set isolation level explicitly
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42 AND stock > 0;
COMMIT;

-- Implement retry logic in PL/pgSQL
CREATE OR REPLACE FUNCTION safe_update_with_retry(p_product_id INT)
RETURNS VOID AS $$
DECLARE
    v_retries INT := 0;
BEGIN
    LOOP
        BEGIN
            UPDATE inventory
            SET stock = stock - 1
            WHERE product_id = p_product_id AND stock > 0;
            EXIT; -- success, exit loop
        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                IF v_retries >= 5 THEN
                    RAISE; -- give up after 5 retries
                END IF;
                v_retries := v_retries + 1;
                PERFORM pg_sleep(0.05 * v_retries); -- exponential backoff
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

3. Unhandled Exceptions Causing Implicit Rollback

Any unhandled error inside a transaction block causes PostgreSQL to roll back the entire transaction. This is especially problematic in batch processing jobs where a single bad row can undo thousands of successful operations.

-- BAD: One failure rolls back everything
BEGIN;
INSERT INTO users (email) VALUES ('[e-mail]');
INSERT INTO users (email) VALUES ('[e-mail]'); -- violates unique constraint
INSERT INTO users (email) VALUES ('[e-mail]');
COMMIT; -- All three inserts are rolled back!

-- GOOD: Use SAVEPOINTs to isolate failures
BEGIN;
INSERT INTO users (email) VALUES ('[e-mail]');
SAVEPOINT sp1;

BEGIN
    INSERT INTO users (email) VALUES ('[e-mail]');
EXCEPTION
    WHEN unique_violation THEN
        ROLLBACK TO SAVEPOINT sp1;
        RAISE NOTICE 'Skipping duplicate email.';
END;

INSERT INTO users (email) VALUES ('[e-mail]');
COMMIT; -- user1 and user3 are committed successfully

---

Quick Fix Solutions

-- 1. Set timeouts to prevent long-running lock waits
SET lock_timeout = '5s';
SET statement_timeout = '30s';
SET deadlock_timeout = '1s';

-- 2. Use SKIP LOCKED for queue-style workloads
BEGIN;
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
COMMIT;

-- 3. Identify blocking queries immediately
SELECT
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

---

Prevention Tips

1. Keep transactions short and focused. Never include external API calls, file I/O, or user interaction inside a transaction block. The longer a transaction holds locks, the higher the chance of conflicts.

2. Enable lock wait logging by setting log_lock_waits = on and deadlock_timeout = 1s in postgresql.conf. This helps you proactively identify problematic queries before they cause widespread rollbacks in production.
   

-- Verify your configuration
SHOW deadlock_timeout;
SHOW log_lock_waits;

---

Related Error Codes

Code	Name	Description
40001	serialization_failure	Most common child of 40000; retry required
40P01	deadlock_detected	Circular lock dependency detected
40002	transaction_integrity_constraint_violation	Deferred constraint failure
55P03	lock_not_available	Lock unavailable with NOWAIT option

---

📖 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.