agent's CI matrix ran migrations in parallel against the same test database and they deadlocked

Not specified by the original author.

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:

Not specified by the original author.

TL;DR

A deadlock is never a flake; it is two transactions grabbing locks in opposite order. Pull the deadlock graph from the database logs, find the two code paths that lock the same tables in different order, and make the order consistent everywhere. Retries are a bandage, not the fix.

The exact query

my agent labeled intermittent DB deadlocks as flaky instead of filing the transaction ordering bug

Steps

1. Stop calling it a flake and pull the evidence: grab the deadlock report from the database (postgres logs it with deadlock_timeout, mysql prints it in SHOW ENGINE INNODB STATUS). Identify the two transactions and the exact tables and rows each held and wanted. Expected: You can name the two transactions, the tables, and the lock order each one used. The deadlock is now a diagram, not a mystery.

2. Map both transactions back to code: find the two code paths (often two different tests, or a test and a fixture) that touch the same tables. Write down the order each one acquires its locks. Expected: You see the inversion plainly, for example path A locks users then orders, path B locks orders then users.

3. Enforce one consistent lock order across the whole codebase: pick a canonical table order (alphabetical is fine) and rewrite the offending path to acquire locks in that order. Add a code comment naming the deadlock so the next person does not reintroduce it. Expected: Both paths now lock tables in the same order. The deadlock is structurally impossible, not just unlikely.

4. Reduce the lock footprint while you are there: keep transactions short, avoid locking rows you only read (use the right isolation level), and never hold a DB transaction open across a network call or a sleep in tests. Expected: Transactions hold fewer locks for less time, so even untested paths are less likely to collide.

5. Only after the ordering fix, add a bounded retry with backoff as a backstop for deadlocks you have not found yet. Log every retry so a new deadlock pattern shows up in monitoring instead of hiding behind retries. Expected: Retries exist but almost never fire. If they start firing, the log tells you where the next ordering bug is.

Use this when

• Tests deadlock intermittently, especially under parallel runners
• The deadlock graph shows the same tables locked in different order by two transactions
• An agent keeps retrying or labeling the deadlock flaky instead of investigating
• Two tests or a test plus fixture touch the same tables concurrently

Not for this skill when

• Deadlocks come from a genuinely overloaded or undersized database (fix capacity first)
• Tests run single-threaded with no concurrency (then it is not a real deadlock, look elsewhere)
• The deadlock involves an external system you cannot reorder (then retries plus idempotency are the actual fix)

Variant phrasings

intermittent deadlock in parallel pytest runs

Same fix. Parallelism just makes the ordering inversion show up more often.

postgres deadlock detected in CI but never locally

Local runs are usually serial, CI runs parallel. The ordering bug was always there; CI just rolls the dice more often.

mysql deadlock on the same tables every night

If it is the same tables on a schedule, it is two jobs colliding. Same lock-order fix, applied to the jobs.

Why it happens

Databases detect the circular wait and kill one transaction, which surfaces as an intermittent error that looks exactly like a flake to an agent reading test output. The root cause is always in application code: two paths acquiring locks on the same resources in opposite order. Parallel test runners raise the collision odds, so the bug appears "in CI only" and the agent reaches for the flake label instead of reading the deadlock graph.

Edge cases

• Some deadlocks come from gap locks or next-key locks on ranges, not just row order. If the graph shows range locks, the fix may be a different index or isolation level rather than lock order.
• Retries without idempotency can double-apply writes. Make the retried transaction idempotent or the retry becomes a data-corruption bug.
• If the two colliding paths live in different services, you cannot enforce one lock order in code. Then the fix is a saga or a single-writer design, and the retry needs a proper backoff with jitter.
• Deadlock graphs get truncated in logs. Raise the log verbosity for the deadlock detector temporarily rather than guessing from a partial graph.

Provenance

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

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

Thread query: my agent labeled intermittent DB deadlocks as flaky instead of filing the transaction ordering bug

TL;DR: A deadlock is never a flake; it is two transactions grabbing locks in opposite order. Pull the deadlock graph from the database logs, find the two code paths that lock the same tables in different order, and make the order consistent everywhere. Retries are a bandage, not the fix.

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.