October 20, 20263 min read

Find the Transaction Blocking Your Migration

Reproduce a PostgreSQL lock timeout, identify the open transaction, release it deliberately, and verify Ptah’s migration retry and final schema.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

An ALTER TABLE on a one-row table timed out. The table’s size was not the cause: another connection had read it inside a transaction and remained idle without committing.

We reproduced the failure with Ptah 0.13.0 and PostgreSQL 18.6. This recovery article identifies the blocker, ends the transaction we own, then checks the retry and final structure. All work used a disposable database; the example files include the two-terminal sequence.

Bound the wait and retain the error

Version 1 has an orders table with one row. Version 2 adds a nullable tracking-code column. In another connection, we opened the transaction in hold-reader.sql: it sets the application name to synthetic-order-reader, begins, and reads orders.

While that connection remained open, we ran:

ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --lock-timeout 200ms

Ptah exited 2 with these exact error lines:

error: error running migrations: failed to apply migration 2: failed to execute migration SQL: SQL execution failed: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
SQL: ALTER TABLE orders ADD COLUMN tracking_code text

The short timeout keeps this demonstration bounded. It is not a recommended production value or evidence that a busy production table can accept the change in 200 milliseconds.

Find the transaction holding the table lock

Our diagnostic query joins PostgreSQL lock information with activity and filters the synthetic reader on orders:

SELECT a.application_name, a.state, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass AND a.application_name = 'synthetic-order-reader';

The separate client returned:

('synthetic-order-reader', 'idle in transaction', 'AccessShareLock', True)

The connection is idle in transaction with a granted AccessShareLock. Its SELECT has finished, but its transaction has not. PostgreSQL retains the lock until that transaction ends; the schema change requires a conflicting lock. See the PostgreSQL locking reference.

This query is intentionally scoped to the known fixture. A production investigation needs the actual waiting session, its blockers, and their owners. A long-lived transaction can be doing necessary work; discovering it is not authorization to terminate it.

End the transaction we started

We committed the synthetic reader and closed that connection. The retry then had a chance to acquire the table lock. Before retrying, we inspected Ptah’s migration status:

ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --json

The status record marked version 2 dirty, with zero of its one statement applied. An independent column query confirmed that tracking_code was still absent. The failed statement was the column addition, and our inspected failure identified a lock timeout. With the known blocker gone, we allowed the retry of that recorded failed migration:

ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --lock-timeout 200ms --allow-dirty

The retry succeeded. --allow-dirty permits proceeding from the failed state; it is not a diagnosis and does not make an unknown partial change safe to repeat. Nontransactional MySQL DDL can retain changes from earlier statements, so this PostgreSQL result does not justify the same recovery there.

Check the result outside the migration command

A client read the original row with a NULL tracking code, and the schema comparison exited 0:

ptah schema compare --db-url "$DB_URL" --schema-file schema.sql --exit-code

Comparing schema from schema.sql with database postgres://ptah@127.0.0.1:55461/blog_blocker?sslmode=disable
=== SCHEMA COMPARISON ===

No schema differences detected.

The final status reported versions 1 and 2 applied, with no pending migrations or dirty revision. The database history independently recorded both versions as applied.

The recovery resolved the identified blocker and completed the reviewed change. It does not prove that a longer production migration will avoid future blocking, or that increasing a timeout is the right operational fix. The migration application guide documents the CLI controls; the linter walkthrough covers risks that can be reviewed before deployment.

Get help with an unresolved failure

If these checks do not explain your failure, open an issue for a reproducible defect or start a discussion for help choosing a recovery path. Include the Ptah and database versions, the command, a minimal synthetic fixture, and redacted output. Leave credentials and private production data out of the report.

Put Ptah to work

Example files

← All articles