PostgreSQL 40P01 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 40P01: Deadlock Detected — Causes, Fixes & Prevention

PostgreSQL error 40P01 occurs when two or more transactions are waiting on each other's locks, creating a circular dependency that can never resolve on its own. PostgreSQL's deadlock detector periodically checks for such cycles and resolves them by forcibly rolling back one of the transactions. The victim transaction receives this error, while the surviving transaction continues normally.

Top 3 Causes

1. Inconsistent Row Locking Order

The most common cause is two transactions locking the same rows in opposite orders.

-- ❌ Deadlock-prone pattern
-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction B (concurrent)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- locks row 2 first
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- waits for row 1
COMMIT;

-- ✅ Fix: Always lock rows in ascending primary key order
BEGIN;
SELECT * 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. Inconsistent Multi-Table Locking Order

When multiple tables are modified within a transaction, different code paths may acquire table-level locks in different orders.

-- ❌ Service A locks orders → inventory
BEGIN;
UPDATE orders SET status = 'processing' WHERE order_id = 101;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 55;
COMMIT;

-- ❌ Service B locks inventory → orders (opposite order = deadlock risk)
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 55;
UPDATE orders SET status = 'reserved' WHERE order_id = 101;
COMMIT;

-- ✅ Fix: Enforce a global locking order (e.g., always orders → inventory)
BEGIN;
SELECT * FROM orders WHERE order_id = 101 FOR UPDATE;
SELECT * FROM inventory WHERE product_id = 55 FOR UPDATE;
UPDATE orders SET status = 'processing' WHERE order_id = 101;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 55;
COMMIT;

3. Missing Indexes Causing Excessive Lock Scope

Without proper indexes, a simple UPDATE can escalate into a full sequential scan, locking far more rows than intended.

-- ❌ No index → sequential scan → locks too many rows
UPDATE orders SET status = 'done'
WHERE customer_email = 'user@example.com';

-- ✅ Fix: Add a targeted index to reduce lock scope
CREATE INDEX CONCURRENTLY idx_orders_email ON orders(customer_email);

-- ✅ Also consider batching large updates
UPDATE orders SET status = 'done'
WHERE id IN (
    SELECT id FROM orders
    WHERE customer_email = 'user@example.com'
    ORDER BY id
    LIMIT 500
);

Quick Fix: Retry Logic

Deadlocks can't always be fully eliminated, so always implement retry logic in your application or stored procedures.

CREATE OR REPLACE FUNCTION safe_transfer(
    from_id INT, to_id INT, amount NUMERIC
) RETURNS VOID AS $$
DECLARE
    retries INT := 0;
BEGIN
    LOOP
        BEGIN
            SELECT * FROM accounts
            WHERE id IN (from_id, to_id)
            ORDER BY id FOR UPDATE;

            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;
            RETURN;
        EXCEPTION
            WHEN deadlock_detected THEN
                retries := retries + 1;
                IF retries >= 3 THEN
                    RAISE;
                END IF;
                PERFORM pg_sleep(0.05 * retries);
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

1. Standardize lock ordering across your entire codebase.
Document a global rule (e.g., always lock tables/rows by ascending primary key or alphabetical table name) and enforce it during code reviews. Set lock_timeout to prevent indefinite waits.

SET lock_timeout = '5s';
SET deadlock_timeout = '1s'; -- how often PostgreSQL checks for deadlocks

2. Keep transactions short and focused.
Never perform external API calls, file I/O, or user interaction inside a transaction. Pre-fetch any required data before opening the transaction, and commit as quickly as possible. Monitor pg_stat_activity and your PostgreSQL logs regularly to catch recurring deadlock patterns early.

-- Monitor active lock waits
SELECT pid, usename, state, wait_event, LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Related Errors

40001 — serialization_failure: Similar retry-required error under SERIALIZABLE isolation.
55P03 — lock_not_available: Raised when NOWAIT locking fails immediately.
57014 — query_canceled: Triggered when lock_timeout is exceeded.

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