ERROR: deadlock detected means two (or more) transactions are each waiting for a lock the other holds. PostgreSQL detects this and kills one transaction.

Why this happens

Deadlocks occur when two transactions acquire locks in different orders, creating a circular dependency. Transaction A locks row 1 then waits for row 2, while Transaction B locks row 2 then waits for row 1. PostgreSQL’s deadlock detector runs periodically (default 1 second) and aborts the youngest transaction to break the cycle.

Fix 1: Always lock rows in the same order

-- ❌ Different order causes deadlocks
-- 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;

-- ✅ Always lock by ascending ID
-- Both transactions: lock id=1 first, then id=2
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Fix 2: Keep transactions short

Long transactions hold locks longer, increasing deadlock probability. Move non-database work outside the transaction.

Fix 3: Use advisory locks for complex operations

SELECT pg_advisory_lock(hashtext('transfer_' || least(1,2) || '_' || greatest(1,2)));
-- Do your work
SELECT pg_advisory_unlock(hashtext('transfer_' || least(1,2) || '_' || greatest(1,2)));

How to find deadlocks

-- Check PostgreSQL logs for deadlock details
-- Or query current locks:
SELECT pid, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted;

Alternative solution: Use NOWAIT or SKIP LOCKED

If your logic allows it, avoid waiting entirely:

-- Fail immediately instead of waiting for a lock
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip locked rows (useful for job queues)
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;

Prevention

• Set deadlock_timeout in postgresql.conf to tune detection speed (default is 1s).
• Log all deadlocks with log_lock_waits = on to identify patterns before they become frequent.

Related: PostgreSQL cheat sheet · PostgreSQL Connection Refused · PostgreSQL: Relation Does Not Exist · What is PostgreSQL