MySQL 'Waiting for table metadata lock': why 'online' ALTER TABLE still isn't free

You run a one-line ALTER TABLE to add a column. On staging it took half a second. On production it hangs. You check the process list and there it is, sitting in Waiting for table metadata lock, and worse, behind it a growing wall of ordinary SELECTs and INSERTs all in the same state. The application is timing out. You did not lock anything on purpose. So what is holding it?

The answer is almost never the ALTER itself and almost always some other transaction you forgot was open, and the twist that catches experienced teams is that MySQL 8.0's "online DDL" does not save you here. "Online" reduces the copy cost of the change; it does not remove the metadata lock. That lock is a separate, brief, but absolute thing, and on a busy table a brief absolute lock can still take your writes down for seconds to minutes while it waits its turn.

The exact symptom in the process list

You will see this in SHOW PROCESSLIST or performance_schema:

Id    User   Command  Time  State                              Info
201   app    Query    143   Waiting for table metadata lock    ALTER TABLE orders ADD COLUMN ...
202   app    Query    120   Waiting for table metadata lock    SELECT * FROM orders WHERE ...
203   app    Query     98   Waiting for table metadata lock    INSERT INTO orders (...) VALUES ...
...
188   app    Sleep    900                                      NULL

Notice thread 201 (the ALTER) waiting, and threads 202 and 203, ordinary queries, waiting behind it. Notice also thread 188: Sleep, idle for 900 seconds, doing nothing. That sleeping session is very often the actual villain, because it has an open transaction that touched orders and never committed.

Why this happens: the metadata lock queue

Every statement that reads or writes a table takes a shared metadata lock on it, held until the transaction commits or rolls back. Shared locks are compatible with each other, so a thousand concurrent SELECTs coexist happily. A schema change is different: ALTER TABLE needs an exclusive metadata lock, and exclusive is compatible with nothing. It must wait until every shared lock on the table is released, meaning every transaction that touched the table has ended.

The part that turns a stall into an outage is MySQL's fairness rule. Once the ALTER is waiting for its exclusive lock, MySQL queues new requests behind it, even new shared-lock SELECTs that would otherwise be fine. So the sequence is: one old transaction refuses to release its shared lock, the ALTER waits for exclusivity, and then every subsequent query piles up behind the ALTER. One idle session can freeze all access to the table.

Finding the transaction that holds the lock

Step 1: See the pending and granted locks

On MySQL 8.0 with the metadata-lock instrument enabled (it is by default), the sys schema gives you the answer already joined:

SELECT * FROM sys.schema_table_lock_waits\G

If you want the raw view, or you are unsure the instrument is on, query performance_schema.metadata_locks directly and cross-reference the transaction table:

SELECT ml.object_name,
       ml.lock_type,
       ml.lock_status,          -- GRANTED vs PENDING
       ml.owner_thread_id,
       trx.trx_id,
       trx.trx_started,
       trx.trx_mysql_thread_id AS processlist_id
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads th
  ON th.thread_id = ml.owner_thread_id
LEFT JOIN information_schema.INNODB_TRX trx
  ON trx.trx_mysql_thread_id = th.processlist_id
WHERE ml.object_name = 'orders'
ORDER BY ml.lock_status;

The row with lock_status = GRANTED and a lock_type of SHARED_READ or similar, owned by a thread that is not your ALTER, is the blocker. Its processlist_id is what you kill. The PENDING row is your waiting ALTER.

Step 2: Confirm the blocker is safe to kill, then kill it

Check what that thread is actually doing before you act. An idle session (Sleep, or an INNODB_TRX row with an old trx_started and no active query) is almost always safe. A long-running write you care about is not.

-- inspect first
SELECT * FROM information_schema.INNODB_TRX WHERE trx_mysql_thread_id = 188\G
-- then, if safe:
KILL 188;

Killing rolls back that transaction. The instant its shared lock releases, the ALTER acquires its exclusive lock, completes, and the entire queue behind it drains in order. If several old transactions hold the table, kill them all; the ALTER needs every shared lock gone.

Step 3: Make future DDL fail fast instead of queuing the app

The real damage is not the wait, it is that the waiting ALTER takes the whole table down with it. Prevent that by capping how long DDL will wait, per session, before running it:

SET SESSION lock_wait_timeout = 5;   -- seconds
ALTER TABLE orders ADD COLUMN promo_code VARCHAR(32) NULL;

Now if the ALTER cannot get its lock within 5 seconds it errors out and releases the queue, rather than sitting for minutes with the application stacked up behind it. You retry the DDL at a quieter moment. This one habit turns "the site went down during a migration" into "the migration failed and we ran it later", which is the trade you want every time.

Why online DDL does not save you here

The widespread belief is that ALGORITHM=INPLACE (the default for many changes in MySQL 8.0) makes ALTER free because it avoids copying the table. It does avoid the copy, and that is a huge win for the long middle of the operation. But the metadata lock is a separate concern. INPLACE still takes a brief exclusive MDL at the start to begin the operation, and again at the cutover at the end. Those windows are short, but "short" on a table doing thousands of writes a second still means blocking writes while in-flight transactions drain, and if one long transaction is open, that short window becomes a long one.

So "online" is a statement about copy cost, not about locking. For a modest table it is genuinely low-impact. For a large, hot table, the cutover lock alone can be an incident.

For large or hot tables: gh-ost and pt-online-schema-change

When even the cutover lock is unacceptable, use a tool that keeps the table writable throughout by building a shadow copy and swapping it in with a single fast rename.

Tool	How it stays online	Cost
pt-online-schema-change	Copies rows into a new table; triggers keep it in sync during the copy	Mature and widely used, but the triggers add write overhead to every change on the table while it runs
gh-ost	Copies rows and tails the binary log to replay changes; no triggers	Lower load on the primary, more moving parts; needs binlog access

Both still take a brief lock at the final atomic rename, but they eliminate the long lock and the table-rebuild stall in between. Two operational rules matter more than which tool you pick:

• Test against a replica first and time it. Run the change on a replica or a production-sized copy and measure the real duration and load before you touch the primary. A change that takes twenty minutes on production is a very different plan from one that takes twenty seconds.
• Watch replication lag during and after. These tools generate substantial extra write volume, which floods the binlog and can push replicas behind. If read traffic is served from replicas, lag is a user-visible outage of its own. Monitor it and throttle the tool if it climbs.

Verification

-- 1. No pending metadata locks remain on the table
SELECT object_name, lock_type, lock_status
FROM performance_schema.metadata_locks
WHERE object_name = 'orders' AND lock_status = 'PENDING';
-- empty result = nothing waiting

-- 2. The schema change actually applied
SHOW CREATE TABLE orders\G          -- the new column is present

-- 3. The process list is clear of "Waiting for table metadata lock"
SELECT id, state, info FROM information_schema.PROCESSLIST
WHERE state = 'Waiting for table metadata lock';
-- empty result = the queue drained

All three clear means the lock is gone, the change is in, and nothing is queued. If you used an online tool, also confirm replica lag has returned to baseline before you call it done.

What people get wrong

Killing the ALTER instead of the blocker. The natural instinct, because the ALTER is the obvious long-running statement. But killing it just removes the victim; the idle transaction still holds its shared lock, and your next ALTER hangs identically. Kill the granted lock's owner, not the pending one.

Believing 'online DDL' means zero impact. It means no full table copy for supported changes. The metadata lock at start and cutover is unchanged. On a busy table, plan for the lock, not just the copy.

Running big DDL with the default lock_wait_timeout. The default is very long. That is what lets a blocked ALTER queue the entire application behind it for an hour. Set a short session timeout before every production DDL so it fails fast and releases the queue.

Assuming a stuck ALTER is a deadlock or a row-lock problem. It is not. Metadata locks are a different subsystem from InnoDB row locks. If you were chasing row-level contention you would want the lock wait timeout exceeded playbook or the deadlock retry approach; this is neither. The tell is the exact state string, Waiting for table metadata lock.

Long-lived application transactions that never commit. The root cause, upstream of the whole incident. An ORM session left open, a connection pooled mid-transaction, a SELECT in an uncommitted read. Keep transactions short and commit promptly, and this class of stall largely disappears.

When it is still broken

1. You killed the blocker and the ALTER still waits. There is more than one holder. Re-run the lock query; kill each granted shared-lock owner until the pending exclusive lock is granted.
2. New blockers keep appearing. The application is continuously opening transactions on the table. Run the DDL in a maintenance window, or briefly point writes elsewhere, so the table goes quiet long enough for the lock to be acquired.
3. The blocker is a replication thread. On a replica, the SQL apply thread can hold the lock. Do not blindly kill it; understand the replication topology first, or run the change through a tool designed for it.
4. The change is slow even after acquiring the lock. Now it is genuinely a large operation, not a lock problem. That is a job for gh-ost or pt-online-schema-change, and worth pairing with the slow query log playbook to make sure the table is not also being hammered by unindexed reads while you migrate.

The rule that holds across every version: the ALTER you are staring at is usually the victim, not the cause. Find the transaction still holding a shared lock, and remember that "online" describes the copy, never the lock.