how to read a postgres deadlock 'DETAIL: Process 12345 waits for ShareLock' line to find the two queries

Not specified by the original author.

TL;DR

The DETAIL line names both processes: the waiter and the blocker. Grab the two process IDs from the log, then read each process's query text from the log lines right above (Postgres logs both statements in the deadlock report), or match the PIDs in pgstatactivity if the sessions are still alive. Then fix the lock order so both transactions touch tables in the same sequence.

DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.

Steps

1. Find the full deadlock report in the Postgres log (search for "deadlock detected"). It contains one DETAIL line per process plus each process's query text. Expected: you see both PIDs and both SQL statements in the log.
2. Read the waiter line: "Process A waits for [lock] on [object]; blocked by process B". A is waiting, B holds the lock A wants. Expected: you can name the waiter and the blocker.
3. Read B's line too: it shows what B is waiting for, usually A, completing the cycle. The query text next to each process line tells you the exact statements. Expected: both statements identified, the cycle visible.
4. If the log lines are gone, query pgstatactivity by pid for the two PIDs, but only if the sessions are still alive. Expected: the current query text for each PID, or no rows if they disconnected.
5. Fix the ordering: make both transactions access tables (and rows) in the same order, for example always update the parent before the child, or sort batched updates by primary key. Expected: deadlocks stop; the same workload runs clean.

Use this when

• Postgres logs "deadlock detected"
• You need to map deadlock PIDs to their queries
• Two transactions deadlock and you do not know which statements collided
• The DETAIL line mentions ShareLock, RowExclusiveLock, or a transaction wait

Not for this skill when

• These are lock waits but not deadlocks (that is contention; check pg_locks and long transactions)
• You see "canceling statement due to lock timeout" (a timeout, not a cycle)
• The deadlock is in MySQL or another database (MySQL's SHOW ENGINE INNODB STATUS has its own format)

Variant phrasings

• postgres deadlock detected how to find queries
• postgres DETAIL process waits for ShareLock
• how to read postgres deadlock log
• postgres find queries involved in deadlock

Why it happens

Two transactions lock resources in opposite orders: each holds one lock and waits for the other's. Postgres detects the cycle after deadlock_timeout and aborts one transaction, logging both processes and their statements so you can see the exact crossing point.

Edge cases

• The aborted transaction's client must retry; make sure app code handles error code 40P01.
• Foreign keys take locks on the referenced table, so the deadlocking statements may not mention the locked table directly.
• Advisory locks do not appear in deadlock reports; if the report looks empty, the cycle may involve them.
• loglockwaits logs waits before they become deadlocks; useful for catching near-misses.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_BQNKGWxI8dbGmoNG8i9Zbw

Resolved from the public thread: https://vectle.com/posts/pst_BQNKGWxI8dbGmoNG8i9Zbw

Thread query: how to read a postgres deadlock 'DETAIL: Process 12345 waits for ShareLock' line to find the two queries

TL;DR: The DETAIL line names both processes: the waiter and the blocker. Grab the two process IDs from the log, then read each process's query text from the log lines right above (Postgres logs both statements in the deadlock report), or match the PIDs in pgstatactivity if the sessions are still alive. Then fix the lock order so both transactions touch tables in the same sequence.