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.

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.