Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-deadlock~35 min

Deadlocks went from four a day to nine hundred, and nothing in the code had changed

Reported symptoms

  • ●The overnight settlement batch, which normally finishes by 03:20, is still running at 07:40 on Monday and has processed sixty per cent of its work
  • ●The application error tracker records 912 deadlock detected errors overnight against a baseline of four a day for the preceding eleven months
  • ●Every deadlock is retried automatically by the batch framework and every retry eventually succeeds, so no work has been lost
  • ●Database CPU is at 78 per cent overnight against a normal peak of 40, and the WAL generation rate has roughly tripled
  • ●No application deployment has occurred in nine days and the settlement code has not been modified in four months
  • ●The only change is a capacity increase applied on Friday, doubling the batch worker pool from eight to sixteen
  • ●The team proposes lowering deadlock_timeout so that deadlocks are detected and retried faster

Evidence

  • · The server log contains 912 deadlock reports, each with a DETAIL naming two processes waiting on each other and quoting both statements
  • · The two statements in every report are UPDATE accounts SET balance = balance - $1 WHERE id = $2 and the identical statement with different parameters
  • · The account ids in the two halves of each cycle are always in opposite order, so one transaction touched the lower id first and the other touched the higher id first
  • · The settlement code selects the accounts to update with SELECT id FROM accounts WHERE batch_id = $1 and iterates the result set in the order returned, with no ORDER BY
  • · EXPLAIN on that SELECT shows a Bitmap Heap Scan whose row order depends on physical placement, and the plan changed from an Index Scan four months ago when the table grew past the point where the planner preferred a bitmap scan
  • · The deadlock rate scales with the square of the worker count in the historical data: eight workers produced roughly four a day and sixteen produce roughly nine hundred
  • · Each retry re-reads the same unordered result set, so a retried transaction is as likely to deadlock as the original
  • · deadlock_timeout is at its default of one second, and the survivor of each cycle waited approximately that long before the victim was aborted
Diagnosis and resolutionclick to reveal

Root cause

The settlement job locks a set of accounts in whatever order the query returns them, and that order is not defined. Two workers processing overlapping account sets can therefore acquire the same two locks in opposite orders, which is a cycle, which is a deadlock. The bug has been present since the code was written. It was invisible because it needs two workers to overlap on two specific accounts within the same brief window, and with eight workers that happened about four times a day. Doubling the worker count did not double the collision probability; collisions scale with the number of **pairs** of workers, so doubling the workers roughly quadrupled the pairs, and the contention within each pair rose as well. Four a day became nine hundred a night. The ordering is undefined for a specific and instructive reason. `SELECT id FROM accounts WHERE batch_id = $1` has no `ORDER BY`, so the order is whatever the plan produces. Four months ago the table grew past the point where the planner switched from an index scan — which happened to return ids in ascending order — to a bitmap heap scan, which returns rows in physical order. Physical order varies per worker because the rows each one selects are scattered differently. So the code has always been wrong, and for seven months it was accidentally right because the plan happened to sort for it. The plan change four months ago removed that accident, and the capacity increase on Friday made the consequence visible. The retry logic is what turns this from an error into an outage. Every retry re-reads the same unordered result set and is as likely to deadlock as the original attempt, so a heavily contended batch spends its time deadlocking, rolling back and retrying — throwing away all the work each transaction had done, which is where the tripled WAL and the elevated CPU come from. Lowering `deadlock_timeout`, as proposed, would make detection faster and the problem worse: more retries per second, more work discarded per second, and a graph search added to every ordinary lock wait on the server.

Remediation

The immediate relief is to reduce the worker count back to eight. That does not fix anything, and it restores service while the real change is prepared and reviewed. Say so explicitly when doing it, so that "we reverted the capacity increase" is not recorded as the resolution. The fix is one clause. Lock the rows in a defined order: ```sql SELECT id FROM accounts WHERE batch_id = $1 ORDER BY id FOR UPDATE; ``` `ORDER BY id` makes a cycle impossible: a cycle requires one transaction to hold a higher key while waiting for a lower one, and if every transaction acquires in ascending order that cannot occur. `FOR UPDATE` takes the locks up front, in that order, rather than acquiring them incidentally as each `UPDATE` runs. The `ORDER BY` is the load-bearing part. Without it the order comes from the plan, and the plan changes with the data — which is exactly how this arrived. Keep the retry logic. Deadlocks can still arise from foreign key checks, unique index maintenance and triggers that the application does not control, so retries remain a necessary safety net. What changes is that they become rare again. Once deployed, restore the worker count to sixteen and watch the deadlock rate. If it does not return to the low single digits, there is a second ordering path in the code that the first fix did not cover — most likely a different statement in the same transaction touching a different table.

Verification

The deadlock rate returns to its historical baseline at the increased worker count. Measure it from the server log rather than the application error tracker, because the server log is where both halves of each cycle are recorded: ```bash grep -c "deadlock detected" /var/log/postgresql/postgresql-18-main.log ``` The settlement batch completes within its normal window at sixteen workers. WAL generation returns to its previous rate, which is the measurable consequence of transactions no longer being discarded and repeated. `EXPLAIN` of the amended query shows the `ORDER BY` being satisfied — either by an index scan or by an explicit sort — confirming the ordering is enforced by the plan rather than assumed. A deliberate test at high concurrency in a staging environment produces no deadlocks, where the same test against the previous code reproduces them within seconds. This is the check that distinguishes "the fix worked" from "the load happened to be lower tonight".

Prevention

**Order every multi-row lock acquisition.** `SELECT ... ORDER BY <primary key> FOR UPDATE` before doing the work, or sort the batch in the application before writing it. A total order on the rows makes cycles impossible, and it costs nothing at runtime. This is a code review item, not a database setting. There is no configuration that prevents an ordering bug. **Never rely on result order without `ORDER BY`.** The order is a property of the plan, the plan is a property of the statistics, and the statistics change as the data grows. Code that depends on an unordered result is correct until the table reaches a size that changes the plan, and then it is not — with no deployment and no warning. **Alert on the deadlock rate, not on individual deadlocks.** A few a day is normal and unactionable. A rate that rises with traffic is an ordering bug and it will become an outage at some traffic level you have not reached yet. The rate is the signal; the individual events are noise. **Log the deadlock detail and keep it.** The server log names **both** statements in the cycle; the application only ever sees its own. Diagnosing an ordering bug from the application side alone means guessing at the other half. Note that the logged statements include their parameter values, so on tables with sensitive columns the deadlock detail is data that lands in the log. **Treat a capacity increase as a change that needs the same review as a deployment.** Doubling concurrency is a change to the system's behaviour, and it found an eleven-month-old bug in one night.

Reported symptoms

The overnight settlement batch normally finishes by 03:20. On Monday it is still running at 07:40, sixty per cent complete.

The application error tracker records 912 deadlock detected errors overnight, against a baseline of four a day for eleven months. Every one was retried automatically and every retry eventually succeeded, so no work was lost.

Database CPU is 78% overnight against a normal peak of 40. WAL generation has roughly tripled.

No deployment in nine days. The settlement code has not changed in four months. The only change is a capacity increase applied on Friday, doubling the batch worker pool from eight to sixteen.

The team proposes lowering deadlock_timeout so deadlocks are detected and retried faster.

Evidence provided

Read-only / Safea representative cycle, with both statements
$ grep -A5 'deadlock detected' /var/log/postgresql/postgresql-18-main.log | head -7
2026-08-24 02:14:08.117 UTC [30112] batch@settle ERROR:  deadlock detected
2026-08-24 02:14:08.117 UTC [30112] batch@settle DETAIL:  Process 30112 waits for ShareLock on transaction 884213; blocked by process 30087.
Process 30087 waits for ShareLock on transaction 884219; blocked by process 30112.
Process 30112: UPDATE accounts SET balance = balance - $1 WHERE id = $2
Process 30087: UPDATE accounts SET balance = balance - $1 WHERE id = $2
2026-08-24 02:14:08.117 UTC [30112] batch@settle HINT:  See server log for query details.
2026-08-24 02:14:08.117 UTC [30112] batch@settle CONTEXT:  while updating tuple (14892,3) in relation "accounts"

Illustrative output

The account ids in the two halves of each cycle are always in opposite order.

The settlement code selects its accounts with:

SELECT id FROM accounts WHERE batch_id = $1

No ORDER BY. It iterates the result in the order returned.

EXPLAIN shows a Bitmap Heap Scan. Four months ago, when the table was smaller, the same query used an Index Scan.

Historical deadlock counts: eight workers, ~4 a day. Sixteen workers, ~900 a night.

Work the evidence before reading on

  1. Both statements in every cycle are the same statement with different parameters. What does that tell you about where the bug is?
  2. The code has not changed in four months and the deadlocks appeared on Friday. What changed?
  3. Four a day became nine hundred a night when the workers doubled. Is that proportionate?
  4. Every deadlock was retried and every retry succeeded. Why is the batch still four hours late?

Root cause

The lock order is undefined, and always has been

The job locks accounts in whatever order the query returns them. Without ORDER BY, that order is not defined — it is whatever the plan produces.

Two workers whose account sets overlap can therefore acquire the same two locks in opposite orders. That is a cycle, and a cycle is a deadlock.

Doubling the workers more than quadrupled the collisions

Collisions require a pair of workers to overlap. Eight workers give 28 pairs; sixteen give 120. Contention within each pair rises too, since each worker’s set is drawn from the same account population.

Four a day to nine hundred a night is entirely consistent with that. The capacity increase did not cause the bug; it moved the system to a point on the curve where an eleven-month-old bug became the dominant cost.

The retries are why the batch is late

Every deadlock aborts a transaction entirely. Everything it had done is discarded, and the retry starts from the beginning — re-reading the same unordered result set, and therefore as likely to deadlock as the original.

That is where the tripled WAL and the elevated CPU come from: the server is doing the work, throwing it away, and doing it again.

Resolution

Reduce the worker pool to eight for immediate relief. This fixes nothing and restores service. Record it as mitigation, not resolution, so that “we reverted the capacity increase” does not become the accepted answer.

The fix is one clause:

SELECT id FROM accounts WHERE batch_id = $1 ORDER BY id FOR UPDATE;

ORDER BY id makes a cycle impossible. A cycle requires one transaction to hold a higher key while waiting for a lower one; if every transaction acquires in ascending order, that cannot happen.

FOR UPDATE takes the locks up front in that order, rather than acquiring them incidentally as each UPDATE executes.

Restore the worker count to sixteen after deploying, and watch the rate. If it does not return to single digits, there is a second ordering path — most likely a different statement in the same transaction touching a different table.

Verification

Deadlock rate at sixteen workers returns to baseline. Measure from the server log, which records both halves of each cycle:

grep -c "deadlock detected" /var/log/postgresql/postgresql-18-main.log

The batch completes within its normal window at sixteen workers.

WAL generation returns to its previous rate — the measurable consequence of transactions no longer being discarded and repeated.

EXPLAIN shows the ORDER BY satisfied by the plan, so the ordering is enforced rather than assumed.

A deliberate high-concurrency test in staging produces no deadlocks, where the same test against the previous code reproduces them within seconds. This is what distinguishes “the fix worked” from “traffic was lower tonight”.

Prevention

Order every multi-row lock acquisition. ORDER BY <primary key> FOR UPDATE, or sort the batch before writing it. It costs nothing at runtime and it is a code review item, because no database setting prevents an ordering bug.

Never depend on result order without ORDER BY. The order comes from the plan, the plan comes from the statistics, and the statistics change as the data grows. This is the mechanism that made a seven-month-old correct-by-accident program incorrect with no deployment.

Alert on the deadlock rate, not on individual deadlocks. A few a day is normal. A rate that rises with traffic is an ordering bug that will become an outage at a traffic level you have not reached yet.

Log and retain the deadlock detail. The server log names both statements; the application only ever sees its own. Note that the logged statements carry their parameter values, which matters on tables with sensitive columns.

Review capacity increases like deployments. Doubling concurrency changed the system’s behaviour and found an eleven-month-old bug in one night.