how to read a postgres deadlock 'DETAIL: Process 12345 waits for ShareLoc

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.

Not specified by the original author.

TL;DR

Two transactions locked the same rows in opposite order, so each waits on the other forever and Postgres aborts one. Fix it by always locking rows in the same order (e.g. ORDER BY primary key in SELECT ... FOR UPDATE), keeping transactions short, and retrying the aborted transaction with backoff.

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ShareLock on transaction 12345; blocked by process 12345.

Use this when

• Transactions abort with "deadlock detected"
• Concurrent writers or updaters keep failing
• The DETAIL lines name the blocked processes and you need the story

Not for this skill when

• Queries time out on locks without a deadlock (raise lock_timeout handling instead)
• The database is just slow
• The race is in application code with no DB transaction involved

Steps

1. Read the full DETAIL, not just the headline. It names the processes and often the tables:

-- in the Postgres log, look for the lines after "deadlock detected":
-- they name the relations and the statements each process was running
SHOW log_directory;

Expected output: the log location. The DETAIL block lists each blocked process, the lock it wanted, and the query it was running, which usually reveals two updates touching the same tables in opposite order.

2. Find the current lock waiters to see the pattern live:

SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
 AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Expected output: who blocks whom and on what query. A cycle in this graph is your deadlock.

3. Impose a consistent lock order. The classic fix is ordering row locks by primary key:

BEGIN;
SELECT * FROM accounts WHERE id IN (7, 3, 9) ORDER BY id FOR UPDATE;
-- now do the updates; every transaction takes these rows in id order
UPDATE accounts SET balance = balance - 100 WHERE id = 3;
UPDATE accounts SET balance = balance + 100 WHERE id = 7;
COMMIT;

Expected output: no deadlock, because every concurrent transaction locks rows 3, 7, 9 in the same sequence and one simply waits for the other.

4. Shrink the transaction: move non-database work outside it and commit promptly:

# bad: HTTP call inside the transaction holds locks for seconds
# good:
result = call_external_api()  # outside
with db.transaction():
    db.execute("UPDATE accounts SET ...")  # fast, then commit

Expected output: shorter lock hold times, which shrinks the window where two transactions can interleave badly.

5. Retry the victim with backoff in application code. Deadlocks are expected concurrency events, not bugs:

import time, random
for attempt in range(4):
    try:
        run_transfer()
        break
    except DeadlockError:
        time.sleep(0.1 * (2 ** attempt) + random.random() * 0.1)
else:
    raise

Expected output: transient deadlocks resolve on retry. If the same transaction deadlocks repeatedly, the lock order is still inconsistent, go back to step 3.

Variant phrasings

postgres deadlock detected how to debug

Steps 1-2 give you the who and what. The log DETAIL is the single most useful artifact.

deadlock detected process waits for ShareLock

The standard DETAIL format. Two processes each hold a lock the other wants; Postgres picks a victim and aborts it so the other can proceed.

how to avoid deadlocks in postgres

Consistent lock ordering (step 3), short transactions (step 4), and retry logic (step 5). In that order of importance.

Why it happens

Row locks are held until transaction end, and two transactions that lock the same set of rows in different orders can each end up holding a lock the other needs. Neither can proceed, so Postgres detects the wait cycle and aborts one participant to break it. It is a scheduling problem, not data corruption: the aborted transaction simply needs to run again.

Edge cases

• Foreign keys take locks on the referenced row too; inserting child rows concurrently can deadlock on the parent. Order parent touches consistently as well.
• Unique-index inserts can deadlock via speculative insertion; retry logic covers these.
• SELECT ... FOR UPDATE without ORDER BY locks in scan order, which varies; always add ORDER BY on a unique key.
• Serializable isolation raises serialization failures instead of deadlocks sometimes; the retry pattern from step 5 covers both.

Provenance

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

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

Thread query: "deadlock detected" Postgres: what causes it

TL;DR: Two transactions locked the same rows in opposite order, so each waits on the other forever and Postgres aborts one. Fix it by always locking rows in the same order (e.g. ORDER BY primary key in SELECT ... FOR UPDATE), keeping transactions short, and retrying the aborted transaction with backoff.

Not specified by the original author.

TL;DR

Serialize the contending migrations and shrink the lock footprint. Make sure only one migration process runs at a time, break big transactions into smaller ones, and apply DDL to tables in a consistent order everywhere. Postgres detected two transactions each waiting on the other's lock and killed one; the fix is to stop them from overlapping.

psycopg2.errors.DeadlockDetected: deadlock detected

Steps

1. Confirm it is a real deadlock and not a lock timeout. Check the Postgres logs for "deadlock detected" and read the DETAIL line. Expected: the DETAIL names two processes waiting on each other's locks.

2. Identify the two statements. Query the activity view while it happens, or enable lock-wait logging and read which migration statements were involved. Expected: you can name the two DDL statements and the tables they locked.

3. Stop the overlap. Ensure only one migration process runs at a time: a deploy mutex, an advisory lock, or simply waiting for the other deploy to finish. Expected: the activity view shows a single migration process.

4. Retry the killed migration on its own. Expected: it completes without a deadlock.

5. Prevent the recurrence. Order DDL the same way in every migration (same table order), keep migration transactions short, and avoid concurrent DDL on the same tables. Expected: later concurrent deploys serialize instead of deadlocking.

Use this when

• "Deadlock detected" appears while two migrations overlap.
• One migration is killed while another holds locks.
• Concurrent deploys or parallel migration workers run DDL at the same time.
• The DETAIL line names two migration statements.

Not for this skill when

• It is a lock timeout with no deadlock (that is a locktimeout or statementtimeout issue).
• The deadlock is inside application code rather than migration DDL.
• A single migration deadlocks with itself over foreign-key ordering (that is a statement-ordering problem).

Variant phrasings

• deadlock detected during migration
• concurrent migrations deadlock postgres
• psycopg2 DeadlockDetected alembic
• two deploys migrating at once deadlock

Why it happens

Two migration transactions lock tables in different orders (A then B versus B then A). Each holds one lock and waits for the other, so Postgres's deadlock detector picks a victim and aborts it. Overlapping deploys or parallel migration workers make this likely, and the victim's migration is left half-applied.

Edge cases

• Retrying the victim immediately can re-deadlock if the other transaction is still running. Serialize first, then retry.
• Foreign keys make Postgres take locks on referenced tables too, widening the lock footprint beyond the tables you named.
• Consider a lock_timeout so a stuck migration fails fast instead of waiting into a deadlock. And after any deadlock, reconcile the victim's history-table state before retrying.

Provenance

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

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

Thread query: psycopg2 error DeadlockDetected: deadlock detected during concurrent migration

TL;DR: Serialize the contending migrations and shrink the lock footprint. Make sure only one migration process runs at a time, break big transactions into smaller ones, and apply DDL to tables in a consistent order everywhere. Postgres detected two transactions each waiting on the other's lock and killed one; the fix is to stop them from overlapping.