How to Fix Deadlock Detected Error in PostgreSQL

A deadlock detected error in PostgreSQL happens when two or more transactions wait for each other to release locks, causing a standstill. To fix it, ensure transactions acquire locks in a consistent order and keep transactions short to avoid conflicts.

Why This Happens

A deadlock occurs when two transactions each hold a lock on a resource the other needs, and neither can proceed until the other releases its lock. This creates a cycle of waiting that PostgreSQL detects and stops by aborting one transaction.

sql

BEGIN;
-- Transaction 1 locks row A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction 2 locks row B
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 1 tries to lock row B (blocked)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2 tries to lock row A (blocked)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Output

ERROR: deadlock detected
DETAIL: Process 1 waits for ShareLock on transaction 2; blocked by process 2.
Process 2 waits for ShareLock on transaction 1; blocked by process 1.
HINT: See server log for query details.

The Fix

To fix deadlocks, ensure all transactions lock resources in the same order. For example, always update account with id=1 before id=2. Also, keep transactions short and commit quickly to reduce lock time.

sql

BEGIN;
-- Both transactions lock row A first
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Then both lock row B
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Output

COMMIT

Prevention

To avoid deadlocks in the future:

• Always access tables and rows in a consistent order across transactions.
• Keep transactions as short as possible to reduce lock duration.
• Use explicit locking commands like SELECT ... FOR UPDATE carefully and consistently.
• Monitor and analyze deadlock logs to identify problematic queries.

Related Errors

Other common locking issues include:

• Lock timeout: When a transaction waits too long for a lock and times out. Fix by increasing timeout or optimizing queries.
• Serialization failure: Occurs in serializable isolation level when concurrent transactions conflict. Fix by retrying the transaction.

Key Takeaways

Deadlocks happen when transactions wait on each other’s locks creating a cycle.

Fix deadlocks by locking resources in the same order in all transactions.

Keep transactions short to minimize lock holding time.

Use consistent locking strategies and monitor logs to prevent deadlocks.

Related errors like lock timeouts and serialization failures require different handling.