skip to content

An ALGORITHM=INSTANT ALTER TABLE on a busy MySQL orders table hangs, and within seconds every query on that table stalls; what is happening, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 45%

answer

  1. even instant DDL needs a lock
  2. an idle open transaction
  3. the pending exclusive request blocks newcomers
  4. Waiting for table metadata lock
  5. lock_wait_timeout defaults to a year

basics

~20 s

The ALTER is waiting for an exclusive metadata lock that an open transaction on orders still holds, and MySQL makes every new query on the table wait behind that pending request. Kill the ALTER or the blocker, then retry with a short lock_wait_timeout.

solid answer

~40 s

Every `ALTER TABLE`, even an `INSTANT` one, needs an exclusive metadata lock on the table at least briefly. Any transaction that has touched `orders` holds a shared metadata lock until it commits or rolls back, including a session sitting idle mid-transaction. The `ALTER` waits for it, and a pending exclusive request blocks the queries that arrive after it, so `SHOW FULL PROCESSLIST` fills with `Waiting for table metadata lock`. Because `lock_wait_timeout` defaults to 31,536,000 seconds, a year, nothing times out. To recover I kill the `ALTER` first, which frees the queue at once, then find the blocker through `sys.schema_table_lock_waits`, whose `blocking_pid` and `sql_kill_blocking_connection` columns name it. To prevent it I run DDL with `SET SESSION lock_wait_timeout` of a few seconds, check for long transactions first and retry in a loop.

code

sql · 11 lines
sql
-- Who is waiting, and who is blocking them?
SELECT waiting_pid, waiting_query, waiting_query_secs,
       blocking_pid, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = 'shop' AND object_name = 'orders';

-- Retry the change with a bounded wait
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders
  ADD COLUMN gift_message VARCHAR(200) NULL,
  ALGORITHM=INSTANT;

go deeper

for a junior

Recall that even instant schema changes need a brief exclusive lock on the table, and that an open transaction can make them wait.

for a middle

Explain shared versus exclusive metadata locks, why they last until a transaction ends, and what the Waiting for table metadata lock state means.

for a senior

Diagnose the queue from sys.schema_table_lock_waits, kill in the right order, and make every migration run with a short lock_wait_timeout and a retry loop.

for a principal

Make metadata-lock safety part of the migration standard: pre-flight transaction checks, bounded lock waits and a ban on idle open transactions in services.

## Metadata locks in MySQL MySQL protects a table's **definition** with **metadata locks** (MDL), separate from InnoDB's row locks: - Any statement that reads or writes a table takes a **shared** metadata lock on it, and inside a transaction it keeps that lock **until the transaction ends**, not just until the statement ends. - DDL needs an **exclusive** metadata lock, which conflicts with every shared one. Online DDL is described in three phases: initialisation takes a shared upgradeable lock; execution may upgrade to exclusive briefly; and **committing the new table definition upgrades it to exclusive**, if only for a moment. So `ALGORITHM=INSTANT` shortens the time the exclusive lock is held, but it does not remove the need to acquire it. ## Why one ALTER stalls the whole table The failure needs three ingredients: 1. **A transaction holding a shared lock.** A report started a long `SELECT` inside a transaction, or an application opened a transaction, read from `orders` and then went idle without committing. In the processlist the culprit often shows `Command: Sleep`, which makes it easy to miss. 2. **The ALTER requests an exclusive lock** and waits for that transaction to finish. 3. **A pending exclusive request blocks later requests.** MySQL's manual states it directly: a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table. Every new `SELECT`, `INSERT` and `UPDATE` on `orders` now queues behind the `ALTER`. The result looks like a table-wide outage caused by a statement that should have taken milliseconds. Connection pools fill up, application threads time out, and the database itself is otherwise healthy. Two defaults make it worse: - **`lock_wait_timeout`** governs metadata-lock waits and defaults to **31,536,000 seconds**, one year. The `ALTER` will not give up on its own. - **`innodb_lock_wait_timeout`** (50 seconds by default) governs InnoDB **row** locks and does not apply to metadata locks, so lowering it does nothing here. ## Diagnosing it | Where to look | What it tells you | |---|---| | `SHOW FULL PROCESSLIST` | the `ALTER` and the queued queries in state `Waiting for table metadata lock`, plus the idle session | | `performance_schema.metadata_locks` | granted and pending locks per session; the `wait/lock/metadata/sql/mdl` instrument is enabled by default | | `sys.schema_table_lock_waits` | each waiting session with `blocking_pid`, and ready-made `sql_kill_blocking_query` and `sql_kill_blocking_connection` statements | | `information_schema.INNODB_TRX` | open InnoDB transactions and when they started, to spot a long-running one | ## Recovering 1. **Kill the ALTER first.** Removing the pending exclusive request immediately releases every query queued behind it. This is the fastest way to end the outage, and the schema change can be retried later. 2. **Then deal with the blocker.** Identify it through `blocking_pid` and decide whether to `KILL` it. An idle session holding a transaction open is almost always a bug to fix in the application. 3. **Retry the ALTER** once no long transaction touches the table. Killing the blocker first also works, but if the blocker is a long report you may be cancelling legitimate work while the queue keeps growing. ## Preventing it - **Short lock timeout for DDL sessions.** Run `SET SESSION lock_wait_timeout = 5;` (or similar) before the `ALTER`. If the lock is not granted within that time, the statement fails with `ER_LOCK_WAIT_TIMEOUT` and the queue drains; a migration script retries with backoff. - **Pre-flight checks.** Before running DDL, look for transactions older than a few seconds in `INNODB_TRX` and for sessions holding locks on the target table. - **Fix idle-in-transaction code.** Autocommit-off connections, transactions left open across network calls, and reporting sessions holding read transactions are the usual sources. - **Online schema-change tools face the same lock.** Their final table swap also needs an exclusive metadata lock. gh-ost bounds its cut-over lock with `--cut-over-lock-timeout-seconds` (3 seconds by default) and retries; pt-online-schema-change sets `lock_wait_timeout=60` for its own sessions unless you override it. The lesson interviewers look for: an "instant" change is instant only once it holds its lock, and getting that lock depends on every other session on the table.

  • Why doesn't lowering innodb_lock_wait_timeout stop a MySQL ALTER TABLE from waiting on a metadata lock?
    innodb_lock_wait_timeout applies only to InnoDB row-lock waits. Metadata-lock waits, which DDL and DML both take on table definitions, are governed by lock_wait_timeout, whose default is 31,536,000 seconds. Setting lock_wait_timeout low in the DDL session is what bounds the wait.
  • In MySQL, why does killing the waiting ALTER TABLE restore traffic faster than killing the session that blocks it?
    The queued queries are not blocked by the original holder's shared lock, which is compatible with theirs; they are blocked by the ALTER's pending exclusive request. Killing the ALTER removes that request, so every queued query proceeds at once, and you can deal with the long transaction without time pressure.

A cleaner waits at a meeting-room door for the last person inside to leave, and the receptionist stops letting anyone new in until the cleaner has finished. If the person inside has fallen asleep, the corridor fills up; sending the cleaner away reopens the door at once, while waking the sleeper takes longer.

  • ALGORITHM=INSTANT takes no locks, so it can never block queries
  • innodb_lock_wait_timeout will make the waiting ALTER give up
  • Only the ALTER is waiting; other queries are just slow for unrelated reasons
  • An idle session in Sleep state cannot be holding any lock
  • The safest first move is to restart MySQL to clear the locks