How to fix Deadlock detected in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 28, 2025· 145 views

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.

Quick fix

Fixes Deadlock detected

-- 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;
  1. Ensure consistent transaction ordering
  2. Use explicit locking with FOR UPDATE
  3. Keep transactions short
  4. Implement application-level retry logic
postgresql — error
-- 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;
Deadlock detected

What this error means

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.

Typical symptoms

  • Applications receive "ERROR: deadlock detected" exceptions
  • Intermittent transaction failures during concurrent operations
  • Multiple users performing database updates simultaneously causing failures
  • Specific sequences of operations always trigger the error
  • One transaction rolls back unexpectedly while another succeeds

Common causes

  1. Transactions access rows in different orders, causing circular lock dependencies
  2. Long-running transactions hold locks while waiting for other resources
  3. Multiple concurrent transactions updating the same tables without coordination
  4. Using SELECT FOR UPDATE on multiple tables in non-deterministic order
  5. Complex application logic with many inter-dependent database operations
  6. Insufficient explicit locking leading to implicit lock escalation

How to fix "Deadlock detected"

1Ensure consistent transaction ordering

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:

sql
-- 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;
2Use explicit locking with FOR UPDATE

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:

sql
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:

sql
SELECT * FROM products WHERE id = 5 FOR NO KEY UPDATE;
3Keep transactions short

Reduce the time that locks are held by minimizing the operations within a transaction. Move non-database operations outside the transaction block:

sql
-- 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;
4Implement application-level retry logic

Configure your application to automatically retry transactions that encounter deadlocks. Use exponential backoff with jitter to avoid repeated collisions:

typescript
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));
    }
  }
}
5Enable deadlock logging for diagnosis

Configure PostgreSQL to log deadlock events and analyze the pattern:

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

6Consider using SKIP LOCKED for read-heavy workloads

If you have high-concurrency scenarios where skipping locked rows is acceptable, use the SKIP LOCKED clause:

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

Advanced notes

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.

Frequently asked questions

What does "Deadlock detected" mean?
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.
How do I fix "Deadlock detected"?
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.
What causes "Deadlock detected"?
Transactions access rows in different orders, causing circular lock dependencies Long-running transactions hold locks while waiting for other resources Multiple concurrent transactions updating the same tables without coordination
Is the "Deadlock detected" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading