A deadlock requires a cycle of incompatible waits. If one transaction holds a row while another waits, the holder can still finish and release it. If both hold a resource the other needs, neither can complete that path. PostgreSQL detects the cycle and aborts one transaction, allowing the other to continue. The application must distinguish that rejection from an ordinary slow wait.

When it breaks

Row updates acquire locks even without explicit locking statements, so two innocent-looking updates in opposite orders can deadlock. More participants, foreign-key checks, triggers, advisory locks, or lock upgrades can make the cycle less obvious. Sorting just one list of row identifiers is not proof that every resource in a larger operation follows one order.

PostgreSQL reports deadlock detection as 40P01. Applications must not rely on the victim being the youngest transaction or the one that did least work. A statement or lock timeout may fire before deadlock detection, producing a different error. Inspect the actual SQLSTATE and server diagnostics instead of classifying every pause as a deadlock or every retry as a successful repair.

Check it quickly

Use two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, on the same disposable PostgreSQL 18 database. A creates two rows and shows the detector interval:

CREATE SCHEMA deadlock_demo;
CREATE TABLE deadlock_demo.items (id integer PRIMARY KEY,n integer);
INSERT INTO deadlock_demo.items VALUES (1,0),(2,0);
SHOW deadlock_timeout;

The tested interval was one second. The statements below have a ten-second budget, leaving room for detection; ensure no shorter lock timeout interferes. If your detector interval is longer, adjust the demonstration’s statement budget accordingly. A records its increment and updates row one, without committing:

\set delta 10
SET statement_timeout='10s';
BEGIN;
UPDATE deadlock_demo.items SET n=n+:delta WHERE id=1;

B records a different increment and updates row two:

\set delta 100
SET statement_timeout='10s';
BEGIN;
UPDATE deadlock_demo.items SET n=n+:delta WHERE id=2;

In A, submit the next block. It waits for B’s row. Do not wait for this block to finish before switching terminals:

\set ON_ERROR_STOP off
UPDATE deadlock_demo.items SET n=n+:delta WHERE id=2;
\echo second_result :SQLSTATE
\set ON_ERROR_STOP on

While A is waiting, submit B’s second update:

\set ON_ERROR_STOP off
UPDATE deadlock_demo.items SET n=n+:delta WHERE id=1;
\echo second_result :SQLSTATE
\set ON_ERROR_STOP on

The cycle now exists. One terminal prints second_result 40P01, while the other eventually prints second_result 00000. Either terminal can lose. Temporarily allowing expected errors makes both outcomes visible; it does not change which transaction the server rejects. In the losing terminal, clear the failed transaction:

ROLLBACK;

In the successful terminal, commit its complete operation:

COMMIT;

In either terminal, inspect both rows:

SELECT id,n FROM deadlock_demo.items ORDER BY id;

If A won, both values are ten; if B won, both are one hundred. Mixed values would contradict the expected atomic result. The losing transaction’s first update was rolled back along with its second attempt, so counting its first statement as completed work would overstate what was committed.

In the losing terminal, retry the whole operation. Its delta variable still identifies the original increment. This time, explicitly lock both rows in identifier order before updating:

BEGIN;
SELECT id FROM deadlock_demo.items ORDER BY id FOR UPDATE;
UPDATE deadlock_demo.items SET n=n+:delta;
COMMIT;

The locking read returns identifiers one and two. With the winner already committed, the retry completes. In either terminal, verify both increments are present exactly once and remove the fixture:

SELECT id,n FROM deadlock_demo.items ORDER BY id;
DO $$ BEGIN
  IF (SELECT array_agg(n ORDER BY id) FROM deadlock_demo.items) IS DISTINCT FROM ARRAY[110,110] THEN
    RAISE EXCEPTION 'deadlock recovery lost or duplicated work';
  END IF;
END $$;
DROP SCHEMA deadlock_demo CASCADE;

Both values must be one hundred ten. The assertion detects lost or duplicated work, not merely absence of an error message. Keep the two original sessions throughout; opening a third terminal for the retry would lose the client variable identifying which operation needs recovery.

Prevent recurrence across the complete path

Consistent ordering removes the particular two-row cycle shown here when all participants follow it and identifiers remain stable. Apply the ordering to every relevant resource and request the needed lock mode deliberately. Audit trigger and constraint paths too; a local ordering convention cannot exclude cycles introduced elsewhere.

Keep transactions short and avoid holding database locks while waiting for a person or remote service. Retry server-declared deadlocks only after rollback, repeating the reads and decisions as well as writes. Use a bounded budget and jitter under contention. Recurring deadlocks deserve investigation of acquisition order and workload, because repeated aborts still consume time and do not guarantee eventual completion.

Sources