PostgreSQL ERROR: deadlock detected — What It Means and How to Fix It

If you’ve ever seen the error "ERROR: deadlock detected" in PostgreSQL, chances are your application suddenly failed in production.

This error is confusing, hard to reproduce, and often misunderstood. In this article, we’ll explain what it really means and how to fix it properly.

What Is a Deadlock?

A deadlock happens when two (or more) transactions block each other forever.

Each transaction holds a lock and waits for another lock that will never be released.

Simple Example

Transaction A locks row 1 and waits for row 2  
Transaction B locks row 2 and waits for row 1

PostgreSQL detects this situation and aborts one transaction to prevent an infinite wait.

The Exact Error Message

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.

This means PostgreSQL killed one transaction to resolve the deadlock.

Why Deadlocks Happen in PostgreSQL

1. Different Order of Updates

The most common cause is updating rows in different orders.

Example

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

-- Transaction B
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Each transaction locks rows in a different order — classic deadlock.

2. Long Transactions

The longer a transaction stays open, the longer it holds locks.

This increases the chance of collisions with other transactions.

3. Missing Indexes

Without proper indexes, PostgreSQL may lock more rows than necessary.

This widens the lock scope and increases deadlock probability.

How PostgreSQL Detects Deadlocks

PostgreSQL periodically checks for circular lock dependencies.

When detected, it automatically cancels one transaction and returns the error.

How to Fix Deadlocks (Step by Step)

Step 1: Always Update Rows in the Same Order

Ensure all transactions update tables and rows in a consistent order.

Step 2: Keep Transactions Short

• Avoid user interaction inside transactions
• Commit as early as possible

Step 3: Add Missing Indexes

Indexes reduce the number of locked rows.

CREATE INDEX idx_accounts_id ON accounts(id);

Step 4: Retry Failed Transactions

Deadlocks are expected in high-concurrency systems.

The correct approach is retrying the transaction.

Example (Pseudo-code)

try {
  runTransaction();
} catch (DeadlockError) {
  retryTransaction();
}

How to Debug Deadlocks

Enable Deadlock Logging

PostgreSQL can log deadlock details: