The worst MySQL outage I keep seeing is not caused by a heavy query. It is caused by a tiny ALTER TABLE that should have taken milliseconds. Someone runs an online DDL statement against a busy table, the statement sits in "Waiting for table metadata lock", and within a minute every query touching that table is queued behind it. The application does not slow down gradually. It stops.
The frustrating part is that the ALTER is usually innocent. The real culprit is a transaction somewhere in the fleet that read from the table twenty minutes ago and never committed. Until you can name that transaction, restarting the ALTER just replays the incident. These are my field notes on diagnosing metadata lock (MDL) waits on MySQL 8.0 and 8.4, and on making DDL boring again.
Why an idle transaction blocks DDL
Every statement that touches a table takes a metadata lock on it, and here is the detail that surprises people: a plain SELECT inside a transaction holds a SHARED_READ metadata lock until the transaction ends, not until the statement ends. Under the default autocommit behavior each statement is its own transaction and the lock is released immediately. But the moment an application opens an explicit transaction, runs one read, and then goes idle, it is holding an MDL for as long as it stays open.
ALTER TABLE needs an EXCLUSIVE metadata lock on the table, at least briefly. Even with ALGORITHM=INSTANT or ALGORITHM=INPLACE, the statement must acquire the exclusive MDL during the prepare and commit phases. Online DDL means the table stays readable and writable during the long middle phase of the operation; it does not mean the MDL requirement disappears. I covered how this compares to PostgreSQL's lock model in MySQL online DDL vs PostgreSQL DDL locks, and the short version is the same trap exists in both engines.
The cascade happens because MDL requests queue fairly. The idle transaction holds SHARED_READ. The ALTER requests EXCLUSIVE and waits. Every new SELECT or DML statement then requests its own shared MDL, and because an exclusive request is already queued ahead of them, they all wait too. One sleeping connection plus one ALTER equals a full stop on that table.
Confirm the symptom before anything else
The processlist tells you that you have an MDL problem, but not who caused it. When the incident starts, most threads show the state "Waiting for table metadata lock". Resist the urge to kill the ALTER immediately. First capture the evidence, because the blocker will look completely harmless: a connection in Sleep state with no current query.
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. Start by listing who holds and who wants locks on user tables:
SELECT object_type,
object_schema,
object_name,
lock_type,
lock_duration,
lock_status,
owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
AND object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY object_schema, object_name, lock_status;Read the lock_status column. GRANTED rows are holders; PENDING rows are waiters. In the classic incident you will see one GRANTED SHARED_READ lock, one PENDING EXCLUSIVE lock from the ALTER, and a growing pile of PENDING shared requests behind it.
Name the blocker, not just the lock
The owner_thread_id in performance_schema is an internal thread id, not the connection id you can pass to KILL. Join through performance_schema.threads to translate it and to see what the owning session is doing:
SELECT ml.object_schema,
ml.object_name,
ml.lock_type,
ml.lock_status,
t.processlist_id,
t.processlist_user,
t.processlist_command,
t.processlist_time AS seconds_in_state,
t.processlist_info AS current_query
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 = 'app'
AND ml.object_name = 'orders'
ORDER BY ml.lock_status, t.processlist_time DESC;The blocker almost always shows processlist_command = 'Sleep' and current_query as NULL. That is the idle transaction. Its processlist_time tells you how long it has been sitting there, which is useful for the postmortem conversation with the owning team.
If the sys schema is available, sys.schema_table_lock_waits packages the same diagnosis and even generates the kill statement for you:
SELECT object_schema,
object_name,
waiting_pid,
waiting_query,
blocking_pid,
blocking_lock_type,
sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_name = 'orders';One caution: this view shows every waiting and blocking pair, so during a pileup it returns many rows. Focus on the blocking_pid that appears in every row. That is your root blocker. Killing it releases the SHARED_READ lock, the ALTER grabs its EXCLUSIVE lock, finishes, and the queue drains in seconds.
Use lock_wait_timeout as a blast-radius control
The default value of lock_wait_timeout is 31536000 seconds, which is one year. That default is why a blocked ALTER will happily hold the whole table hostage forever. Before any DDL on a busy table, I set a short timeout for that session only:
SET SESSION lock_wait_timeout = 5;
ALTER TABLE app.orders
ADD COLUMN fulfillment_status VARCHAR(32) NULL,
ALGORITHM = INSTANT;Now the failure mode changes completely. If an idle transaction is holding the MDL, the ALTER gives up after five seconds with ER_LOCK_WAIT_TIMEOUT instead of stalling the application. Nothing behind it queues for longer than five seconds. You retry later or hunt down the blocker calmly. This single setting converts a potential outage into a retriable error, and it belongs in every migration tool configuration, not in the runbook people read after the incident.
Do not confuse lock_wait_timeout with innodb_lock_wait_timeout. The former governs metadata locks; the latter governs InnoDB row lock waits. Setting one does nothing for the other.
Design safe DDL windows
Timeouts limit damage, but a repeatable DDL process avoids it. The pattern I use for busy tables looks like this. First, check for open long transactions before starting: query information_schema.innodb_trx for anything older than your tolerance and chase those sessions first. Second, run the DDL with the short lock_wait_timeout and a retry loop, because a failed attempt is cheap. Third, schedule structural changes for the window when long-running reports and batch jobs are not active, since those are the usual MDL holders. Fourth, for tables where even a brief exclusive MDL is risky, use a tool from the pt-online-schema-change or gh-ost family and accept the operational overhead they bring.
It also pays to fix the source. Idle transactions holding MDLs are usually connection-pool or ORM behavior: a pool configured with autocommit disabled, a session opened for a read path that never commits, or a background job that opens a transaction and then calls an external API. Each one is a small bug that only becomes visible when DDL arrives.
Make MDL waits observable before the incident
Everything above is reactive. The durable fix is monitoring that answers two questions continuously: how old is the oldest open transaction, and are any sessions currently waiting on metadata locks? Both are cheap queries against information_schema.innodb_trx and performance_schema.metadata_locks, and both make excellent alerts. A transaction open longer than a few minutes on an OLTP system is almost always a leak. A nonzero count of PENDING metadata locks that persists across two samples means DDL or LOCK TABLES is stuck right now.
Teams that already watch these two signals treat schema changes as routine. Teams that do not tend to discover metadata locks the hard way, during a deploy, with a migration tool that has no timeout configured. A broader MySQL monitoring baseline should include both signals next to the usual query and replication metrics.
Where MonPG fits
MonPG today is a PostgreSQL monitoring platform, and I want to be straightforward about that: it does not monitor MySQL yet. MySQL support is in active development, and the lock diagnostics described here, including metadata lock waits, oldest transaction age, and blocker identification, are exactly the signals it is being built to surface. You can follow the progress on the MySQL monitoring (coming soon) page. If your team also runs PostgreSQL, the same evidence-first workflow already exists there, and you can start there today with lock chains, long-transaction tracking, and query history in one place.