MySQL 'Waiting for table metadata lock': one ALTER TABLE freezes the whole application - find the blocker and run DDL safely

#Symptoms

• A deployment runs ALTER TABLE orders ... and it hangs.
• Within seconds, SHOW PROCESSLIST shows dozens or hundreds of sessions in state Waiting for table metadata lock, including plain SELECTs.
• The application times out; connection pools fill up; the database looks "frozen" but CPU is low.

#Why one ALTER blocks everyone

Every statement takes a metadata lock (MDL) on the tables it uses, held until the transaction ends (not when the statement ends).

1. A transaction runs SELECT ... FROM orders and stays open (an idle connection after BEGIN, a long report, an app that forgot to commit).
2. ALTER TABLE orders needs an exclusive MDL, so it waits for (1).
3. New queries on orders request shared MDLs, but they queue behind the waiting ALTER (pending exclusive locks have priority).

So the real blocker is usually an idle transaction that isn't doing anything.

#Step 1: find the blocker

performance_schema.metadata_locks is enabled by default in MySQL 8.0+. The sys schema gives a ready view:

SELECT object_schema, object_name,
       waiting_pid, waiting_query, waiting_lock_type,
       blocking_pid, blocking_lock_type, sql_kill_blocking_connection
FROM   sys.schema_table_lock_waits;

Look at the blocking connection's transaction:

SELECT p.id, p.user, p.host, p.command, p.time, p.state, p.info,
       t.trx_started, t.trx_state, t.trx_rows_locked
FROM   information_schema.processlist p
LEFT   JOIN information_schema.innodb_trx t ON t.trx_mysql_thread_id = p.id
WHERE  p.id = <blocking_pid>;

A command = Sleep with an old trx_started = idle transaction holding the lock.

If the sys view shows nothing useful, query the raw table:

SELECT ml.object_schema, ml.object_name, ml.lock_type, ml.lock_status, t.processlist_id, t.processlist_info
FROM   performance_schema.metadata_locks ml
JOIN   performance_schema.threads t ON t.thread_id = ml.owner_thread_id
WHERE  ml.object_name = 'orders'
ORDER  BY ml.lock_status;

#Step 2: clear the pile-up

Fastest relief, in order of preference:

1. Kill the ALTER (it's waiting, nothing has changed yet): new queries proceed immediately.
2. Then deal with the blocking transaction: ask the owner, or kill it.

KILL QUERY <alter_pid>;     -- stop the waiting DDL
KILL <blocking_pid>;        -- end the idle transaction (rolls it back)

Warning

Killing a connection rolls back its open transaction. For an idle session that's usually harmless, but check trx_rows_locked and trx_started; a big uncommitted batch takes time to roll back.

#Step 3: run DDL so it can't freeze production

#Always set a short lock wait for DDL sessions

SET SESSION lock_wait_timeout = 5;      -- seconds; default is one year
ALTER TABLE orders ADD COLUMN channel VARCHAR(20), ALGORITHM=INSTANT;

If the MDL isn't available within 5 seconds, the ALTER fails with ER_LOCK_WAIT_TIMEOUT instead of queueing everyone behind it. Retry later in a loop.

#Prefer INSTANT and INPLACE algorithms

State the algorithm explicitly so MySQL refuses instead of silently choosing a blocking one:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

Even INSTANT and INPLACE need a brief exclusive MDL at start and end, so lock_wait_timeout still matters.

#Large or rebuild-type changes: online schema change tools

gh-ost and pt-online-schema-change build a new table copy in the background and swap it in with a very short lock, with throttling based on replica lag:

gh-ost --host=db01 --database=shop --table=orders \
  --alter="MODIFY amount DECIMAL(14,2) NOT NULL" \
  --max-load=Threads_running=50 --critical-load=Threads_running=200 \
  --chunk-size=1000 --cut-over=default --execute

Managed services may restrict some tool options (binlog access, SUPER); check provider guidance.

#Step 4: stop idle transactions

Find long transactions regularly:

SELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_query
FROM   information_schema.innodb_trx
ORDER  BY trx_started
LIMIT  10;

#Prevent it next time

• Every migration tool and script sets lock_wait_timeout to a few seconds and retries.
• Specify ALGORITHM and LOCK in every ALTER; use gh-ost/pt-osc for rebuilds.
• Alert on transactions older than a few minutes and on sessions waiting for MDL.
• Run schema changes when long reports aren't scheduled.