skip to content

A PostgreSQL email worker fails with SQLSTATE 40P01 after a one-second stall; what did deadlock_timeout do, and how do the error and pg_locks reveal the cycle?

level: seniorimportance: should knowfreq 38%

answer

  1. sleep first, check later
  2. a one-second default
  3. waits for ShareLock on transaction
  4. client DETAIL, log adds the queries
  5. row waits show as transactionid

basics

~20 s

A lock waiter sleeps for deadlock_timeout (default 1s) before searching for a wait cycle; on finding one, PostgreSQL aborts one transaction with 40P01. Its DETAIL lists who waits on which transaction, and pg_locks shows row waits as ungranted transactionid ShareLocks.

solid answer

~50 s

PostgreSQL does not check for deadlock every time a session blocks: the waiter first sleeps for `deadlock_timeout` (default `1s`, milliseconds when no unit is given, changeable only by superusers or roles granted `SET` on it) and only then searches the wait-for graph. If it finds a cycle, one transaction is rolled back with `ERROR: deadlock detected`, SQLSTATE `40P01`; which one is not guaranteed, so the application retries on that code. The DETAIL has lines like `Process 5120 waits for ShareLock on transaction 90412; blocked by process 5133.`, and the server log adds each process's query. A row-lock wait looks like that because row locks live in the tuple, not in shared memory: the waiter requests a `ShareLock` on the holder's transaction ID, which `pg_locks` shows as `locktype = 'transactionid'` with `granted = false`. Without a cycle the waiter just keeps waiting; `deadlock_timeout` is not a lock-wait limit.

code

console · 5 lines
console
ERROR:  deadlock detected
DETAIL:  Process 5120 waits for ShareLock on transaction 90412; blocked by process 5133.
Process 5133 waits for ShareLock on transaction 90411; blocked by process 5120.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (3,14) in relation "recipient_stats"

go deeper

for a junior

Recall that PostgreSQL detects deadlocks itself, rolls one transaction back with SQLSTATE 40P01, and that the application should retry it.

for a middle

Explain that the check runs only after deadlock_timeout, why a row wait shows up as a transactionid ShareLock, and how to read the DETAIL lines.

for a senior

Show how you rebuild the cycle from the server log's per-process queries, fix the lock order, and track pg_stat_database.deadlocks after the change.

for a principal

Discuss tuning deadlock_timeout against detection latency and CPU on a busy cluster, and when to accept retries rather than redesign the access order.

## The scenario Two PostgreSQL transactions touch the same two rows in opposite order: 1. The send worker (pid 5120, transaction 90411) runs `UPDATE email_outbox SET status = 'sent' WHERE id = 101` and row-locks email 101. 2. A bounce webhook (pid 5133, transaction 90412) runs `UPDATE recipient_stats SET bounces = bounces + 1 WHERE recipient_id = 7` and row-locks recipient 7. 3. The worker runs `UPDATE recipient_stats SET sent = sent + 1 WHERE recipient_id = 7` and **waits** for transaction 90412. 4. The webhook runs `UPDATE email_outbox SET status = 'bounced' WHERE id = 101` and **waits** for transaction 90411. Neither can ever proceed. 5. About a second after the first wait began, one of them is rolled back with SQLSTATE `40P01` and the other continues. The theory of deadlocks, why consistent ordering prevents them and how to design retries, belongs to the engine-neutral deadlocks leaf. This answer is about how PostgreSQL detects and reports one. ## What deadlock_timeout does - A backend that has to wait for a lock does **not** check for deadlock straight away. The check is relatively expensive and most waits end on their own, so it sleeps first. - After `deadlock_timeout`, default `1s` and read as milliseconds when no unit is given, the **waiting backend itself** runs the check over the graph of who waits for whom. Only superusers and roles granted `SET` on the parameter can change it. - If the check finds a cycle, PostgreSQL aborts **one** transaction in it with `ERROR: deadlock detected`. The manual says which one is difficult to predict and should not be relied on. - If there is no cycle, the backend keeps waiting, **indefinitely**. `deadlock_timeout` is not a lock-wait limit; bounding waits is `lock_timeout`'s job, which the schema-changes leaf covers. - The same value is also the point at which `log_lock_waits` reports a long wait, a setting the query-stats leaf covers. The manual calls `1s` about the smallest value you would want in practice and suggests raising it on a heavily loaded server, ideally above the typical transaction time. Lowering it reports real deadlocks sooner but runs the check for many waits that would have resolved anyway. ## Reading the error The aborted session receives something like: ```console ERROR: deadlock detected DETAIL: Process 5120 waits for ShareLock on transaction 90412; blocked by process 5133. Process 5133 waits for ShareLock on transaction 90411; blocked by process 5120. HINT: See server log for query details. CONTEXT: while updating tuple (3,14) in relation "recipient_stats" ``` - Each DETAIL line is one edge of the cycle: a process, the lock it wants, and the process in its way. - "ShareLock on transaction N" means the process is **waiting for a row lock** held by transaction N, as explained below; a wait for a table lock would name a relation instead. - The client's copy omits the queries. The **server log** copy adds a `Process 5120: <query>` line for every member, which is what shows the opposite ordering. - `CONTEXT` names the tuple, as (block, offset), and the table the aborted statement was updating. ## Why a row wait looks like a transaction wait Row locks are written into the tuple header, not held in the shared lock table, so there is no in-memory entry for a row to queue on. Every transaction that has been assigned a transaction ID holds an **exclusive lock on that ID** until it ends. A session that needs a row lock held by transaction N therefore requests a **`ShareLock` on N**, which can be granted only once N commits or aborts. During the stall, `pg_locks` shows it: ```sql SELECT pid, locktype, transactionid, mode, granted, waitstart FROM pg_locks WHERE NOT granted; ``` You would see rows with `locktype = 'transactionid'`, `mode = 'ShareLock'`, `granted = false`, and in `waitstart` the time each wait began. The held row locks themselves normally do not appear. Turning these rows into "who blocks whom" is what `pg_blocking_pids()` does, which the monitoring leaf covers. ## What to change - **Fix the order**: make both code paths touch `email_outbox` before `recipient_stats`, or take both row locks up front in a fixed order with `SELECT ... FOR NO KEY UPDATE`, so locks are always acquired the same way round. - **Retry on `40P01`**: it sits in class 40, transaction rollback, beside `40001`; the whole transaction was rolled back and must be re-run from the start. - **Measure**: `pg_stat_database.deadlocks` counts detected deadlocks per database; trend it before and after the fix. - **Do not tune it away**: changing `deadlock_timeout` changes how fast deadlocks are reported, never whether they happen.

  • Should you lower PostgreSQL's deadlock_timeout so deadlocks are detected faster?
    Rarely. The check is relatively expensive, so the server runs it only after a waiter has slept `deadlock_timeout`; the manual calls the `1s` default about the smallest practical value and suggests raising it on busy servers, ideally above typical transaction time. Lowering it adds checks to ordinary waits and prevents nothing; fixing the lock order is what removes deadlocks.
  • Why does the worker wait on a transaction rather than on the recipient_stats row itself?
    Row locks are written into the tuple header, not kept in shared memory, so there is no lock-table entry for the row to queue on. Each transaction with an assigned ID holds an exclusive lock on that ID until it ends, so the waiter requests a `ShareLock` on the holder's ID, granted only when the holder commits or aborts. `pg_locks` shows it as `locktype = 'transactionid'`, `granted = false`.
  • How do you tell whether PostgreSQL deadlocks are rising across the database rather than in one incident?
    `pg_stat_database` has a cumulative `deadlocks` column per database. Sample it on a schedule and graph the rate; a step change after a deploy points at a new access order. The server log keeps each occurrence with every member's query, so match the spikes to the DETAIL and `Process N:` lines to find the code paths involved.

  • deadlock_timeout aborts any statement that waits longer than one second for a lock
  • PostgreSQL always aborts the transaction that started most recently
  • pg_locks lists every held row lock, so the cycle can be read straight from it
  • 40P01 is a serialization failure that only happens under SERIALIZABLE
  • Setting deadlock_timeout to a few milliseconds prevents deadlocks