PostgreSQL Deadlock Detected (Error 40P01): Causes and Solutions

By Sharon Ben-Moshe · September 14, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Diagnose and resolve PostgreSQL Error 40P01 (deadlock detected). Learn how to order resource locks, shorten transaction windows, and tune deadlock_timeout.

Quick answer

PostgreSQL Error 40P01 (deadlock detected) occurs when two or more transactions hold locks that the other transactions need to proceed, creating a circular wait cycle. To eliminate deadlocks, enforce identical lock ordering across all queries (e.g. updating rows sorted by primary key), keep transactions small and fast, and implement application-level retry logic with exponential backoff.

FAQs

How does PostgreSQL detect a deadlock?

PostgreSQL runs a deadlock detection cycle whenever a transaction waits for a lock longer than the configured `deadlock_timeout` (default 1 second). If it identifies a circular dependency graph, it aborts one transaction with error 40P01 to release locks.

Why is consistent lock ordering critical?

If Transaction A updates Account 1 then Account 2, while Transaction B updates Account 2 then Account 1 concurrently, they will deadlock. If both transactions always update accounts in ascending order (WHERE id IN (1, 2) ORDER BY id), deadlocks are impossible.

Does PostgreSQL FOR UPDATE cause deadlocks?

SELECT FOR UPDATE can cause deadlocks if multiple sessions lock multiple rows in different orders. Sorting rows prior to locking or using NOWAIT / SKIP LOCKED eliminates blocking.

Should I increase deadlock_timeout to fix deadlocks?

No. Increasing `deadlock_timeout` does not prevent deadlocks; it only delays their detection, causing blocked transactions to hang longer before failing.

SQL Errors

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

By Sharon Ben-Moshe · Sep 29, 2026

Fix Google BigQuery's "Unrecognized name: column" error. Learn alias visibility rules across WHERE and QUALIFY, case sensitivity, and struct unnesting.

Read post

SQL Errors

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

By Sharon Ben-Moshe · Sep 26, 2026

Understand SQLite Error 20 (datatype mismatch). Learn how SQLite type affinity, CHECK constraints, and STRICT tables enforce data integrity.

Read post

SQL Errors

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Always access and lock shared tables and rows in a globally consistent order (e.g., ORDER BY id).

Keep transaction execution time minimal: do not perform external HTTP requests or heavy processing inside a database transaction.

Use SELECT ... FOR UPDATE SKIP LOCKED for high-throughput background queue processing.

Configure application retry logic with randomized exponential backoff for unavoidable serialization and deadlock failures.

A classic deadlock scenario explained

Imagine an e-commerce inventory transfer between warehouse A and warehouse B:

How circular lock ordering triggers deadlocks

-- Transaction 1 (Transfer from Warehouse 10 to Warehouse 20)
BEGIN;
UPDATE inventory SET stock = stock - 5 WHERE warehouse_id = 10; -- Locks row 10
-- Meanwhile, Transaction 2 runs:
-- UPDATE inventory SET stock = stock - 3 WHERE warehouse_id = 20; -- Locks row 20
UPDATE inventory SET stock = stock + 5 WHERE warehouse_id = 20; -- BLOCKED by Tx 2!

-- Transaction 2 (Transfer from Warehouse 20 to Warehouse 10)
-- Tx 2 now attempts:
UPDATE inventory SET stock = stock + 3 WHERE warehouse_id = 10; -- DEADLOCK! Tx 1 holds row 10!

Eliminating deadlocks with deterministic sorting

When locking multiple rows in a batch, sort the IDs before acquiring row-level locks so every transaction requests rows in the exact same sequence:

Enforcing deterministic row locking order

-- Lock multiple inventory records in strict ascending order
SELECT id, stock
FROM inventory
WHERE id IN (10, 20)
ORDER BY id ASC
FOR UPDATE;

-- Proceed with updates safely: no other transaction can form a reverse cycle
UPDATE inventory SET stock = stock - 5 WHERE id = 10;
UPDATE inventory SET stock = stock + 5 WHERE id = 20;
COMMIT;

-- Claim the next pending job without blocking concurrent workers WITH next_job AS ( SELECT id FROM background_jobs WHERE status = 'pending' ORDER BY priority DESC, created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED ) UPDATE background_jobs SET status = 'processing', claimed_at = NOW() WHERE id = (SELECT id FROM next_job) RETURNING *;

PostgreSQL Deadlock Detected (Error 40P01): Causes and Solutions

Quick answer

FAQs

How does PostgreSQL detect a deadlock?

Why is consistent lock ordering critical?

Does PostgreSQL FOR UPDATE cause deadlocks?

Should I increase deadlock_timeout to fix deadlocks?

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

Key takeaways

A classic deadlock scenario explained

Eliminating deadlocks with deterministic sorting

Deadlock-free task queues with SKIP LOCKED

Official references

Quick answer

FAQs

How does PostgreSQL detect a deadlock?

Why is consistent lock ordering critical?

Does PostgreSQL FOR UPDATE cause deadlocks?

Should I increase deadlock_timeout to fix deadlocks?

Keep reading

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

Key takeaways

A classic deadlock scenario explained

Eliminating deadlocks with deterministic sorting

Deadlock-free task queues with SKIP LOCKED

Related SQL guides

Official references