Find what is blocking what, and who to look at first.
promptread-only tools
triage-lock-contention([connection])The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Triage lock contention on connection shop_prod. 0. Call checkPrivileges. Without stats access, currentActivity hides the query text of backends belonging to other roles, which is most of what this investigation reads. Read before you write. Do not kill anything, and do not recommend killing anything until the last step. This is for a wait that is still happening. If what you have is a deadlock the server already detected and logged, the sessions in it are gone and nothing below will find them -- use diagnose-deadlock, which reconstructs it from the log and the schema instead. 1. Call currentLocks. The rows carry a chain_depth: 0 is the backend asked about, and the largest depth is the backend at the root of the wait chain. That is the one to look at first. Read mode and lock_type on the same rows before going further, because they decide what kind of problem this is. An AccessExclusiveLock at the root is DDL or a VACUUM FULL and everything behind it is stopped dead; a RowExclusiveLock is ordinary DML and the queue is moving, just slowly. granted separates what is held from what is being waited on, and wait_start says how long the queue has been stuck -- which is not the same number as how long the blocker has been running, and is the one that says how much damage is already done. 2. Call currentActivity for the pids in the chain. For each, read state, wait_event_type, query and how long the transaction has been open. Check leader_pid before deciding what any pid is. A parallel worker carries its leader's pid there, and it is the leader that holds the transaction -- terminating a worker achieves nothing and the leader simply starts another. Read backend_xmin on the root as well. A backend holding an xmin horizon is not only blocking this chain: it stops vacuum removing dead tuples anywhere in the cluster, so a long idle transaction here is also the bloat somebody else is investigating. If it is set and old, say so, and hand the second half to bloat-and-vacuum-review. 3. An idle in transaction backend at the root is the usual answer, and the fix is in the application that left it open, not in the database -- so name the application rather than the pid. currentActivity carries application_name, user and client_addr for exactly this: a pid is gone by the time anyone acts on the report, while a connection pool, a deploy or a host is something an operator can go and fix. query_id links the statement to statementStats, which says whether this is a recurring pattern or one bad session. 4. Report the chain root, what it is doing, how long it has held the lock, and what is queued behind it. Only then discuss whether terminating it is safe, and say what would be lost.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "triage-lock-contention",
"arguments": {
"connection": "shop_prod"
}
}
}