PostgreSQL 40P01 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 40P01: deadlock detected

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency that can never be resolved on its own. PostgreSQL's built-in deadlock detector periodically checks for these cycles and resolves them by forcibly rolling back one of the involved transactions and returning error code 40P01. While a single deadlock won't bring down your database, frequent occurrences signal a serious design flaw that will hurt application reliability and throughput.

Top 3 Causes & SQL Examples

1. Inconsistent Lock Ordering

The most classic cause. Two transactions acquire locks on the same rows but in opposite orders, guaranteeing a deadlock under concurrent load.

-- Transaction A locks row 1, then tries to lock row 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction B (concurrent) locks row 2, then tries to lock row 1 → DEADLOCK
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- Fix: always lock rows in the same sorted order
BEGIN;
SELECT id FROM accounts
WHERE id IN (1, 2)
ORDER BY id   -- enforce consistent lock order
FOR UPDATE;

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

2. Implicit Locks from Foreign Keys

PostgreSQL acquires implicit row-level locks during INSERT, UPDATE, and DELETE. When foreign key relationships exist, modifying a parent row places a share lock on related child rows. If another transaction is simultaneously modifying those child rows, a deadlock cycle can form silently.

-- Risky pattern: child and parent updated in different transaction order
-- Fix: explicitly lock the parent row first
BEGIN;

-- Step 1: Explicitly lock the parent first
SELECT id FROM orders WHERE id = 100 FOR UPDATE;

-- Step 2: Then safely modify child rows
UPDATE order_items
SET quantity = quantity - 1
WHERE order_id = 100 AND product_id = 55;

UPDATE orders
SET total_amount = total_amount - 2500
WHERE id = 100;

COMMIT;

3. Unsorted Batch Updates

When multiple workers update the same set of rows without a consistent ordering, the probability of deadlocks increases dramatically with concurrency.

-- Dangerous: random row order in batch update
UPDATE inventory
SET stock = stock - 1
WHERE product_id IN (301, 205, 412, 108);

-- Safe: sort IDs before acquiring locks
BEGIN;
SELECT product_id FROM inventory
WHERE product_id IN (108, 205, 301, 412)
ORDER BY product_id   -- consistent ascending order
FOR UPDATE;

UPDATE inventory
SET stock = stock - 1
WHERE product_id IN (108, 205, 301, 412);
COMMIT;

Quick Fix Solutions

Implement retry logic — Deadlocks are transient by nature. Catch 40P01 at the application layer and retry with exponential backoff:

-- Monitor active lock waits to diagnose deadlocks
SELECT
  blocked.pid AS blocked_pid,
  blocking.pid AS blocking_pid,
  blocked.query AS blocked_query,
  blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Set lock_timeout to avoid indefinite waits and fail fast instead:

-- Session-level: fail if lock not acquired within 3 seconds
SET lock_timeout = '3s';

Prevention Tips

1. Keep transactions short. The longer a transaction holds locks, the higher the chance of conflicts. Never perform external API calls, user interactions, or heavy computation inside a transaction block. Use idle_in_transaction_session_timeout to auto-terminate stale transactions:

-- postgresql.conf
idle_in_transaction_session_timeout = '30s'

2. Always enforce a consistent resource acquisition order. Establish a team-wide convention — for example, always lock rows by ascending primary key. Document this rule and enforce it in code reviews. Combined with application-level retry logic for 40P01 and 40001 (serialization_failure), this two-layer defense handles the vast majority of deadlock scenarios in production.

Related Errors

Code	Name	Relationship
`40001`	serialization_failure	Similar retry requirement under SERIALIZABLE isolation
`55P03`	lock_not_available	Raised by `NOWAIT`; a deadlock-avoidance alternative
`57014`	query_canceled	Triggered by `lock_timeout`; used as a deadlock prevention safeguard

📖 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