🔒 Diagnosing Postgres Lock Contention and Deadlocks With AI

The symptom was awful and specific: every few minutes the app would hang for thirty seconds, then recover, with no errors and no slow queries in the usual sense. The queries weren’t slow — they were waiting. A long-running analytics transaction was holding a lock that a flood of short writes needed, and they all queued behind it. Lock contention is one of the hardest Postgres problems to diagnose because the blocked queries look fine in isolation; the damage is in the relationships between sessions. AI is excellent at reading the blocking graph and explaining who holds what, but it can’t see your live pg_locks. You feed it the snapshot; it untangles the chain.

This is the playbook I reach for when Postgres is “slow” but nothing is actually slow.

See who is blocking whom

The single most useful query in your kit is the blocking-tree query. It joins pg_locks against itself through pg_blocking_pids() to show which sessions are waiting on which.

SELECT
  blocked.pid          AS blocked_pid,
  blocked.query        AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking.query       AS blocking_query,
  blocked.wait_event_type,
  now() - blocking.query_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = bp.pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocking_duration DESC;

This gives you the blocker, the blocked, and how long the blocker has been running. The blocker at the top of the longest chain is usually the root cause. I paste this output to AI and ask it to reconstruct the dependency tree and name the single session at the root.

Here is output from my blocking-pid query. Build the wait-for graph: who is the root blocker, what is it doing, and which queries are queued behind it? Tell me whether this looks like a long transaction holding a lock too long, a missing index forcing row-level lock escalation, or genuinely conflicting access patterns. Don’t suggest killing anything yet.

The fast mitigation vs. the real fix

Once you’ve identified the root blocker, you have a fast lever and a slow one. The fast lever is canceling the offending session — but only after you understand what it’s doing, because canceling the wrong one just moves the problem.

-- cancel the query (gentle); pg_terminate_backend is the hard kill
SELECT pg_cancel_backend(12345);

That clears the jam. It does not fix the cause. The real fix is in the access pattern: a transaction that holds locks across a slow external call, an UPDATE that locks more rows than it should, or a missing index that forces a full-table scan under a lock. AI is good at proposing the structural fix once it sees the queries involved — usually “shorten the transaction” or “add the index so the lock footprint shrinks.”

Hunt the long-lived transactions

Most contention traces back to a transaction that stayed open too long — often an app that started a transaction, did some work, then waited on something external before committing. Find them:

SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS idle_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start < now() - interval '30 seconds'
ORDER BY xact_age DESC;

idle in transaction is the villain here: a session holding locks while doing nothing. The durable fix is a server-side guardrail so a misbehaving client can’t hold locks forever:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '15s';
SELECT pg_reload_conf();

I ask AI to sanity-check that timeout against the app’s longest legitimate transaction so we don’t start aborting valid work.

Decode deadlock logs

A true deadlock — two transactions each holding what the other needs — gets resolved automatically by Postgres aborting one. The log entry is dense but complete:

grep -A 20 "deadlock detected" /var/log/postgresql/postgresql-16-main.log

The log shows both processes, the locks they held, and the locks they wanted. This is a perfect AI task: paste the full deadlock report and ask it to reconstruct the lock-acquisition order for each transaction and identify the cycle.

Here’s a deadlock detected log block. Tell me the exact lock-acquisition order for each of the two transactions, identify the cycle, and tell me what consistent ordering of operations would prevent it. The classic fix is to make all transactions touch rows in the same order — confirm whether that applies here.

The fix for almost every deadlock is consistent lock ordering: if every transaction updates rows in the same canonical order (say, by primary key ascending), the cycle can’t form. AI will spot when two code paths touch the same tables in opposite orders.

Verify on a replica, then ship the pattern change

The mitigations — canceling sessions, setting timeouts — you can do live once you understand the graph. The structural fixes — reordering operations, shortening transactions, adding an index to shrink the lock footprint — get tested under concurrency on a replica or a load test before they ship, because lock behavior only shows up under contention. AI turns an opaque blocking graph into a named root cause in minutes, which is most of the battle when prod is hanging. It doesn’t get to terminate backends on its own. The rest of the operational Postgres material is in the Postgres guides, my lock-debugging prompts live in the prompt library, and if the instance is reachable from anywhere it shouldn’t be, fix that first with hardening Redis and Postgres against exposure.