PostgreSQL Deadlock Detected: Quick Fix That Works

You're Here Because PostgreSQL Threw a Deadlock Error

It's frustrating. You're running a normal workload and bam — ERROR: 40P01: deadlock detected. Your app hangs, users complain, and you need this fixed yesterday. I've seen this dozens of times. Here's the fix that works 90% of the time.

The Fix: Lock in the Same Order Every Time

The culprit is almost always transaction A grabs row 1, then tries row 2, while transaction B grabs row 2, then tries row 1. PostgreSQL can't resolve that without killing one. The fix is simple: make every transaction lock rows in the exact same order.

For example, say you have a table called orders and two transactions update different rows:

Transaction A:

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'pending' WHERE id = 2;
COMMIT;

Transaction B (bad):

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 2;
UPDATE orders SET status = 'shipped' WHERE id = 1;
COMMIT;

See the problem? Transaction B locks id=2 first, then id=1. Transaction A does the opposite. Deadlock city.

The fix: Always sort the row IDs before updating. In your application code, do something like this:

-- Pseudo-code:
ids = [1, 2]
sorted_ids = ids.sort()  -- [1, 2] always
BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = sorted_ids[0];
UPDATE orders SET status = 'pending' WHERE id = sorted_ids[1];
COMMIT;

Now both transactions lock id=1 first, then id=2. Deadlock gone. Works across any number of rows — just sort the IDs alphabetically or numerically.

Why This Works

PostgreSQL uses row-level locking with UPDATE and DELETE commands. When a transaction updates a row, it holds a lock until that transaction commits or rolls back. If two transactions need the same rows but lock them in different orders, PostgreSQL detects the circular wait after a few seconds (default deadlock_timeout is 1 second). It then kills one transaction — the one with the least work done — and throws error 40P01.

By enforcing a consistent lock order, you eliminate the circular wait entirely. PostgreSQL never sees a deadlock because no transaction is waiting for a lock held by another transaction in a cycle. It's that simple.

Less Common Variations

Sometimes the deadlock isn't about row order. Here are three other scenarios I've run into:

1. Foreign Key Locks

If you have a foreign key from orders to customers, deleting a customer can lock rows in orders. If two transactions delete different customers but those customers share order rows, you can get a deadlock. The fix: delete related rows first, or use ON DELETE CASCADE with caution.

2. Implicit Locks from Indexes

When you update a row, PostgreSQL also locks index entries. If you have a unique index on email and two transactions try to update the same email to different values, you can deadlock even if the row IDs are ordered. The fix: use SELECT ... FOR UPDATE on the row first, then update.

3. Long-Running Transactions with Many Locks

If one transaction holds dozens of row locks and another needs just one of them, the first transaction might block the second. If the first transaction then tries to lock a row the second holds, deadlock. The fix: keep transactions short. Break big updates into batches of 1000 rows with commits between batches.

Prevention: Set These Up Now

Don't wait for the next deadlock. Here's what I'd do on every PostgreSQL server:

• Set deadlock_timeout to 500ms — PostgreSQL will detect deadlocks faster. Default is 1 second, which is too long for busy apps. Add to postgresql.conf: deadlock_timeout = 500ms.
• Use SET lock_timeout = '2s' in your application session. This prevents transactions from waiting forever. If a lock can't be acquired in 2 seconds, the transaction aborts. Better than a deadlock.
• Add a monitoring query to check for blocked processes. Run this weekly:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE state = 'active'
  AND pg_blocking_pids(pid) IS NOT NULL;

If you see blocked pids regularly, you have a locking pattern problem. Fix it before it turns into deadlocks.

• Log deadlocks — set log_lock_waits = on and log_min_duration_statement = 1000. This logs any query that waits over 1 second for a lock. Review those logs weekly.

One more thing: if you're using an ORM like Hibernate or Django ORM, check if it orders updates automatically. Some don't. You'll need to sort the IDs in your code manually. Test it in staging — this one fix will save you hours of debugging.