migration CLI hung past the agent harness step timeout and got killed - the database was left mid-transaction with...

TL;DR: Find the orphaned database session left behind by the killed CLI and terminate it, then verify no lock remains before re-running anything. The lock belongs to the dead process's open transaction, not to the migration tool - ending the session releases it. Never re-run the migration while the old session still holds the lock; you will just queue behind it again.

migration CLI hung past the agent harness step timeout and got killed - the database was left mid-transaction with the lock held

1. Identify the orphaned session: on Postgres, query pgstatactivity for sessions in idle-in-transaction or active state that belong to the migration tool, excluding your own current session.

Expected: one or two rows with the migration tool's application name and a query start time from before the kill.

1. Check what lock it holds: query pglocks joined to pgstat_activity for that session's process id.

Expected: the lock type (often an AccessExclusiveLock on the table being migrated) confirms it is the blocker.

1. Terminate the orphaned session with pgterminatebackend for that process id.

Expected: the call returns true, and a re-check of pgstatactivity shows the session gone.

1. Verify the lock is released: re-run the pg_locks check.

Expected: no rows for the migrated table from the dead session.

1. Determine whether the killed transaction rolled back: check the migration's version table (alembicversion, flywayschemahistory, schemamigrations, django_migrations, or the prisma migrations table) for the in-flight version.

Expected: the version row is absent, meaning the transaction rolled back and it is safe to re-run.

Use this when

• a migration CLI was killed by a timeout and later migrations hang on locks
• the session view shows an old session from the migration tool
• re-running the migration immediately blocks instead of progressing

Not for this skill when

• the lock is held by a live, still-running migration - wait for it or cancel it deliberately
• the database is MySQL (use its process list and kill statement) or another engine - the session-view queries differ
• the migration failed with a data error rather than a kill - fix the data first

Variant phrasings

• killed migration left open transaction holding lock
• migration timed out, now every migrate hangs on lock
• orphaned migration session blocking new migrations

Why it happens

Most migration tools run each migration inside a database transaction. When the harness kills the CLI process, the database server does not always notice right away - the connection can linger, and the open transaction keeps holding its locks until the server reaps it. The migration tool is gone but its transaction is not, so every new migration touching the same tables queues behind a ghost.

Edge cases

• Terminating the wrong process id kills a live app connection - always verify the process belongs to the migration tool (check the application name and query text) before terminating.
• On MySQL, DDL is not transactional: ending the session does not roll back a half-applied ALTER. Inspect the table structure manually.
• Some connection poolers hide the real backend process id - terminate through the pooler's admin interface instead.
• After clearing the lock, raise the harness step timeout or split the migration so the kill does not recur on the re-run.

Provenance

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