How Metadata Locks Block a DDL Statement Behind Ordinary Queries

A schema change needs exclusive access to a table’s definition, and a query needs a shared read of that definition. On paper these are compatible with everything except each other, so a short ALTER TABLE should wait briefly and proceed. In practice a schema change on a busy table can stall the table for minutes while no long query is running at all. The mechanism responsible is the lock queue, and the failure is caused by the shortest query on the table rather than the longest.

Two locks, not one

Reading a table involves two separate locking concerns. Row-level locks or MVCC snapshots protect the rows. Metadata locks protect the definition — the column list, the index set, the constraints — and they are held for the duration of a statement or a transaction, not for the duration of a row read.

A query takes a shared metadata lock when it opens the table and releases it when the statement ends, or at commit if it is inside an explicit transaction. A schema change takes an exclusive metadata lock, which conflicts with every shared lock on the same object. A second schema change also conflicts with the first. Both engines maintain these locks implicitly: no statement asks for one, and no statement can skip one.

The consequence that catches people is the shared lock’s lifetime in an explicit transaction. A BEGIN followed by a SELECT and then an hour of application code before COMMIT holds the shared metadata lock for the whole hour, because the table’s definition must remain valid for the transaction’s snapshot. The query itself may have taken a millisecond.

The queue is what makes it fatal

If the exclusive request simply waited for existing shared holders to finish, new queries would continue to be served and the schema change would eventually find a gap. That is not what happens. Lock acquisition in both PostgreSQL and MySQL is not fair, but it is queue-aware: a request that cannot be granted immediately waits, and subsequent requests that would conflict with the waiting request queue behind it rather than jumping ahead.

So the sequence is:

1. A long-running transaction holds a shared metadata lock on the table.
2. ALTER TABLE requests exclusive access and waits.
3. A new query requests shared access. It conflicts with the waiting exclusive request, so it queues behind the ALTER rather than alongside the long transaction.
4. Every subsequent query on the table queues behind that one.

The table is now blocked for reads by a schema change that is itself blocked by a transaction that may not even be touching the rows any more. The block is complete: the queue has one exclusive request at its head, and nothing behind it can proceed. This is the mechanism behind the widely observed pattern where a migration appears to hang and the application’s connection pool fills with queries waiting on a table that is not doing anything.

The specific implementation differs in where the state is visible. MySQL’s metadata locks are tracked in a dedicated subsystem and performance_schema.metadata_locks exposes the current holders and waiters, which is the direct way to see the queue. PostgreSQL has no separate metadata lock type for most DDL: ALTER TABLE takes an ACCESS EXCLUSIVE lock on the relation through the ordinary heavyweight lock manager, and pg_locks with the relation lock type shows the granted and waiting entries. The pile-up happens the same way, because a new request is refused not only when it conflicts with a granted lock but also when it conflicts with a request already waiting ahead of it.

Bounding the damage with a timeout

Both engines offer a per-statement timeout that converts an unbounded stall into a failed statement.

MySQL’s lock_wait_timeout defaults to a full year for metadata locks, which is effectively no limit at all; sessions or migrations set it to a few seconds before DDL. PostgreSQL’s lock_timeout works the same way and, importantly, applies to the wait for the exclusive lock rather than to the execution of the statement, so it is a bound on queue time, not on the rewrite.

-- PostgreSQL: fail rather than queue behind a long transaction
SET lock_timeout = '2s';
ALTER TABLE invoices ADD COLUMN settled_at timestamptz;

Setting the timeout is only half the discipline. The other half is retrying with backoff and giving up after a bounded number of attempts, because a migration that retries forever reproduces the original stall in slower motion. A runner that applies this per statement and aborts the whole migration on repeated timeout leaves the ledger in a state a human can reason about, which is the behaviour described in the migration ledger and ordering rules.

The timeout does not help with the case where the exclusive lock has already been granted and the statement is copying the table. That wait is inside the operation, not in the lock queue, and the distinction between the two categories of ALTER is the subject of metadata-only versus rewriting alters.

Why a “quick” DDL still needs the exclusive lock

Every schema change must take the strongest lock even when the change itself is trivial, because the engine has no way to promise that the definition will not change while a statement is running. A column addition that only writes a catalogue row still needs a moment in which no other session holds a shared lock, so the wait is entirely determined by the longest-lived metadata lock holder, not by the cost of the change.

This is why the operational advice is always about the holders rather than the statement: terminate or wait out idle-in-transaction sessions, keep transactions short, and never hold a transaction open across an application-level call to another service. A single connection in idle in transaction state is enough to block a schema change on every table it has touched, for as long as it lives.

Two engine-specific behaviours make the problem worse in specific ways. MySQL’s metadata locks are held until the end of the transaction even for statements that autocommit would have released immediately, so an ORM that opens a transaction and issues a read holds the lock until commit.

The second is two-phase commit. A transaction left in the prepared state by PREPARE TRANSACTION is no longer attached to a session, holds its locks and its snapshot, and stays that way until some coordinator issues COMMIT PREPARED or ROLLBACK PREPARED. Nothing in the server expires it. A prepared transaction that touched a table blocks any DDL on that table for as long as it remains prepared, and because no session owns it, terminating backends does not clear it. Engines that expose prepared transactions for external coordinators therefore need monitoring on the prepared-transaction view, and a timeout policy at the coordinator that guarantees resolution.

Making the change not need the lock for long

The remedy is not a better lock strategy; it is to make the exclusive window short. The general shape is to perform the expensive part under a weaker lock and the trivial part under the exclusive one.

Adding an index is the canonical case. Building an index without blocking writes is possible in both engines with an explicit option (CREATE INDEX CONCURRENTLY in PostgreSQL, ALGORITHM=INPLACE, LOCK=NONE in MySQL). The build runs while writes continue, and the exclusive lock is taken only briefly at the start and end to record the new index in the catalogue. The concurrent build is slower and does more total work, because it must record writes that arrive while it scans, and in PostgreSQL a failed concurrent build leaves an invalid index that must be dropped before retrying — a real operational cost that the non-concurrent form does not have.

Adding a column with a default is the second case, where the engine’s fast-default mechanism turns a rewrite into a catalogue edit, as described in the article on rewriting alters. The lock is still ACCESS EXCLUSIVE, but it is held for microseconds rather than minutes.

The third case is the one that requires application cooperation: dropping or renaming a column that running code still uses. No lock strategy makes that safe, because the incompatibility is between the schema and the code, not between two sessions. Staging the change across two deploys is the only mechanism, and it is the subject of expand and contract schema changes.

Detecting the queue before it becomes an outage

The signal is a rising count of sessions waiting on a lock for one relation while the relation’s own activity is near zero. In MySQL, sys.schema_table_lock_waits joins the metadata lock table with the process list and names the blocking session directly. In PostgreSQL, pg_blocking_pids returns the process identifiers blocking a given backend, and a query over pg_locks filtered to ungranted rows gives the waiting set.

The lock queue’s shape is diagnostic. A single waiting ACCESS EXCLUSIVE request with many shared requests behind it is the pile-up described above, and the fix is to end the holder. Many waiting shared requests with no exclusive request at the head means an ordinary lock contention problem elsewhere, and DDL is not the cause.

Monitoring should track the age of the oldest transaction that has touched a table, not the duration of running statements. A transaction that has been open for an hour and executed one query is the hazard, and no statement-level metric shows it.