Logs and counters
Transient failures leave permanent traces — if you've turned the right knobs on. This lesson covers the two places PostgreSQL records trouble: cumulative statistics views and the server log.
The deadlock nobody saw
A deadlock that's caught by a retry wrapper never reaches your error tracker. The database still counts it — pg_stat_database.deadlocks: "Number of deadlocks detected in this database":
A> SET deadlock_timeout = '10s'; -- Pin the victim, as in the chapter-3 deadlock lesson, so the transcript is reproducible.
SET
B> SET deadlock_timeout = '50ms';
SETThe classic: A and B lock the same two rows in opposite order.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
UPDATE 1
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- deadlock_detected
ERROR: 40P01: deadlock detected
⏵ A resumes:
UPDATE 1
A> COMMIT;
COMMIT
B> ROLLBACK;
ROLLBACKThe app retried, the users never noticed. But the database remembers:
B> SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
--------------------------
(1 row)
M> SELECT (d.deadlocks - s.deadlocks)::int AS new_deadlocks
FROM pg_stat_database d, stats_before s
WHERE d.datname = current_database();
new_deadlocks
---------------
1
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
A counter that only ever goes up is an alerting gift: graph its rate, alert on a jump. (The scenario snapshots the counter into a table in its setup so the transcript shows a clean delta; in production you'd graph the raw value directly. The pg_stat_force_next_flush() call is also demo plumbing — statistics normally reach the views within moments on their own.)
What the server log says
Each deadlock also writes a log entry that names both queries — this one is from the run that generated the transcript above (illustrative excerpt, not regenerated by CI; pids and timestamps vary):
2026-07-06 15:38:22.941 UTC [51185] ERROR: deadlock detected
2026-07-06 15:38:22.941 UTC [51185] DETAIL: Process 51185 waits for ShareLock on transaction 6174; blocked by process 51184.
Process 51184 waits for ShareLock on transaction 6175; blocked by process 51185.
Process 51185: UPDATE accounts SET balance = balance + 25 WHERE id = 1
Process 51184: UPDATE accounts SET balance = balance + 10 WHERE id = 2
2026-07-06 15:38:22.941 UTC [51185] STATEMENT: UPDATE accounts SET balance = balance + 25 WHERE id = 1The DETAIL lines are the whole investigation: both pids, both transactions, both statements, and the cycle between them.
Two logging GUCs turn the log into a lock-debugging tool before things deadlock:
log_lock_waits: "Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance". Default off; turn it on — a queue that clears in two seconds never shows up inpg_stat_activitysampling, but it shows up here.log_min_duration_statement: "Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time" — and a statement that spent its life waiting on a lock logs that time too, so slow-query logs catch lock victims, not just bad plans.
pg_stat_database counts deadlocks and rollbacks forever, so alert on the rate rather than on user reports. Turning on log_lock_waits is cheap and names the blocker for every wait longer than deadlock_timeout (one second by default) — the flight recorder for lock queues. Retry wrappers are what make these failures invisible to humans, and the counters and logs are how you keep seeing them anyway.