Waiting for Table Metadata Lock: Why One ALTER Blocks Every Query

On a Wednesday afternoon I ran this on a table with about 40 million rows:

ALTER TABLE orders
  ADD COLUMN fulfillment_ref VARCHAR(64) NULL,
  ALGORITHM=INSTANT;

Column appended at the end, no index, no type change. The manual calls it instant, and on every other table it had finished before the tab stopped spinning. This one sat there. Two minutes in my phone started buzzing: checkout was timing out.

Not because the ALTER was slow. Because of what it was waiting for, and what everything behind it was waiting for.

The lock nobody thinks about

Every statement touching a table takes a metadata lock. Not an InnoDB row lock, the layer above: a lock on the table definition, so nobody can drop a column out from under a running query. A plain SELECT takes it shared. ALTER TABLE needs it exclusive.

In autocommit mode that shared lock dies with the statement, which is why you can push thousands of reads a second for years and never learn this thing exists. Open a transaction and the lock lives until the transaction ends. Not until your SELECT ends. Until COMMIT or ROLLBACK, whichever comes later. Sometimes never.

Something in our app held a shared MDL on orders. The ALTER asked for exclusive, got refused, and went to sleep. That part is recoverable. What came next is not.

Why one blocked ALTER stops every reader

Once that exclusive request sits in the MDL queue, MySQL will not let new readers jump ahead of it, or a busy table would starve the DDL forever. So every new SELECT on orders asks for its shared lock, sees an exclusive request pending, and waits. Then the next one waits behind that one.

That is how one stuck session becomes an outage in seconds. In SHOW PROCESSLIST it looks like a hundred unrelated problems, because every connection is parked in the same state: Waiting for table metadata lock. Reads are not slow, they are not running at all: that is why the CPU graph sits flat while the site is down.

Find the session that holds it

Do not kill connections by age. Ask who owns the lock. sys has a view that hands you the kill statement as a column, so you are not writing one by hand at 3 a.m.:

SELECT waiting_pid,
       waiting_query_secs,
       blocking_pid,
       blocking_account,
       blocking_lock_type,
       sql_kill_blocking_query
FROM sys.schema_table_lock_waits;

One row came back. blocking_lock_type was SHARED_READ, held for 41 minutes, and the query text was a SELECT that had finished long before I opened my laptop. The connection was idle. The transaction was not.

SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = 1187;

trx_state said RUNNING while the connection's Command said Sleep. That is the signature of the same bug in every codebase where I have found it: something opened a transaction, read a row, then went off to do other work without committing. A report builder, here. I have also seen a worker throw between BEGIN and COMMIT and leave the transaction open.

If sys comes back empty, performance_schema.metadata_locks shows the same dependency per object: PENDING rows are waiters, GRANTED rows are holders. The instrument is on by default in 8.x, so no restart needed:

SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'orders';

The timeout is one year

innodb_lock_wait_timeout defaults to 50 seconds and everybody knows it. lock_wait_timeout, which governs metadata locks, defaults to 31536000 seconds. A year. Your migration does not fail and does not alert. It waits, and every reader waits with it.

So DDL now goes out in a session that set its own budget first:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders
  ADD COLUMN fulfillment_ref VARCHAR(64) NULL,
  ALGORITHM=INSTANT;

Five seconds, then ERROR 1205, and nothing else is affected. A migration you retry after lunch beats a successful one that took checkout down for six minutes. Pin the algorithm too. Without it, MySQL picks what it thinks is cheapest, and that belief was formed before your data volume had any say.

Instant does not mean the lock is cheap

This is what I had wrong, and why the incident happened on a column add rather than a scary type change. ALGORITHM=INSTANT makes the rebuild cheap, not the lock: an instant change still needs an exclusive MDL to flip the data dictionary entry. If a long transaction holds a shared lock, your instant ALTER waits and readers pile up behind it.

Same for the online schema change tools. pt-online-schema-change and gh-ost copy rows into a shadow table without blocking anyone for most of their run, then need a short exclusive lock at cutover to swap names. Short means as soon as open transactions drain. If one never drains, the identical queue forms at the end of a process you were watching for twenty minutes.

What I changed

Two things in the app. The report builder runs with autocommit and a cap on transaction length, because a report holding a read lock for 41 minutes should have been a different query. The pool's connection init now runs SET SESSION lock_wait_timeout = 5, and an idle-in-transaction timeout keeps a mid-transaction connection out of the pool entirely. That one also guards against pool exhaustion, as the connection math behind a full pool explains.

One habit: instant is no longer permission to run DDL mid-afternoon without checking what holds locks on that table. Six queries before, none after. Same lesson as the slow query workflow, pointed at the lock queue instead of the planner.

The pre-flight query and the kill statement live in Snippet Ark now. If you run migrations against a live MySQL database, put them somewhere you will find them before the next deploy.