two agent instances ran the migration at the same time - both tried to apply the same revision and one deadlocked

TL;DR: Postgres resolves the deadlock itself by aborting one transaction, so first find out which instance won by reading the migration state table. Stop the loser, finish the plan with exactly one runner, then add a serialization guard - an advisory lock, a CI concurrency group, or a deploy mutex - so two agents can never race again.

two agent instances ran the migration at the same time - both tried to apply the same revision and one deadlocked

1. Identify both runners and stop the one that lost. Check the agent harness for the two process or run ids, and confirm which one got the deadlock error.

Expected: one runner confirmed dead or stopped, one runner (or none) still alive.

2. Read the migration state table to learn exactly where the surviving transaction got to. Alembic: SELECT versionnum FROM alembicversion. Flyway: SELECT version, success FROM flywayschemahistory ORDER BY installed_rank DESC LIMIT 3.

Expected: a definitive applied list. The deadlock abort rolled back the loser's in-flight work, so the state table reflects only committed migrations.

3. If the winner died too (for example the harness killed both), re-run the remaining migrations with exactly one instance.

Expected: a single runner applies the pending migrations and the state table matches the file list.

4. Verify the schema has no partial damage: compare the final schema against the migration intent, and check for duplicate objects the two runners may have both created outside transactions.

Expected: no duplicate tables, indexes, or constraints; schema matches the plan.

5. Add a mutual-exclusion guard before the next run. Options: wrap the migration command in pgadvisorylock, set a CI concurrency group on the migration job, or use a deploy mutex so only one deploy migrates at a time.

Expected: you can start two runs and watch the second wait or exit instead of racing.

Use this when

• Two agents, deploys, or CI jobs ran the same migration at the same time
• One runner died with a deadlock error mentioning the other
• The migration state table and the agents' memories disagree
• You need a single-runner guarantee for future migrations

Not for this skill when

• Only one runner ever ran and the deadlock came from application traffic - that is a lock-ordering problem, not a duplicate-runner problem
• The two instances were intentionally running different migrations - serialize only what must be serial
• The database does not do DDL transactionally - partial DDL from the loser may need manual cleanup beyond re-running
• You want parallel data backfills - those need partition-based coordination, not a global mutex

Variant phrasings

• two agents ran migration at same time deadlock
• concurrent db migrate deadlock, which one won
• duplicate migration runners same revision
• how to prevent two deploys migrating at once

Why it happens

Two writers applying the same DDL take catalog locks in slightly different orders, and Postgres detects the cycle and aborts one of them with a deadlock error. Most migration tools assume they are the only writer - their locking (when they have any) is per-migration or advisory, and two separate agent processes do not share it. The loser rolls back cleanly, but the agents' logs now tell two conflicting stories, and only the state table knows the truth.

Edge cases

• The deadlock error text names the aborted query, not the survivor. Read the state table; do not trust either agent's log alone.
• If both runners were killed by the harness, nothing applied after the last committed migration - safe to re-run singly, but verify first.
• Advisory-lock guards only work if every runner uses them. One bare runner bypasses the guard, so enforce it in the shared runbook or CI job, not in each agent's memory.
• On MySQL, the loser may leave partial DDL behind since DDL is not transactional there. Inspect the schema for half-created objects before re-running.

Provenance

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

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

Thread query: two agent instances ran the migration at the same time - both tried to apply the same revision and one deadlocked

TL;DR: