PostgreSQL 40002 Error: Causes and Solutions Complete Guide

PostgreSQL Error 40002: Transaction Integrity Constraint Violation

PostgreSQL error code 40002 (transaction_integrity_constraint_violation) occurs when a transaction is aborted because it would violate data integrity guarantees enforced at the transaction level. This typically happens under higher isolation levels such as SERIALIZABLE or REPEATABLE READ, where PostgreSQL's concurrency control detects conflicts between simultaneous transactions. Unlike statement-level constraint errors (23xxx), this error is inherently about concurrency — the same operation might succeed when retried.

---

Top 3 Causes

1. Serializable Isolation Conflicts (SSI Cycle Detection)

PostgreSQL's Serializable Snapshot Isolation (SSI) detects read/write dependency cycles between concurrent transactions. When a cycle is found, one transaction is chosen as the victim and aborted with error 40002.

-- Transaction A (Session 1)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE type = 'savings';
INSERT INTO accounts (type, balance) VALUES ('savings', 500);
COMMIT; -- May fail with 40002 if Transaction B runs concurrently

-- Transaction B (Session 2) -- running at the same time
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE type = 'savings';
INSERT INTO accounts (type, balance) VALUES ('savings', 300);
COMMIT; -- One of these commits will trigger 40002

2. Concurrent Write-Write Conflicts

When two transactions attempt to modify the same row simultaneously under REPEATABLE READ or higher, PostgreSQL detects the conflict and aborts the later transaction to maintain consistency.

-- Both sessions run this concurrently on the same row
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE inventory
SET stock_quantity = stock_quantity - 10
WHERE product_id = 42;

COMMIT;
-- The second session to commit will receive error 40002

3. Concurrent Unique Constraint Violations

When two transactions attempt to insert the same unique key value simultaneously, PostgreSQL cannot allow both to proceed without violating the unique constraint.

-- Session 1 and Session 2 both run simultaneously
BEGIN;
INSERT INTO users (email, username)
VALUES ('[e-mail]', 'user_a');
COMMIT;
-- One session will fail with 40002 (or 40001/23505 depending on isolation level)

-- Safer approach using ON CONFLICT
INSERT INTO users (email, username)
VALUES ('[e-mail]', 'user_a')
ON CONFLICT (email)
DO UPDATE SET username = EXCLUDED.username, updated_at = NOW();

---

Quick Fix Solutions

Implement retry logic — this is the most critical fix. Error 40002 (and 40001) are designed to be retried by the application.

-- PL/pgSQL retry wrapper example
CREATE OR REPLACE FUNCTION execute_with_retry(max_attempts INT DEFAULT 5)
RETURNS VOID AS $$
DECLARE
    attempt INT := 0;
BEGIN
    LOOP
        BEGIN
            -- Your transaction logic here
            BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

            COMMIT;
            RETURN; -- success, exit loop

        EXCEPTION
            WHEN transaction_integrity_constraint_violation
              OR serialization_failure THEN
                attempt := attempt + 1;
                IF attempt >= max_attempts THEN
                    RAISE;
                END IF;
                PERFORM pg_sleep(0.05 * attempt); -- back-off
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Use SELECT FOR UPDATE to lock rows proactively and avoid conflicts:

BEGIN;

-- Lock the row first to prevent concurrent modifications
SELECT * FROM inventory
WHERE product_id = 42
FOR UPDATE;

UPDATE inventory
SET stock_quantity = stock_quantity - 10
WHERE product_id = 42;

COMMIT;

---

Prevention Tips

1. Use the right isolation level. Don't default to SERIALIZABLE for every transaction — reserve it for operations that truly require full serializability. READ COMMITTED (PostgreSQL's default) handles most use cases and is far less prone to 40002 errors.

-- Only use SERIALIZABLE when absolutely necessary
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- high conflict risk
-- vs.
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- safer default

2. Minimize transaction duration. Keep transactions short and focused. Move external API calls, file I/O, and heavy computations outside of transaction blocks to reduce the window of conflict.

-- Bad: long-lived transaction
BEGIN;
SELECT * FROM orders; -- do heavy processing in application...
UPDATE orders SET status = 'processed' WHERE order_id = 1;
COMMIT;

-- Good: short, focused transaction
-- (do processing outside the transaction block)
BEGIN;
UPDATE orders SET status = 'processed' WHERE order_id = 1;
COMMIT;

---

Related Errors

Code	Name	Relationship
40001	serialization_failure	Most closely related; also requires retry logic
40P01	deadlock_detected	Concurrency issue; also resolved by retry
23000	integrity_constraint_violation	Statement-level constraint error, not transaction-level
55P03	lock_not_available	Occurs with NOWAIT locking when lock can't be acquired

Key Takeaway: Error 40002 is not a bug — it's PostgreSQL doing its job protecting data integrity. Always build retry logic into your application for any transaction using isolation levels above READ COMMITTED.

---

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