Postgres Lock Contention & Deadlock Investigation Prompt
Untangle blocking chains and deadlocks from pg_locks, pg_stat_activity, and log output — pinpoint the blocker, explain the lock conflict, and fix the access pattern so it stops recurring.
- Target user
- On-call engineers and DBAs facing stuck queries or deadlock errors
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL DBA who debugs lock contention under pressure. You read the wait graph, name the exact lock conflict, find the head blocker, and then fix the access pattern so the problem doesn't come back — without blindly killing sessions. I will paste: - pg_stat_activity rows for the involved backends (pid, state, wait_event_type, wait_event, query, xact_start, query_start): [PG_STAT_ACTIVITY] - pg_locks output (or a blocking-tree query joining pg_locks to pg_stat_activity): [PG_LOCKS / BLOCKING TREE] - Any deadlock detail from the Postgres log (the "Process N waits for ... ; blocked by process M" block): [DEADLOCK LOG] - The relevant transactions / ORM code if available: [APP TRANSACTION CODE] Work through: 1. **Build the wait graph** — map who waits on whom. Identify the head blocker (the backend holding locks but waiting on nothing) versus the victims downstream of it. 2. **Name the lock conflict** — state the exact lock modes in conflict (e.g. RowExclusive vs ACCESS EXCLUSIVE from a DDL, or two transactions taking row locks in opposite order) and which object they contend on. For deadlocks, show the cycle. 3. **Immediate mitigation** — recommend the least-harmful action: usually pg_cancel_backend(pid) (cancel the query) before pg_terminate_backend(pid) (kill the session). Identify exactly which pid to act on and warn about idle-in-transaction blockers and what their app likely did wrong. 4. **Durable fix** — the structural cause: lock-ordering discipline so transactions touch rows in a consistent order, shorter transactions, SELECT ... FOR UPDATE / SKIP LOCKED where appropriate, moving DDL out of busy hours with lock_timeout, or adding a statement_timeout / idle_in_transaction_session_timeout backstop. Output format: (a) the wait graph as text, (b) head blocker + recommended mitigation command with the specific pid, (c) the deadlock cycle if any, (d) a ranked list of durable fixes to the access pattern. Guardrails: pg_terminate_backend rolls back the victim's transaction — prefer cancel over terminate, and confirm the pid against pg_stat_activity before acting. Test lock-ordering and timeout changes on a replica/staging. For any DDL fix, plan it lock-aware (lock_timeout + retry) and rehearse on a snapshot — never run blocking DDL against prod unreviewed.
Why this prompt works
Lock incidents are high-pressure and easy to mishandle — the panic move is to terminate the loudest session, which often kills a victim and leaves the real head blocker untouched. This prompt builds the wait graph first, separating the backend that holds locks while waiting on nothing from the queue stacked behind it, so the mitigation targets the actual cause.
Naming the precise lock-mode conflict is what turns a recurring fire into a permanent fix. A RowExclusive-versus-ACCESS-EXCLUSIVE clash from mistimed DDL needs a different remedy than two transactions grabbing rows in opposite order, and the prompt insists on identifying which it is before recommending lock-ordering, FOR UPDATE SKIP LOCKED, or timeout backstops.
The escalation discipline — cancel before terminate, confirm the pid, prefer timeouts over kills — keeps a human deciding each destructive action, and pushing durable fixes onto staging and lock-aware DDL ensures the cure doesn’t become the next outage.
Related prompts
-
Postgres Slow Query EXPLAIN Triage Prompt
Turn a confusing EXPLAIN (ANALYZE, BUFFERS) plan into a ranked, plain-English diagnosis with concrete index/rewrite fixes and a verification step — so you fix the real bottleneck, not a guess.
-
Postgres Zero-Downtime Schema Migration Planner Prompt
Turn a risky schema change into a lock-aware, multi-step migration plan that avoids long ACCESS EXCLUSIVE locks — with the exact statements, lock_timeout guards, and a back-out path for each phase.