SaaS architecture
deadlock detected in PostgreSQL: causes, measured
PostgreSQL's deadlock detected, measured on 17: which session loses, foreign key and batch-update deadlocks, ORDER BY … FOR UPDATE, and the retry.
ERROR: deadlock detected (SQLSTATE 40P01) means two or more transactions were each waiting for a lock the other held, so none could ever continue, and PostgreSQL aborted one of them to let the others finish. The DETAIL line names the processes and what each waited for; the queries are only in the server log, which the HINT says. The fix is nearly always: take locks in a consistent order — and take the strongest lock you will need first — and retry the whole aborted transaction, not just the statement that failed.
We measured eight ways to get the error on PostgreSQL 17.11. In each, a monitoring session confirmed in pg_locks that both transactions held their first lock before the second one closed the circle.
What does the deadlock error tell you?
The classic case: session A updates row 1, session B updates row 2, then A updates row 2 and B updates row 1.
ERROR: 40P01: deadlock detected
DETAIL: Process 823 waits for ShareLock on transaction 753; blocked by process 830.
Process 830 waits for ShareLock on transaction 752; blocked by process 823.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "t"
A row lock shows up as a wait on the other transaction (ShareLock on transaction 753), because that transaction is what has to end. The session that got the error is aborted: its next statement got current transaction is aborted (25P02), and its earlier work was gone. The other session’s update went through and it committed normally.
Which session loses is not “the second one”. Each waiting session checks for a deadlock once, after it has waited deadlock_timeout (1 s by default); a check that finds no circle is not repeated, and the session whose check finds the closed circle is aborted. When B closed the circle 0.15 s after A started waiting, A — the first to wait — lost, 1.001 s into its wait. When B waited two seconds before closing it, B lost, 1.001 s after its own wait began. The documentation is explicit that “exactly which transaction will be aborted is difficult to predict and should not be relied upon”. Code that assumes the newer request fails will sometimes be wrong: in our seven runs the first session to wait lost five times.
deadlock_timeout moves the detection: at 200ms the error came after 0.201 s, at 5s after 5.001 s — during which both sessions stood still, although, in the run where B closed the circle 0.16 s in, the deadlock already existed for 4.84 of those seconds. A higher value does not prevent deadlocks; it only makes them take longer to resolve.
Which deadlocks are not obvious from the code?
| Pattern | Deadlock? | Why |
|---|---|---|
| Two transactions update the same rows in opposite order | yes, 1.001 s | the classic case |
update tenants set name = … (or invoice_count = invoice_count + 1) while the other inserts an invoice for that tenant | no, not even a wait | an update of a non-key column takes FOR NO KEY UPDATE, which does not conflict with the foreign key’s FOR KEY SHARE |
the same, but the update changes a UNIQUE column or the primary key of tenants | yes | that update takes the stronger lock; CONTEXT showed the foreign-key check: SELECT 1 FROM ONLY "public"."tenants" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x |
both insert an invoice for tenant 1, then select … from tenants where id = 1 for update | yes | each holds FOR KEY SHARE from its insert and wants FOR UPDATE |
the same with update tenants set invoice_count = invoice_count + 1 where id = 1 | no, B waited for A | FOR NO KEY UPDATE again |
plain inserts of the same unique keys in opposite order (a bulk import with overlapping keys) | yes, 1.000 s | CONTEXT: while inserting index tuple (0,3) in relation "kv_pkey": the second insert waits to see whether the first commits |
insert … on conflict do update on two keys in opposite order | yes, also when the keys did not exist yet | CONTEXT: while inserting index tuple |
pg_advisory_xact_lock(1) / (2) in opposite order | yes | DETAIL names advisory lock [5,0,2,1]; pg_try_advisory_xact_lock never deadlocked, both just got f |
The foreign-key rows matter most in a multi-tenant schema, where nearly every table references tenants. A counter or a status on the tenant row does not conflict with the foreign key — but it serialises that tenant’s writers (B waited 15 s for A), and a transaction that bumps two tenants’ counters must do so in a fixed order, or it is the classic deadlock again, on tenants itself. Updating a unique column on the tenant row (a slug, an external id), or locking it FOR UPDATE after inserting child rows, is what turns ordinary traffic into deadlocks. The last one is a lock upgrade: both sessions took the same locks in the same order and still deadlocked, because each first held the weak FOR KEY SHARE and then wanted the strong one. Taking FOR UPDATE (or FOR NO KEY UPDATE) on the tenant before inserting avoids it. A set code = code that wrote the same value did not count as a key change: the lock stayed No Key Update.
Can one UPDATE statement deadlock with another?
Yes, when both lock many rows and the plans walk them in different orders. Two sessions updated the same rows (where id between 1 and 1000), one forced to a sequential scan and one to an index scan (enable_seqscan / enable_indexscan per session), on a table filled in random order, with a pg_sleep(0.001) per row in the first test so the overlap was reproducible:
| How the rows were locked | 1,000 rows, pg_sleep(0.001) per row | 200,000 rows, no delay |
|---|---|---|
update … where id between …, one seq scan, one index scan | 4 of 5 runs deadlocked | 2 of 5 |
select … for update without order by, same plans | 5 of 5 | 0 of 5 |
select id … order by id for update first, then the update, in both | 0 of 5 | 0 of 5 |
order by id for update in one session, a plain update (seq scan) in the other | 3 of 3 | — |
Five runs are not a rate; what the table shows is that it depends on timing, which is why it appears under load and not in tests, and that ordering only protects you when every code path does it — in the last row the ordered session lost all three times.
In the server log both processes showed the same statement text — the deadlock was between two copies of one query, run with different plans. order by … for update before the change gives every transaction the same lock order, which is the documentation’s advice: “The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.” How a job queue takes its rows with SKIP LOCKED, and the error that still remains there, is in could not serialize access due to concurrent update.
How did the two transactions get their locks?
- The same rows, in a different orderOne lock order everywhereSort the ids, or
select … order by id for updatefirst — in every code path that touches these rows. - The same lock order, but a weak lock first and a stronger one laterTake the strongest lock first
FOR UPDATEon the tenant row before inserting its children, not after. - Through a foreign key, while updating the parent's key or unique columnDo not change key columns in busy transactionsNon-key updates of the parent did not conflict at all.
- Inserts or upserts of the same unique keys in a different orderSort the batch by keyBoth waited on each other's uncommitted index entry.
The four patterns are measured above on PostgreSQL 17.11; the fix in the first branch was measured too (0 of 5 with ordering in both sessions), the other three follow from the lock modes we saw and were not measured separately. In every case the aborted transaction has to be retried from its start, outside any savepoint.
How do you retry after a deadlock?
From the start of the transaction. Repeating only the failed statement got current transaction is aborted, and the COMMIT that followed answered ROLLBACK: all of the loser’s work was gone, without an error at the commit — the trap measured in current transaction is aborted. Repeating the whole transaction worked: the loser succeeded on its second attempt and the end state was correct for both — for what is inside the database. A sequence value the aborted attempt drew stayed used (the retried insert got id 3, not 1), and anything the attempt did outside the database happens again, so send mails and calls after the commit. A retry inside a savepoint does not help: after rollback to savepoint the loser still held its earlier row lock, the other session kept waiting, and the retried statement deadlocked again after 1.0 s.
To find what deadlocked, read the server log. It has the statement each process was waiting in — not the earlier statements that took the locks it held: in the foreign-key case both logged statements were INSERTs into invoices, and the UPDATE tenants that closed the circle was not there. With log_lock_waits = on (without it, nothing appeared before the error), it also logged detected deadlock while waiting for ShareLock on transaction … after 1000.052 ms just before the error; when the circle closed later than deadlock_timeout, it first logged still waiting … after 1000.043 ms, and for the winner, acquired ShareLock … after 2652.280 ms. With lock_timeout = '500ms' in both sessions the deadlock became canceling statement due to lock timeout (55P03) after 0.5 s — a code a deadlock retry may not catch; with it only in the session that closed the circle, it stayed a deadlock. More on that error in canceling statement due to lock timeout.
What we did not measure
Deadlocks between more than two sessions. Deadlocks involving DDL (an ALTER TABLE waiting in a cycle), and CREATE INDEX CONCURRENTLY. How specific ORMs order their updates.
Sources: PostgreSQL 17 documentation on explicit locking (deadlocks), read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11 in containers, on 21 and 22 October 2026.