PostgreSQL Deadlocks, Timeouts, and Cancellation Cheat Sheet

BEGIN;
SELECT id FROM app.accounts
WHERE id IN (41, 72)
ORDER BY id
FOR UPDATE;
UPDATE app.accounts SET balance = balance - 100 WHERE id = 41;
UPDATE app.accounts SET balance = balance + 100 WHERE id = 72;
COMMIT;

Note: All code paths touching these accounts must use the same ordering for the convention to work.

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE app.orders ADD COLUMN fulfilled_at timestamptz;
COMMIT;

Note: Failure cancels the statement and leaves the transaction aborted; rollback and reschedule rather than looping aggressively.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '30s';
SELECT tenant_id, sum(total) FROM app.orders GROUP BY tenant_id;
COMMIT;

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_user SET transaction_timeout = '5min';

Note: Test pool behavior first; settings affect new sessions and termination discards session state.

SELECT a.pid, a.usename, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocking_pids,
       clock_timestamp() - a.query_start AS query_age, a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

SELECT pid, usename, datname, state, query_start, query
FROM pg_stat_activity
WHERE pid = 12345;
-- After human verification: SELECT pg_cancel_backend(12345);

Note: Do not automate termination from stale PID lists; cancel first unless an incident runbook requires stronger action.

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('log_lock_waits', 'deadlock_timeout', 'log_min_error_statement')
ORDER BY name;

Note: Change logging through approved configuration management; query text can contain sensitive values.