MySQL Metadata Lock Contention: Finding the Session That Stops Everything

The deploy was routine: an online ADD COLUMN on the orders table, the kind of ALTER that finishes in seconds on MySQL 8. Four minutes later the processlist had nine hundred threads, almost all of them in the same state, Waiting for table metadata lock, and the application was effectively down. The blocker, when we finally named it, was an analytics export that had opened a transaction, read the orders table exactly once, and then sat idle for twenty minutes with a client-side cursor still open. The ALTER was innocent. The queue behind it did all the damage.

Metadata lock contention is the most theatrical failure mode MySQL has: total outage, terrifying thread counts, and a root cause that looks like nothing at all. This is my working playbook for it on MySQL 8.0 and 8.4: why the pileup grows so fast, where the blockers usually hide, the kill-or-wait-or-cancel decision, and the guardrails that make the whole incident rare.

The queue plays favorites, and that is the problem

Every statement that touches a table takes a metadata lock on it, and a SELECT inside an explicit transaction holds that shared lock until the transaction ends, not until the statement ends. ALTER TABLE needs an exclusive metadata lock, at least briefly, even when the algorithm is INSTANT. The pileup happens because MDL is not fair: it is priority-based. A waiting write request outranks later read requests, so once an exclusive request is queued, every later shared request lines up behind it, by design, so that DDL cannot be starved forever by a stream of reads. max_write_lock_count is the same safeguard pointed the other way, letting pending reads through after a configured number of consecutive write locks.

The consequence is that the failure is multiplicative, not additive. One idle holder plus one waiting ALTER plus steady traffic equals every new query on that table joining the queue. Threads accumulate at the rate your application issues queries, the connection pool exhausts first, then max_connections, and the first visible symptom is often too many connections errors, which sends everyone chasing connection limits instead of locks. The dashboard signature is distinctive once you have seen it: threads_connected climbs steeply while threads_running stays flat. Everything is waiting and nothing is working. On a busy table that shape is a metadata lock problem until proven otherwise.

The usual blockers

The rogue's gallery is short and worth memorizing, because the blocker is almost always one of these:

• The uncommitted explicit transaction. An ORM with autocommit disabled, an application that crashed between BEGIN and COMMIT, a batch job doing hours of work in one transaction. It read or wrote the table once and holds the shared MDL indefinitely.
• The forgotten interactive session. Somebody's mysql client in a tmux session from Tuesday, inside a transaction, holding locks on the table your migration needs. It looks like a sleeping connection doing nothing, because it is.
• Open cursors. A server-side cursor declared in a stored procedure holds the table until CLOSE or procedure end, and a paused or aborted procedure can leave one lingering. Client-side streaming reads that stopped fetching without closing do the same.
• The replica applier. On a replica, DDL replays through the applier; a long upstream ALTER holds metadata locks downstream, and your local DDL on the same table queues behind it.

The exotic remainder exists, an explicit LOCK TABLES someone forgot to release, another DDL queued ahead of yours, an XA transaction left in PREPARE, a backup holding its own lock, but it surfaces in the same metadata_locks read, which is why the evidence beats the memorized list. Notice what is absent from the gallery: heavy queries. The blocker is almost never something slow. It is something open.

Reading the lock table under pressure

On MySQL 8.0 and 8.4 the wait/lock/metadata/sql/mdl instrument is enabled by default, so performance_schema.metadata_locks works out of the box even mid-incident. GRANTED rows are holders, PENDING rows are waiters, and in the classic incident you will see one old GRANTED shared lock, one PENDING exclusive lock from the ALTER, and a growing wall of PENDING shared requests behind it. The owner_thread_id is an internal thread id, so the useful query joins through to a killable processlist id and the transaction age in one shot:

SELECT p.id AS killable_id,
       p.user, p.host, p.command, p.time AS seconds_idle,
       timestampdiff(SECOND, t.trx_started, now()) AS trx_age_seconds,
       ml.object_schema, ml.object_name, ml.lock_type, ml.lock_status
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads th ON th.thread_id = ml.owner_thread_id
JOIN information_schema.processlist p ON p.id = th.processlist_id
LEFT JOIN information_schema.innodb_trx t ON t.trx_mysql_thread_id = p.id
WHERE ml.object_type = 'TABLE'
  AND ml.lock_status = 'GRANTED'
  AND ml.object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY t.trx_started;

Read it from the top: a GRANTED row whose transaction predates the ALTER is your blocker candidate. If the trx columns are NULL but the session still holds the lock, there is no open transaction, which points at a cursor or a non-transactional hold, and the session's statement history in performance_schema is where you look next. Either way, you now have a killable id attached to evidence, which is the whole point of the exercise.

Kill, wait, or cancel the DDL

Three exits, and choosing well is most of the job. If the blocker is idle, abortable, or runaway, kill it. The ALTER proceeds immediately and total downtime was however long diagnosis took; this is the happy path and the most common one. If the blocker is legitimate work that is nearly finished, waiting is defensible, but only while the queue behind the DDL is tolerable. If the blocker can neither be killed nor finished soon, cancel the DDL itself: a queued ALTER holding a pending exclusive request is the cork in the bottle, and killing the ALTER's own thread drains the queue instantly. You retry later, in a window where the blocker has been dealt with first.

Two traps burn people repeatedly. Restarting the ALTER while the original is still pending creates two exclusive requests and twice the blockage, so always confirm the old one is gone before re-running. And killing a COPY-algorithm ALTER deep into its copy phase on a huge table can trigger a rollback more expensive than waiting would have been, so know which algorithm your DDL is actually using before you touch its thread. An INSTANT operation is cheap to cancel because it was never more than a metadata edit, and a genuinely metadata-only change behaves the same; but an INPLACE rebuild interrupted deep into its work has a rollback of its own to perform, and a half-copied table is the expensive version of that lesson.

Guardrails that make this rare

The single highest-value guardrail is lock_wait_timeout, which governs metadata lock waits and defaults to 31,536,000 seconds. That is one year, which is to say no guardrail at all. Set it for the DDL session before running any migration:

SET SESSION lock_wait_timeout = 30;
ALTER TABLE orders ADD COLUMN loyalty_tier varchar(20);

Now the migration fails fast with a clear error instead of queueing the entire application behind it, and the deploy retries cleanly when the table is actually free. Do not confuse this with innodb_lock_wait_timeout, which governs row locks and defaults to fifty seconds; they are different locks at different layers, and the row-lock timeout does nothing for an MDL pileup.

The rest of the guardrails are process, and they are cheap. Before any DDL, run a pre-flight check for transactions older than a minute and for sessions holding cursors on the target table. Run migrations from a dedicated session with lock_wait_timeout set, never from an application connection pool. Schedule against known batch and export windows, because the analytics job from my war story ran on a schedule we could have simply looked up. And alert on the symptom rather than the cause, because the symptom is unambiguous:

SELECT count(*) AS mdl_waiters
FROM information_schema.processlist
WHERE state = 'Waiting for table metadata lock';

That count should be zero or near it, always. When it climbs into double digits on a busy table, you have minutes before the connection pool goes, which is exactly enough time to run the blocker query above if the page reaches the right person. If your performance_schema foundations need work first, Performance Schema with low overhead is the setup I use, and the purge lag field guide covers the other way one idle transaction quietly hurts everyone.

Where MonPG stands on this

Honesty box, since this is a vendor blog: MonPG monitors PostgreSQL today and does not monitor MySQL; that support is in active development. This incident shape is one of the commitments behind the MySQL work: metadata-lock waiters trended per table, the blocker named with its transaction age and last statement instead of left as a sleeping connection, and a waiting-DDL alert that fires before the connection pool goes, not after. The MySQL monitoring (coming soon) page tracks where that stands. For the PostgreSQL half of your fleet, where the cousin of this failure is a lock queue behind an idle-in-transaction session, the tooling already exists on the PostgreSQL side, with the comparisons and more MySQL field notes on the blog.