Diagnosing MySQL “Waiting for table metadata lock”

SHOW PROCESSLIST is a wall of identical rows: dozens of application threads, all in state Waiting for table metadata lock, all against the same table, and near the top an ALTER TABLE in the same state. The ALTER was supposed to be an ALGORITHM=INSTANT column add that takes milliseconds. It has been waiting for four minutes, and so has everything behind it. This is MySQL’s version of the lock-queue stall: the DDL needs an exclusive metadata lock (MDL), something is holding a shared one, and the pending exclusive request blocks every new query on the table. This page shows how to find the holder — which is frequently a thread that is doing nothing at all — clear it, and configure migrations so it cannot happen again. It is the MySQL companion to DDL Lock Management & Timeouts.

Who Holds the Metadata Lock? Architecture of the stall. An idle connection with an open transaction holds a SHARED_READ metadata lock on orders. The ALTER TABLE session waits for an EXCLUSIVE metadata lock. New application queries wait behind the pending exclusive request. The performance_schema metadata_locks table links each lock to its owner thread and processlist id. Who Holds the Metadata Lock? Idle connection BEGIN; SELECT … FROM orders; (no COMMIT) MDL on orders GRANTED SHARED_READ · PENDING EXCLUSIVE ALTER TABLE orders waiting for EXCLUSIVE MDL New app queries Waiting for table metadata lock performance_schema metadata_locks + threads + innodb_trx holds queued behind
The holder is usually not running anything — it is a connection that touched the table inside a transaction and never committed, so its shared MDL lives on.

Symptom / Error Signatures

The processlist is the first place the problem shows up:

+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State                           | Info                                       |
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
| 812 | app  | 10.0.3.14 | shop | Sleep   |  611 |                                 | NULL                                       |
| 955 | mig  | 10.0.9.2  | shop | Query   |  241 | Waiting for table metadata lock | ALTER TABLE orders ADD COLUMN region ...   |
| 961 | app  | 10.0.3.22 | shop | Query   |  239 | Waiting for table metadata lock | SELECT * FROM orders WHERE id = 4471       |
| 962 | app  | 10.0.3.19 | shop | Query   |  238 | Waiting for table metadata lock | UPDATE orders SET status = 'paid' ...      |

Note thread 812: Command: Sleep, Info: NULL, Time: 611. It is not running anything, but it has been idle for ten minutes inside an open transaction, and that transaction holds the shared MDL. The application side sees ERROR 1205 (HY000): Lock wait timeout exceeded only if lock_wait_timeout has been lowered; with the default of one year it sees nothing but stalled requests and eventually its own pool or request timeouts.

Root Cause Analysis

Every statement that reads or writes a table acquires a metadata lock on it, and — the crucial detail — holds that lock until the end of the transaction, not the end of the statement. With autocommit on, that is immediately; inside an explicit transaction, it is until COMMIT or ROLLBACK. This protects the transaction from seeing the table’s definition change underneath it, but it means a connection that ran one SELECT inside BEGIN and then went idle keeps a SHARED_READ MDL indefinitely.

DDL needs an EXCLUSIVE MDL. Online algorithms (INPLACE, INSTANT) hold it only briefly — at the start to prepare and at the end to commit the new definition — but they still need it, and they cannot get it while any shared MDL is granted. MDL requests are prioritised so that a pending exclusive request blocks new shared requests; that is the queue that freezes the table.

Holder type How it looks in the processlist Typical cause
Idle open transaction Sleep, Info NULL, large Time ORM or pool leaked a transaction; autocommit=0 session
Long-running query Query, Sending data, large Time report or export on the primary
Open LOCK TABLES / backup lock Sleep after LOCK TABLES or FLUSH TABLES WITH READ LOCK backup tooling holding locks
Prepared-but-not-committed XA not visible as a normal thread abandoned XA transaction

SHOW PROCESSLIST cannot tell you which of the sleeping threads actually holds the lock. performance_schema.metadata_locks, instrumented by default since MySQL 8.0, can.

From Stalled Processlist to the Holder Four steps. Start from the waiting ALTER's processlist id; query performance_schema.metadata_locks for GRANTED locks on the same table; map owner_thread_id to processlist_id through performance_schema.threads; confirm the open transaction age in information_schema.innodb_trx. From Stalled Processlist to the Holder STEP 1 Waiting ALTER processlist id 955, table shop.orders STEP 2 metadata_locks lock_status = GRANTED on the same object STEP 3 threads owner_thread_id → processlist_id 812 STEP 4 innodb_trx trx_started 10 min ago, 0 rows locked
Three joins turn "something is holding a lock" into a specific connection, its host, and how long its transaction has been open.

Immediate Mitigation

1. Stop the queue by killing the waiting DDL, not the application threads. The ALTER is the head of the queue. KILL QUERY on it removes the pending exclusive request, and every queued application query proceeds at once. Nothing has been applied yet, so this is safe.

-- MySQL 8.0 · requires CONNECTION_ADMIN (or SUPER) · safe: the DDL is still waiting
KILL QUERY 955;

2. Find the real holder. Query the MDL instrumentation for granted locks on the table and map them to processlist ids.

-- MySQL 8.0 · read-only · requires SELECT on performance_schema
SELECT t.processlist_id, t.processlist_user, t.processlist_host,
       t.processlist_command, t.processlist_time,
       ml.lock_type, ml.lock_duration, ml.lock_status
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_schema = 'shop' AND ml.object_name = 'orders'
  AND ml.lock_status = 'GRANTED'
  AND t.processlist_id <> CONNECTION_ID();

3. Confirm it is an idle transaction before killing it. innodb_trx shows whether the transaction has modified anything; an old transaction with trx_rows_modified = 0 is almost certainly a leak and safe to end.

-- MySQL 8.0 · read-only
SELECT trx_mysql_thread_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

4. End the holder, then re-run the DDL with a short wait budget. KILL (without QUERY) closes the connection and rolls back its transaction. If it had modified rows, those changes are lost — which is why step 3 matters.

-- MySQL 8.0 · requires CONNECTION_ADMIN · WARNING: rolls back the holder's uncommitted work
KILL 812;
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN region VARCHAR(32) NULL, ALGORITHM=INSTANT;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN region, ALGORITHM=INSTANT;

Permanent Fix / Long-Term Pattern

Two defaults cause most MDL stalls, and both can be changed. The first is lock_wait_timeout = 31536000: every migration session should lower it to a few seconds so that a blocked DDL fails fast and the runner retries, exactly as described in setting lock_timeout and retrying DDL safely. Because MySQL DDL commits implicitly, keep one DDL statement per migration unit so a retry never re-runs work that already committed — the reasoning is laid out in avoiding implicit commits in MySQL DDL migrations.

The second is unbounded idle transactions. Set wait_timeout or, better, fix the application so it never holds a transaction across network calls. Many pools can also be configured to reset or roll back a connection when it is returned. For the heaviest tables, online schema change tools avoid the long exclusive MDL entirely except for a brief cut-over, which is why gh-ost and pt-online-schema-change remain the default for large MySQL changes.

How Long an Idle Transaction Can Block DDL Bar chart of the maximum time a leaked idle transaction can hold a metadata lock under different settings. Default wait_timeout of 28800 seconds allows 8 hours; wait_timeout of 600 allows 10 minutes; application-level transaction discipline brings it under a second. How Long an Idle Transaction Can Block DDL default wait_timeout (8 h) 480 min wait_timeout = 3600 60 min wait_timeout = 600 10 min no transaction across I/O 0.02 min minutes a leaked transaction can hold its shared MDL
Without an idle limit, a leaked transaction can hold a metadata lock for eight hours; lowering wait_timeout for application users caps the damage while the code is fixed.

Verification Checklist

Frequently Asked Questions

Why does ALGORITHM=INSTANT still wait for a metadata lock? Instant DDL avoids rebuilding the table, but it still changes the table definition, which requires a brief exclusive MDL to ensure no transaction is using the old definition. The exclusive portion is milliseconds; the wait for it is whatever the oldest open transaction on the table dictates.

Is innodb_lock_wait_timeout involved? No. That setting governs InnoDB row-lock waits, default 50 seconds. Metadata locks are handled above the storage engine and use lock_wait_timeout, whose default is one year. Lower the latter for migration sessions.

Can I see metadata locks on MySQL 5.7? Yes, but the instrument is off by default. Enable it with UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; and the same metadata_locks query works. MySQL 8.0 enables it by default.

Is killing the sleeping thread safe? Only after checking innodb_trx. KILL rolls back the thread’s open transaction, so if it modified rows, those changes are discarded. A transaction that has been idle for minutes with no modified rows is almost always a leaked read-only transaction and is safe to end.

Related Articles