psycopg2 error DeadlockDetected: deadlock detected during concurrent migration
Fixes DeadlockDetected errors when two migrations run concurrently against Postgres. Use it when overlapping migration processes kill each other over lock ordering. Key trigger: 'deadlock detected' in the logs naming two migration statements.
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 detectedSteps
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.
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.
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.
Retry the killed migration on its own. Expected: it completes without a deadlock.
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