Logs and counters
PostgreSQL 18 offers cumulative statistics and configurable server logs for investigation. They have different retention and collection rules. This PostgreSQL 18.6 Scenario asserts one deadlock error and its counter delta; it does not assert server-log contents or successful application retry.
The deadlock nobody saw
The database-statistics contract describes deadlocks as "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;
ROLLBACKNo retry is executed here. After B rolls back, flush its statistics and read the counter delta:
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.6 · Run it yourself · Scenario source
B receives 40P01 in the two-row cycle. After B rolls back and requests a statistics flush, M reads an asserted delta of one from the setup baseline. No retry wrapper or user-facing application runs here. The retry lesson executes a separate bounded retry example.
Documented scope: cumulative statistics can be reset explicitly and are reset after an unclean server shutdown. A clean shutdown preserves them. Track stats_reset and handle decreases or restarts when computing rates; neither resets nor restarts are exercised in this Scenario. Counter publication can lag, and the default cache lasts through the monitoring transaction. The forced flush is demonstration plumbing, not a production collection policy.
What the server log says
Deadlock diagnostics can identify the participating statements and wait cycle. The error statement's inclusion depends on logging configuration such as log_min_error_statement; do not treat an illustrative excerpt as generated execution evidence. This lesson supplies no captured log artifact and makes no claim that CI checked log contents.
PostgreSQL 18's logging contract provides two useful controls:
log_lock_waits, off by default, logs qualifying waits longer thandeadlock_timeout. Enable it with the required SET privilege and evaluate log volume. It reports waits; it does not prevent them or record every short wait.log_min_duration_statementlogs qualifying completed statements. Their elapsed duration includes waiting time, but duration alone does not identify whether a lock or a slow plan caused it. Error logging is a separate setting.
Operational advice: correlate counters, activity views, and retained logs. A sampled activity query can miss a short-lived wait; configured wait logging can provide another observation. Neither a fixed sampling interval, a guaranteed capture rate, nor negligible logging overhead is measured here. Retries can hide some errors from users, but bounded retries can still fail and the counters are not a permanent history.