diagnose-deadlock

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.

Investigate this deadlock on connection shop_prod. The log: ERROR: deadlock detected DETAIL: Process 51877 waits for ShareLock on transaction 9021144; blocked by process 51702. Process 51702 waits for ShareLock on transaction 9021139; blocked by process 51877. Start from what is no longer true. The deadlock is over: PostgreSQL detected the cycle, killed one transaction and released the other. The pids in that log do not exist any more, so currentActivity and currentLocks describe a server that has already moved on, and triage-lock-contention -- which is about a wait chain you can still see -- does not apply. Everything below reconstructs the deadlock from the log and the schema. Reach for the live tools only in step 6, and only if it is still recurring. 0. Call checkPrivileges. explainQuery and statementStats are the two this needs and the two a restricted role loses; without them say so rather than guessing at plans. 1. Read the log before touching the database, because it already settles more than it is usually given credit for. For each process extract three things: the statement it was running, the lock it waited for, and the lock it held. The kind of lock names the shape before you look at anything: - 'ShareLock on transaction N' means it was waiting for a row another transaction had written and not yet committed. This is the common case and it is a row-ordering problem. - 'ExclusiveLock on tuple (x,y)' is the queue for one specific row: several transactions want the same row and are lined up. - a relation-level lock, especially AccessExclusiveLock, means DDL or an explicit LOCK TABLE was in the cycle, not ordinary DML. 2. The statement text is not the lock footprint, and assuming it is is why deadlocks between statements that name different tables look impossible. For every table named, call tableDetails and read: - foreign keys in BOTH directions. Writing a row that references a parent takes a lock on the parent row too, so a statement that names only the child touches the parent, and two children pointing at two parents in opposite orders deadlock without ever naming the same table. - triggers. A trigger runs statements the log never shows, against tables the statement never names. - unique indexes. Two inserts of the same key do not conflict on a row that exists; the second waits on the first transaction to end. If a statement calls a function or procedure, call functionDetails and read the body: the transaction is the whole body, and the ordering that matters is the order the body takes. 3. Now reconstruct the acquisition ORDER for each transaction, because that is the only thing that ever causes a deadlock. Two transactions took the same locks in different orders; nothing else produces a cycle. Write the order out per transaction, including the locks step 2 found that the statements do not mention, and say where the two orders cross. 4. Call explainQuery for each statement. The plan decides the order rows are locked, so two UPDATEs with identical WHERE clauses lock in different orders when one uses an index scan and the other a sequential scan -- and a plan flip is enough to start a deadlock that was not happening last week. Where the statement came from pg_stat_statements, recover it with explainQuery by queryid first. Do not use analyze: it executes, and this is a post mortem. 5. Classify it, and say which reading decided it: rows taken in different orders, a foreign key pulling in a parent, a trigger widening the footprint, contention on one unique key, or explicit locking in inconsistent order. 6. Only now ask whether it is still happening, and escalate in three stages. Each one is licensed by the one before it, and in each the fact that the statement APPEARS is itself the finding, before any counter is read. a. Look for it in statementStats. pg_stat_statements evicts under pressure, discarding the least-used entries first, so a statement still present in a cluster that is evicting has proved it runs often enough to survive -- and a deadlock needs concurrency, so that is the first thing worth establishing. Read info.dealloc to know whether eviction is happening at all, because it decides what presence means. Then calls and mean_exec_time say how much overlap there is between two runs. Not finding it settles nothing. It may have been evicted, the counters may have been reset, or the extension may not be installed. Say which of those you ruled out rather than reporting it as rare. Match on the normalised text: the log holds one execution, pg_stat_statements holds the shape with constants replaced. b. Look for it in currentActivity. If it is there, it is running right now, which turns a post mortem into something observable -- you can watch the next occurrence instead of inferring the last one. Read state and how long each transaction has been open: a statement that finishes in milliseconds cannot hold a lock long enough to deadlock with anything, so an open transaction sitting on it is the condition that makes the cycle possible. c. If it is live and slow, call currentLocks. A deadlock is a wait chain that closed into a loop, so the chains forming now are the near-miss version of the one that closed -- the same edges, caught before the last one joined up. If you find a real chain, hand off to triage-lock-contention, which is built for a wait you can still see; come back here with what it found about the root. Whatever the stage, serverSettings gives deadlock_timeout, and log_lock_waits being off means every long wait that did not quite close into a cycle went unlogged -- so the log you are reading is the only one of these events you were ever going to see. 7. Rank the fixes, and rank them in this order: make the acquisition order consistent between the two paths, then shrink the lock footprint, then shorten the transaction so the window is smaller. Be explicit that deadlock_timeout is not a fix -- it changes when the cycle is detected, never whether it forms -- and that max_locks_per_transaction has nothing to do with this. Retrying on SQLSTATE 40P01 belongs in the application and is a mitigation, not a diagnosis: say so plainly if you recommend it, because a retry loop over an ordering bug hides it rather than fixing it. 8. If the fix is DDL -- an index to change a plan, a constraint to drop, a trigger to rewrite -- hand it to plan-schema-change rather than proposing the statement here. What is safe to apply depends on the size and traffic of the table, which that prompt measures.

Synopsis

Arguments

What it asks the model to do

Example mocked arguments