How to Resolve Deadlock in PostgreSQL: Simple Fixes and Prevention

A deadlock in PostgreSQL happens when two or more transactions wait for each other to release locks, causing a standstill. To resolve it, identify conflicting queries and change their order or lock strategy to avoid circular waits. Using consistent locking order and shorter transactions helps prevent deadlocks.

Why This Happens

A deadlock occurs when two transactions each hold a lock the other needs, and neither can proceed. This creates a cycle where both wait forever, so PostgreSQL detects this and aborts one transaction to break the cycle.

For example, if Transaction A locks row 1 and waits for row 2, while Transaction B locks row 2 and waits for row 1, a deadlock happens.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction A holds lock on account 1

-- In another session:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Transaction B holds lock on account 2

-- Back to Transaction A:
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Transaction A waits for lock on account 2

-- Back to Transaction B:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction B waits for lock on account 1

Output

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67890.
Process 67890 waits for ShareLock on transaction 12345; blocked by process 12345.
HINT: See server log for query details.
CONTEXT: while updating tuple (1,2) in relation "accounts"

The Fix

To fix deadlocks, ensure all transactions lock rows in the same order. This prevents circular waiting. Also, keep transactions short and commit quickly to release locks sooner.

sql

BEGIN;
-- Lock accounts in consistent order
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- In another session:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Output

COMMIT
COMMIT

Prevention

To avoid deadlocks in the future:

• Always access tables and rows in the same order in all transactions.
• Keep transactions short and avoid user interaction during transactions.
• Use explicit SELECT FOR UPDATE locks carefully and consistently.
• Monitor deadlock logs and analyze queries causing deadlocks.
• Consider using retry logic in your application to handle deadlock errors gracefully.

Related Errors

Other locking issues similar to deadlocks include:

• Lock timeout: When a transaction waits too long for a lock and times out. Fix by increasing timeout or optimizing queries.
• Serialization failure: Happens 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 rows in a consistent order across transactions.

Keep transactions short to reduce lock holding time.

Use retry logic in your application to handle deadlock errors smoothly.

Monitor logs to identify and fix deadlock causes early.