PostgreSQL 40000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 40000: Transaction Rollback — What It Is and How to Fix It

PostgreSQL error code 40000 (transaction_rollback) is raised when a transaction cannot be completed and must be forcibly rolled back to preserve data integrity. This is a parent-level error class that encompasses several critical sub-errors including serialization failures (40001) and deadlocks (40P01). Understanding this error and its causes is essential for building resilient, production-grade database applications.

Top 3 Causes

1. Serialization Failure (40001)

When using SERIALIZABLE or REPEATABLE READ isolation levels, two concurrent transactions reading and writing the same data can conflict. PostgreSQL rolls back one of them to maintain consistency.

-- Transaction A and B both run concurrently
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;
-- If another transaction modified these rows concurrently,
-- PostgreSQL throws: ERROR: could not serialize access due to concurrent update

Fix: Implement a retry loop in your application whenever SQLSTATE 40001 is returned.

-- Check current isolation level
SHOW transaction_isolation;

-- Use lower isolation level if full serializability isn't needed
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Deadlock Detected (40P01)

A deadlock occurs when two transactions each hold a lock the other needs, creating a circular wait. PostgreSQL automatically detects this after deadlock_timeout (default: 1 second) and kills one transaction.

-- ❌ Deadlock-prone pattern
-- Session 1
BEGIN;
UPDATE orders SET status = 'done' WHERE order_id = 1;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10; -- waits for Session 2

-- Session 2 (runs simultaneously)
BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10;
UPDATE orders SET status = 'done' WHERE order_id = 1; -- waits for Session 1
-- DEADLOCK! PostgreSQL rolls back one session.

-- ✅ Fix: Always access tables/rows in the same order
BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
SELECT * FROM inventory WHERE item_id = 10 FOR UPDATE;
UPDATE orders SET status = 'done' WHERE order_id = 1;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10;
COMMIT;

3. Aborted Transaction State

Any error inside a transaction block (constraint violation, syntax error, etc.) puts the transaction into an aborted state. All subsequent commands are ignored until a ROLLBACK is issued.

-- ❌ Wrong: continuing after an error
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'alice@example.com');
INSERT INTO users (id, email) VALUES (1, 'bob@example.com'); -- UNIQUE violation!
SELECT * FROM users; -- ERROR: current transaction is aborted

-- ✅ Right: use SAVEPOINT for partial rollback
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'alice@example.com');

SAVEPOINT sp1;
INSERT INTO users (id, email) VALUES (1, 'bob@example.com'); -- fails
ROLLBACK TO SAVEPOINT sp1; -- recover without killing the whole transaction

INSERT INTO users (id, email) VALUES (2, 'bob@example.com'); -- succeeds
COMMIT;

Quick Fix Solutions

-- Monitor rollback rates — high ratio signals a problem
SELECT
    datname,
    xact_commit,
    xact_rollback,
    ROUND(xact_rollback::numeric /
        NULLIF(xact_commit + xact_rollback, 0) * 100, 2) AS rollback_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Find transactions currently waiting on locks
SELECT pid, usename, state, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Set timeouts to prevent long-running transactions
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '30s';

Prevention Tips

1. Always implement retry logic for 40001 and 40P01
Catch these specific SQLSTATE codes in your application layer and retry with exponential backoff. Most ORMs and database drivers support this natively.

2. Keep transactions short and access objects in a consistent order
Long-running transactions dramatically increase the chance of conflicts and deadlocks. Access tables and rows in the same order across all transactions, and avoid placing external I/O operations (HTTP calls, file writes) inside transaction blocks.

-- Enable lock wait logging for proactive monitoring
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

Related Error Codes

Code	Name	Summary
40001	serialization_failure	Concurrent serializable transaction conflict
40P01	deadlock_detected	Circular lock wait between transactions
40002	transaction_integrity_constraint_violation	Integrity check failed mid-transaction
25006	read_only_sql_transaction	Write attempted in a read-only transaction

All of these belong to SQL error class 40, and the universal solution pattern is: detect → rollback → retry.

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

DEV Community