SQL Deadlock Detected (tested)

A deadlock happens when two transactions each hold a lock the other one needs: transaction A is waiting for a row locked by B, and B is waiting for a row locked by A, so neither can ever finish. The database can't resolve this by waiting longer — it has to pick one transaction to kill. We reproduced this below with two real psql sessions, not a simulation.

1. The setup: two transactions, opposite lock order

We used a two-row table and had one transaction update row 1 then row 2, while a second transaction updated row 2 then row 1 — the classic opposite-order pattern that causes deadlocks:

CREATE TABLE accounts (id INT PRIMARY KEY, balance NUMERIC);
INSERT INTO accounts VALUES (1, 100), (2, 200);

-- Session A                          -- Session B
BEGIN;                                 BEGIN;
UPDATE accounts SET balance = balance - 10
  WHERE id = 1;    -- locks row 1
                                        UPDATE accounts SET balance = balance - 20
                                          WHERE id = 2;    -- locks row 2
UPDATE accounts SET balance = balance + 10
  WHERE id = 2;    -- blocks: B holds row 2
                                        UPDATE accounts SET balance = balance + 20
                                          WHERE id = 1;    -- blocks: A holds row 1

At this point both sessions are stuck waiting on each other. Nothing times out on its own — PostgreSQL's deadlock detector has to step in.

2. Reproduced: the exact error

About a second after the mutual wait began (PostgreSQL's deadlock_timeout defaults to 1s), session A's blocked statement returned this and the whole transaction rolled back automatically:

ERROR:  deadlock detected
DETAIL:  Process 92 waits for ShareLock on transaction 734; blocked by process 93.
Process 93 waits for ShareLock on transaction 733; blocked by process 92.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,4) in relation "accounts"
ROLLBACK

Session B's blocked statement then went through immediately (A's rollback freed the row it needed) and B committed normally — no error, no manual intervention:

UPDATE 1
COMMIT

PostgreSQL doesn't kill both sides or hang forever: it always picks exactly one transaction as the victim and lets the other proceed.

3. The server log has more detail than the client error

The HINT says "See server log for query details" — and it does. The log line for the same event includes the actual SQL each process was running when it got blocked, which the client-side error doesn't show:

ERROR:  deadlock detected
DETAIL:  Process 92 waits for ShareLock on transaction 734; blocked by process 93.
	Process 93 waits for ShareLock on transaction 733; blocked by process 92.
	Process 92: UPDATE accounts SET balance = balance + 10 WHERE id = 2;
	Process 93: UPDATE accounts SET balance = balance + 20 WHERE id = 1;
HINT:  See server log for query details.

If you only see the client-side error in application logs, check the database server log too — it names both conflicting statements, which is usually enough to spot the opposite lock order immediately.

4. Confirming only one side committed

Querying the table after both sessions finished shows exactly one transaction's effect landed, not a partial mix of both:

SELECT * FROM accounts;
 id | balance
----+---------
  2 |     190
  1 |     110
(2 rows)

Starting balances were 100/200. Only B's full transaction (-20 on id 2, +20 on id 1) applied; A's update to id 1 was rolled back along with everything else in its transaction. This is the important part: a deadlock victim's transaction is not partially applied — PostgreSQL rolls it back completely, so your data never ends up half-updated.

5. The fix: always lock rows in the same order

The deadlock existed only because A updated (1, then 2) while B updated (2, then 1). If every transaction that touches both rows locks them in the same order — say, always lowest id first — they queue up for each other instead of circling:

-- Both transactions now do this, regardless of which account the caller named first:
UPDATE accounts SET balance = balance - 10 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 10 WHERE id = GREATEST(1, 2);

We re-ran the same concurrent test with both sessions updating in ascending id order: no deadlock, both transactions committed, one simply waited briefly for the other's lock to release instead of erroring.

6. Application-side: retry on deadlock, don't just fail

Since the database deliberately kills one side, your application code should treat a deadlock like a transient conflict, not a permanent failure: catch it (PostgreSQL's SQLSTATE for this is 40P01) and retry the whole transaction from the start. Most ORMs and connection libraries expose this SQLSTATE specifically so you don't have to pattern-match the error text.

7. Other databases (per their docs, not measured here)

MySQL (InnoDB) raises ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction and, like Postgres, automatically rolls back one transaction and expects the client to retry. SQL Server raises error 1205 and also auto-kills one transaction as "deadlock victim." The detection mechanism differs internally, but the shape of the fix is identical everywhere: consistent lock ordering to prevent it, retry logic to recover from it.

Written with AI assistance. Measured on 2026-10-03 with PostgreSQL 16.15 (official Docker image), using two concurrent psql sessions against the same table. Every error message, DETAIL line, and server log excerpt below is copy-pasted output, not paraphrased. PlainHeaders is our extension; PostgreSQL and Docker are free.