A deadlock occurs when two or more transactions wait for each other to release locks, creating a circular dependency. PostgreSQL automatically detects and terminates one transaction to break the deadlock. Fix it by ensuring transactions always access rows in a consistent order.
A PostgreSQL deadlock happens when two or more transactions are waiting for each other to release locks on the same resources, creating a circular dependency. When transaction A holds a lock that transaction B needs while transaction B holds a lock that transaction A needs, neither can proceed. PostgreSQL automatically detects this situation after the deadlock_timeout (default 1 second) and terminates one of the transactions to break the deadlock, allowing the other to complete. The terminated transaction receives an error that can be retried by the application.
The most effective solution is to guarantee all transactions access resources in the same order. For example, if you have tables A and B, always lock rows in A before rows in B across all parts of your application. This eliminates the circular dependencies that cause deadlocks.
When updating multiple tables, sort the data by primary key and process in the same order:
-- Good: consistent order
BEGIN;
SELECT * FROM orders WHERE id IN (10, 20) ORDER BY id FOR UPDATE;
SELECT * FROM customers WHERE id IN (5, 7) ORDER BY id FOR UPDATE;
UPDATE orders SET status = 'processed' WHERE id IN (10, 20);
UPDATE customers SET balance = balance - 100 WHERE id IN (5, 7);
COMMIT;Acquire locks upfront on all rows you will modify using SELECT FOR UPDATE. This prevents other transactions from locking the same rows later, avoiding deadlock scenarios:
BEGIN;
-- Lock the rows you plan to update
SELECT * FROM accounts WHERE account_id IN (100, 200) ORDER BY account_id FOR UPDATE;
-- Now update them without risk of others changing them
UPDATE accounts SET balance = balance - 50 WHERE account_id IN (100, 200);
COMMIT;For read-only operations that don't need exclusive locks, use FOR NO KEY UPDATE to reduce lock conflicts:
SELECT * FROM products WHERE id = 5 FOR NO KEY UPDATE;Reduce the time that locks are held by minimizing the operations within a transaction. Move non-database operations outside the transaction block:
-- Bad: long transaction with computation
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- ... process payment for 5 seconds (lock held entire time) ...
UPDATE accounts SET balance = balance - amount WHERE id = 1;
COMMIT;
-- Good: short transaction
compute_payment_offline(); -- outside transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - amount WHERE id = 1;
COMMIT;Configure your application to automatically retry transactions that encounter deadlocks. Use exponential backoff with jitter to avoid repeated collisions:
async function executeWithRetry(
operation: () => Promise<void>,
maxRetries = 3
): Promise<void> {
for (let attempt = 0; attempt < maxRetries; attempt++) {
try {
await operation();
return;
} catch (error) {
if (error.code !== '40P01') throw error; // Not a deadlock
if (attempt === maxRetries - 1) throw error;
const delay = Math.random() * Math.pow(2, attempt) * 1000;
await new Promise(resolve => setTimeout(resolve, delay));
}
}
}Configure PostgreSQL to log deadlock events and analyze the pattern:
-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_min_error_statement = 'error';
ALTER SYSTEM SET log_statement = 'all'; -- In development only
-- Reload configuration
SELECT pg_reload_conf();Then examine the PostgreSQL logs to see which transactions and SQL statements are involved in the deadlock. This helps identify the specific ordering issue in your application logic.
If you have high-concurrency scenarios where skipping locked rows is acceptable, use the SKIP LOCKED clause:
BEGIN;
-- Skip any rows already locked by other transactions
SELECT * FROM work_queue WHERE status = 'pending' LIMIT 10 FOR UPDATE SKIP LOCKED;
UPDATE work_queue SET status = 'processing' WHERE id IN (...);
COMMIT;This allows transactions to proceed with available rows instead of waiting, significantly reducing deadlock probability in competitive environments.
Important: There is NO configuration parameter that magically fixes deadlocks. The problem always depends on your application logic and transaction execution order, not database settings. The deadlock_timeout parameter (default 1 second) only controls how long PostgreSQL waits before checking for deadlocks, it does not prevent them. When a deadlock is detected, PostgreSQL terminates the transaction with the least CPU time, so the specific transaction that fails can be non-deterministic. For distributed systems spanning multiple PostgreSQL databases, each database detects deadlocks independently, which can complicate resolution in globally distributed transactions. In migrations, be especially careful to order operations the same way your application queries do—reversed ordering between migrations and queries is a common deadlock source.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL