DDL Lock Management & Timeouts

Almost every migration outage that engineers describe as “the ALTER took the site down” is not really about how long the ALTER ran. It is about how long it waited. A schema change that needs a few milliseconds of exclusive access can still stall an application for minutes, because it cannot acquire its lock while some other session holds a conflicting one — and while it waits in the lock queue, every ordinary query that arrives after it lines up behind it. The connection pool fills with sessions waiting on a lock that nobody is using yet, request latency climbs to the pool timeout, and health checks start failing. This part of Database Migration Fundamentals covers the lock model you are actually negotiating with when you run DDL, and the timeout-and-retry discipline that turns an unbounded stall into a bounded, retryable failure. It serves the backend engineer who writes the migration, the DBA who approves it, and the platform engineer whose pipeline runs it at three in the morning.

The discipline is small enough to apply to every migration and strong enough to prevent the most common class of schema incident. Set a short lock_timeout so the DDL gives up quickly instead of queueing; retry it with backoff; clear long-running and idle-in-transaction sessions before you start; and watch the lock graph while it runs. Everything else in the site — Expand and Contract Methodology, online index builds, constraint changes — assumes this layer is already in place.

Concept & Mechanism

PostgreSQL protects tables with eight table-level lock modes. Ordinary reads take ACCESS SHARE; INSERT, UPDATE and DELETE take ROW EXCLUSIVE; most ALTER TABLE forms take ACCESS EXCLUSIVE, which conflicts with every other mode including plain SELECT. Some forms are lighter: CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT take SHARE UPDATE EXCLUSIVE, which lets reads and writes continue, and ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables. The crucial behaviour is the queue: lock requests are granted in arrival order when they conflict. A waiting ACCESS EXCLUSIVE request conflicts with the ACCESS SHARE that the next SELECT wants, so that SELECT waits behind the ALTER even though the table is only being read at that moment. One long transaction ahead of your DDL therefore translates into a full stop for the table.

Locks are held until the transaction ends, not until the statement ends. A migration tool that wraps several statements in one transaction holds the strongest lock it acquired for the duration of the entire file, which is why a slow data update placed after an ALTER TABLE in the same transaction extends the exclusive window. The same rule explains the most insidious blocker: a session sitting idle in transaction — an application that opened a transaction, read a row, and then went off to call an external API — holds its ACCESS SHARE indefinitely and will block your DDL until it commits or is killed.

MySQL has an analogous mechanism in the metadata lock (MDL) subsystem. Every statement that touches a table takes a shared metadata lock for the life of its transaction; DDL needs an exclusive MDL, at least briefly, even for ALGORITHM=INPLACE and ALGORITHM=INSTANT changes. A pending exclusive MDL request blocks new shared requests, so the queueing pathology is identical — the processlist fills with sessions in state Waiting for table metadata lock. MySQL’s knob is lock_wait_timeout, which defaults to 31,536,000 seconds (one year), so an unguarded DDL statement will wait essentially forever.

The consequence for migration design is a two-part rule. First, choose DDL forms whose exclusive portion is instant — metadata-only changes, or the two-step NOT VALID then VALIDATE pattern — so that holding the lock costs nothing. Second, bound how long you are willing to wait for that instant, because even a zero-cost lock becomes an outage if you queue for it behind a two-minute report.

Prerequisites & Decision Criteria

Lock discipline applies to every migration, but how much ceremony it needs depends on the table and the traffic. Use this table to decide how strict to be before the change ships.

Before you run any DDL against a production table, confirm the following:

• The statement’s lock mode is known, and its exclusive portion is instant (metadata-only or split into NOT VALID plus VALIDATE).
• The migration tool runs each risky statement in its own transaction, so the exclusive lock is not held across unrelated work.
• A session-level lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) is set by the migration, not left at the server default.
• The runner retries a lock-timeout failure a bounded number of times with backoff, and fails the deploy loudly after the last attempt.
• Long transactions and idle-in-transaction sessions have been checked immediately before the run.

The retry requirement is what makes a short timeout practical. A single attempt with lock_timeout = '2s' fails whenever a two-second query happens to be running; five attempts spaced a few seconds apart succeed almost always, while each individual failure costs the application at most two seconds of queued latency.

Step-by-Step Procedure

1. Classify the statement’s lock before writing it. Look up the lock mode for every DDL form you use (the matrix above covers the common ones) and restructure anything that holds an exclusive lock while doing work. This is the step that keeps the timeout from being your only line of defence; verify it by reading the statement back against the lock table in the PostgreSQL documentation for your major version.

2. Set the timeouts inside the migration itself. Put the settings in the same transaction or session as the DDL so they cannot be forgotten and cannot leak into application sessions.

-- PostgreSQL 12+ · run by the migration role inside the migration's own transaction
-- WARNING: SET LOCAL only lasts until COMMIT; use plain SET if your tool runs statements outside a transaction.
BEGIN;
SET LOCAL lock_timeout = '2s';        -- give up waiting for the lock after 2 s
SET LOCAL statement_timeout = '15s';  -- cap total runtime of any single statement
ALTER TABLE orders ADD COLUMN fulfilment_region text;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS fulfilment_region;

-- MySQL 8.0 · migration session only · requires ALTER privilege on the schema
-- WARNING: lock_wait_timeout defaults to one year; never run DDL on a hot table without lowering it.
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;

Verify before proceeding: run the migration against staging while a deliberately open transaction holds a lock on the table, and confirm it fails with 55P03 (PostgreSQL) or ERROR 1205 (MySQL) within the timeout instead of hanging.

3. Wrap the migration in a bounded retry loop. The runner, not the SQL, owns retries. Detect the lock-timeout error specifically — never retry arbitrary failures — and back off with jitter so concurrent deploys do not synchronise.

# Shell · CI deploy step · needs DATABASE_URL for the migration role
# WARNING: retries only SQLSTATE 55P03 (lock_not_available); any other error fails immediately.
for attempt in 1 2 3 4 5; do
  if out=$(psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f migrate.sql 2>&1); then
    echo "migration applied on attempt $attempt"; exit 0
  fi
  echo "$out" | grep -q "55P03\|lock timeout" || { echo "$out"; exit 1; }
  sleep $(( attempt * 3 + RANDOM % 3 ))
done
echo "gave up after 5 lock timeouts"; exit 1

4. Clear blockers immediately before the run. Query for transactions older than your timeout and for idle-in-transaction sessions, and decide — by policy, not in a panic — which may be terminated. The dedicated guide on clearing idle-in-transaction sessions covers the safe way to do this.

-- PostgreSQL · read-only · run as a role with pg_read_all_stats
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '30 seconds'
ORDER BY xact_start;

5. Run the DDL and watch the lock graph while it executes. Keep a second terminal open on pg_blocking_pids() or the MySQL performance_schema.metadata_locks table so you can see whether the migration is waiting and on whom. If it is waiting, the timeout will end it; your job is to note the blocker for the post-run review.

6. Record the outcome. Log the attempt count, total wait and the blocking query of any failed attempt. Repeated timeouts on the same table point at a structural problem — a reporting job on the primary, an ORM that leaves transactions open — that a longer timeout would only hide.

Verification & Observability

The single most useful view during a migration is “who is waiting on whom”. PostgreSQL exposes it directly through pg_blocking_pids():

-- PostgreSQL 9.6+ · read-only · run during the migration from a separate session
SELECT waiting.pid        AS waiting_pid,
       left(waiting.query, 50) AS waiting_query,
       blocker.pid        AS blocking_pid,
       blocker.state      AS blocker_state,
       now() - blocker.xact_start AS blocker_xact_age,
       left(blocker.query, 50) AS blocking_query
FROM pg_stat_activity waiting
JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid
WHERE waiting.wait_event_type = 'Lock';

For MySQL, the metadata-lock instrumentation shows pending and granted MDLs side by side; it is enabled by default in 8.0.

-- MySQL 8.0 · read-only · requires SELECT on performance_schema
SELECT ml.object_schema, ml.object_name, ml.lock_type, ml.lock_status,
       t.processlist_id, t.processlist_state, left(t.processlist_info, 60) AS stmt
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON t.thread_id = ml.owner_thread_id
WHERE ml.object_type = 'TABLE' AND ml.object_name = 'orders'
ORDER BY ml.lock_status DESC;

Beyond the live view, three signals tell you the discipline is working over time. The count of lock-timeout retries per deploy should be low and stable — a sudden rise means a new long-running workload. The p99 latency of application queries during migration windows should not step up by more than the configured lock_timeout. And log_lock_waits = on with deadlock_timeout = '1s' makes PostgreSQL log every lock wait longer than a second, which gives you a searchable history of what blocked what; feed those logs into the dashboards described in Migration Observability.

Rollback Path

A lock timeout is itself the rollback: when the DDL gives up, its transaction aborts and nothing has changed. The rollback path you need to design is for the case where the DDL succeeded and the change must be undone. For additive, instant changes the reverse is equally instant — dropping a column that nothing reads, or dropping an index — and it needs exactly the same lock discipline, because DROP COLUMN also takes ACCESS EXCLUSIVE and queues just like the forward change.

-- PostgreSQL · migration role · same timeout discipline as the forward migration
-- WARNING: only safe once no deployed application version reads fulfilment_region.
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders DROP COLUMN IF EXISTS fulfilment_region;
COMMIT;

Rollback is safe when two conditions hold: no running application version depends on the object being removed, and the reverse statement’s exclusive portion is also instant. If either fails — for example, a column type change that would need another rewrite to reverse — roll forward with a corrective migration rather than attempting a blocking reverse. The Rollback Automation section covers how pipelines decide between the two.

Common Errors & Fixes

ERROR: canceling statement due to lock timeout (SQLSTATE 55P03). The DDL waited longer than lock_timeout. Root cause: a conflicting lock held by another transaction — usually a long read or an idle-in-transaction session. Fix: retry with backoff; if it recurs, find the blocker with pg_blocking_pids() and remove the workload from the primary or terminate it by policy.

ERROR: canceling statement due to statement timeout (SQLSTATE 57014). The DDL got its lock but the work took longer than statement_timeout. Root cause: the statement scans or rewrites (for example SET NOT NULL without a validated CHECK constraint). Fix: do not raise the timeout — redesign the change as an online pattern such as those in Adding Constraints Without Downtime.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. In MySQL, this is raised both for row-lock waits (innodb_lock_wait_timeout) and for metadata-lock waits (lock_wait_timeout). Fix: check performance_schema.metadata_locks to learn which; for DDL it is almost always an open transaction on the table, found in information_schema.innodb_trx.

ERROR: deadlock detected (SQLSTATE 40P01) during a migration. Root cause: the migration takes locks on two tables in a different order from application transactions, typically when adding a foreign key. Fix: acquire locks in a consistent order (LOCK TABLE parent, child IN SHARE ROW EXCLUSIVE MODE at the top of the transaction) or split the change so each transaction touches one table.

Child Page Index

The guides under this topic each take one piece of the lock problem to depth. Start with why one blocked ALTER TABLE stalls every query behind it, which walks through the queueing behaviour with a reproducible two-terminal demonstration. Setting lock_timeout and retrying DDL safely turns the procedure above into a reusable runner for Flyway, Liquibase and plain psql. When the application is already stalled, finding the blocking session with pg_blocking_pids is the triage runbook, and clearing idle-in-transaction sessions before a migration shows how to remove the most common blocker without harming healthy traffic. MySQL operators should read diagnosing “Waiting for table metadata lock”, which covers the MDL equivalent end to end.

Lock behaviour also differs sharply between engines that run DDL transactionally and those that do not; the Transactional vs Non-Transactional DDL topic explains how that changes what a timeout rolls back. Pipelines can estimate lock exposure before a migration ever reaches production, as described in gating migrations on estimated lock duration.

Frequently Asked Questions

What is a sensible default lock_timeout for migrations? Two to five seconds for tables that serve live traffic, paired with a retry loop of three to five attempts. The value must be comfortably below your connection-pool checkout timeout and your HTTP request timeout, so that a failed attempt only delays requests rather than failing them. Set it per migration session, never globally on the server, because application queries have different needs.

Why does a fast ALTER TABLE still cause an outage? Because it waits in the lock queue behind a long-running transaction, and every query that arrives after it waits behind it too. The DDL’s own work might take a millisecond, but the stall lasts as long as the oldest conflicting transaction. A short lock_timeout bounds that wait.

Does statement_timeout protect me from lock queueing? Only partially. statement_timeout counts lock-wait time as part of the statement’s runtime, so it will eventually end a queued DDL, but it is usually set high enough for the DDL’s real work, which is far too long to leave application queries stalled. Use lock_timeout for the wait and statement_timeout as a cap on the work.

Is MySQL’s innodb_lock_wait_timeout the same thing? No. innodb_lock_wait_timeout governs row-lock waits inside InnoDB and defaults to 50 seconds. DDL waits on metadata locks, which are governed by lock_wait_timeout, defaulting to one year. Lower lock_wait_timeout in the migration session to get the fail-fast behaviour.