Setting lock_timeout and Retrying DDL Safely

You have decided that no migration should be able to freeze a table by queueing for a lock, and now you have to make that true in the tooling you actually use. The difficulty is not the SQL — SET lock_timeout = '2s' is one line — but where that line lives, how it interacts with the migration tool’s own transaction handling, and what happens when it fires. A timeout without a retry turns every busy moment into a failed deploy; a retry that catches too much turns a real error into a loop. This guide builds the combination that works: a timeout scoped to the migration session, and a runner that retries exactly one error class with bounded, jittered backoff. It implements the procedure from DDL Lock Management & Timeouts for the common toolchains.

Symptom / Error Signatures

You need this guide in two situations. The first is the absence of any timeout: a migration runs, waits silently for a lock, and the application stalls — see why one blocked ALTER TABLE stalls every query behind it. The second is a timeout without retry logic, which makes deploys flaky. The tell-tale errors are:

ERROR:  canceling statement due to lock timeout            -- PostgreSQL, SQLSTATE 55P03
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   -- MySQL
FlywayMigrateException: Migration V42__add_region.sql failed ... SQL State: 55P03
liquibase.exception.MigrationFailedException: ... ERROR: canceling statement due to lock timeout

If these appear in CI deploy logs and a manual re-run succeeds a minute later, the timeout is doing its job and the retry is what is missing.

Root Cause Analysis

lock_timeout bounds the time a statement will wait for any single lock. Unlike statement_timeout, it does not count the work the statement does once it has the lock, which makes it the precise tool for the queueing problem. Its scope follows ordinary SET rules: SET LOCAL lasts until the transaction ends, SET lasts for the session, and ALTER ROLE ... SET becomes the default for new sessions of that role. The scope matters because migration tools manage transactions differently.

The retry half has its own subtlety. A lock timeout aborts the whole transaction, so the retry must re-run the whole migration unit, not the single statement. That is only safe if the unit is atomic (PostgreSQL DDL is) or idempotent — which is why idempotent script design and retries go together. On MySQL, where each DDL statement commits implicitly, a multi-statement migration that fails halfway cannot be retried from the top unless every statement is guarded.

Immediate Mitigation

If deploys are already failing on lock timeouts, or stalling without them, these steps restore a safe baseline today.

1. Put the timeout in the migration, not the server config. For Flyway on PostgreSQL, the simplest scoped option is the first line of each risky file:

-- PostgreSQL · Flyway migration V42__add_region.sql · runs in Flyway's per-file transaction
-- WARNING: SET LOCAL is discarded at COMMIT; it cannot affect application sessions.
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN fulfilment_region text;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS fulfilment_region;

For Alembic, enable per-migration transactions in env.py so a retry re-runs one revision rather than the whole upgrade, and set the timeout inside each revision:

# Python · Alembic env.py and a revision · runs as the migration role
# WARNING: without transaction_per_migration=True, SET LOCAL would span the entire upgrade run.
context.configure(connection=connection, target_metadata=target_metadata,
                  transaction_per_migration=True)

def upgrade():
    op.execute("SET LOCAL lock_timeout = '2s'")
    op.add_column("orders", sa.Column("fulfilment_region", sa.Text(), nullable=True))
# ROLLBACK PATH: op.drop_column("orders", "fulfilment_region") in downgrade()

2. Give the migration role a safety-net default. Even if an individual file forgets, the role’s default applies to every session it opens.

-- PostgreSQL · superuser or role owner · affects new sessions of role migrator only
ALTER ROLE migrator SET lock_timeout = '3s';
ALTER ROLE migrator SET statement_timeout = '5min';
-- ROLLBACK PATH: ALTER ROLE migrator RESET lock_timeout; ALTER ROLE migrator RESET statement_timeout;

3. Wrap the tool invocation in a retry that matches only lock errors. Retrying any failure hides syntax errors and constraint violations; match the SQLSTATE or its message.

# Shell · CI deploy job · Flyway CLI with credentials from the environment
# WARNING: only lock-timeout failures are retried; everything else fails the job immediately.
max=5
for i in $(seq 1 $max); do
  if flyway -locations=filesystem:db/migration migrate > flyway.log 2>&1; then
    cat flyway.log; exit 0
  fi
  if ! grep -Eq "55P03|lock timeout|Lock wait timeout exceeded" flyway.log; then
    cat flyway.log; exit 1
  fi
  delay=$(( 2 ** i + RANDOM % 4 ))
  echo "lock timeout on attempt $i/$max, retrying in ${delay}s"; sleep "$delay"
done
cat flyway.log; echo "migration abandoned after $max lock timeouts"; exit 1

4. For MySQL, lower lock_wait_timeout in the session. MySQL has no SET LOCAL, so set the session variable in the migration and keep one DDL statement per migration file so a retry never re-runs an already-committed statement.

-- MySQL 8.0 · migration session · one DDL per file because DDL commits implicitly
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN fulfilment_region VARCHAR(32) NULL, ALGORITHM=INSTANT;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN fulfilment_region, ALGORITHM=INSTANT;

Permanent Fix / Long-Term Pattern

Make the timeout-and-retry contract a property of the pipeline rather than of individual files. The robust pattern has four parts. The migration role carries conservative defaults, so a forgotten SET still fails fast. Each migration file that takes a strong lock sets its own, tighter value. The runner retries only lock errors, with exponential backoff and jitter, for a bounded number of attempts. And the pipeline records the attempt count as a metric, so a table that needs four attempts every deploy becomes visible as a workload problem, as covered in tracking schema migration metrics and SLOs.

Enforce the first two parts in review: a lint rule can require that any file containing ALTER TABLE also contains lock_timeout, which is the kind of check described in Migration Linting & Static Analysis. Keep the backoff ceiling below your deploy’s overall timeout so the pipeline fails with a clear message rather than being killed mid-retry.

Verification Checklist

• Every migration file that takes ACCESS EXCLUSIVE or an exclusive MDL sets lock_timeout or lock_wait_timeout before the DDL.
• The migration role has a default lock_timeout, confirmed with SELECT rolconfig FROM pg_roles WHERE rolname = 'migrator'.
• A staging test with a deliberately open transaction shows the migration failing with 55P03 inside the timeout and succeeding on a later attempt.
• The retry wrapper exits immediately on any non-lock error, proven by a test with a deliberate syntax error.
• Attempt counts are logged or emitted as a metric for every deploy.

Frequently Asked Questions

Should lock_timeout ever be set globally in postgresql.conf? Rarely. A global value applies to application sessions too, where a lock wait is usually legitimate and a timeout turns it into an error. Scope it to the migration role or the migration session; if you want a global guard, use idle_in_transaction_session_timeout instead, which removes blockers rather than failing waiters.

How many retries are enough? Three to five, with exponential backoff that spans at least a minute in total. That covers most transient blockers. If a table needs more, a long-running workload is holding locks during deploy windows, and the fix is to move or reschedule that workload.

Is it safe to retry a migration whose DDL timed out? On PostgreSQL, yes, because DDL is transactional and the failed attempt rolled back completely. On MySQL, only if each migration unit contains a single DDL statement or every statement is idempotent, because earlier statements in a multi-statement file have already committed.

What is the difference between lock_timeout and statement_timeout? lock_timeout limits how long a statement waits for each lock; statement_timeout limits the statement’s total runtime including lock waits. Use a short lock_timeout to prevent queueing and a longer statement_timeout as a ceiling on the work itself.