One ALTER TABLE can stop every query on a MySQL table

A deploy runs a migration:

ALTER TABLE order_items ADD COLUMN refunded_at DATETIME;

On MySQL 8.0.12 and later and on MariaDB 10.3 and later, adding a column at the end of a table is an instant change that leaves the rows alone. Two minutes later the API requests that touch order_items time out, SHOW PROCESSLIST fills with threads in the state Waiting for table metadata lock, and the connection count climbs toward max_connections. The migration hasn’t finished either: it waits for a lock, and the application traffic waits behind it.

Each statement takes a metadata lock

MySQL guards table definitions with metadata locks (MDL). A statement that reads or writes a table takes a shared metadata lock on it: SHARED_READ for a SELECT, SHARED_WRITE for an INSERT, UPDATE or DELETE. Inside a transaction, the session keeps those locks until COMMIT or ROLLBACK.

ALTER TABLE needs an EXCLUSIVE metadata lock, at least for a moment, to swap in the new definition. Online DDL doesn’t skip that step. ALGORITHM=INSTANT takes the exclusive lock to change the metadata, and ALGORITHM=INPLACE, LOCK=NONE lets DML run during the rebuild but may take the exclusive lock briefly at the start and takes it again at the end.

Somewhere on the server sits a transaction that read order_items seven minutes ago and never committed. Two usual owners:

• a client with autocommit off: Python DB-API drivers such as PyMySQL and mysqlclient start that way, so the first SELECT opens a transaction;
• a mysql session on the bastion where someone typed BEGIN, ran a SELECT and went home.

The processlist shows that session as Sleep with an empty Info column, and its Time counts from the last statement, which says nothing about the transaction’s age. Its SHARED_READ lock on order_items stays the whole time, and the ALTER waits for it.

The queue behind the ALTER

New queries on order_items can’t pass the waiting ALTER, and they wait in the same state. MySQL’s metadata lock manager puts a pending EXCLUSIVE request ahead of new SHARED_READ and SHARED_WRITE requests, so a steady flow of reads can’t starve DDL. The chain has three levels:

1. the old transaction, holding SHARED_READ, often asleep;
2. the ALTER TABLE, waiting for EXCLUSIVE;
3. the application queries that came after it, waiting behind the ALTER.

Each queued query keeps a connection busy, pools run dry or open more connections, and once the pile is long enough, new clients get ERROR 1040. The server won’t end the wait for you: lock_wait_timeout, the limit for metadata lock waits, defaults to 31536000 seconds (a year) on MySQL and 86400 seconds (a day) on MariaDB.

Seeing the chain

On MySQL 8.0 and 8.4, performance_schema.metadata_locks lists granted and pending metadata locks, and its instrument wait/lock/metadata/sql/mdl is on by default. Join it to threads to get the connection IDs that KILL takes:

SELECT t.PROCESSLIST_ID      AS id,
       t.PROCESSLIST_USER    AS user,
       t.PROCESSLIST_COMMAND AS command,
       t.PROCESSLIST_TIME    AS time_s,
       ml.LOCK_TYPE,
       ml.LOCK_DURATION,
       ml.LOCK_STATUS,
       LEFT(t.PROCESSLIST_INFO, 60) AS 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 = 'shop'
  AND ml.OBJECT_NAME = 'order_items'
ORDER BY ml.LOCK_STATUS, t.PROCESSLIST_TIME DESC;

Three kinds of rows matter:

• GRANTED SHARED_READ with duration TRANSACTION on a thread in Sleep: the root, the transaction the rest of the chain waits for;
• a granted SHARED_UPGRADABLE and a PENDING EXCLUSIVE on one thread: the ALTER;
• PENDING SHARED_READ or SHARED_WRITE: the queries queued behind it.

The sys schema offers a shortcut, sys.schema_table_lock_waits, with waiting_pid, blocking_pid and ready-made KILL statements. It pairs each pending lock with each granted lock on the same table without checking whether the modes conflict, so the queued readers appear blocked by the ALTER and by the old transaction alike. The root is the blocking_pid with a TRANSACTION lock that shows up in no row as a waiting_pid.

MariaDB keeps performance_schema off by default, and turning it on takes a restart. The metadata_lock_info plugin installs without one and adds information_schema.METADATA_LOCK_INFO, which lists the metadata locks each thread holds. Its THREAD_ID matches the Id column of the processlist:

INSTALL SONAME 'metadata_lock_info';

SELECT THREAD_ID, LOCK_MODE, LOCK_DURATION
FROM information_schema.METADATA_LOCK_INFO
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'order_items';

A thread that holds MDL_SHARED_READ there and sleeps in the processlist is your root. With no instrumentation at all, list open transactions from INNODB_TRX as in the post about sleeping transactions; the oldest one on a sleeping connection is the usual suspect, though nothing proves it touched order_items.

Picking what to stop

You have two ways out. Pick the one that costs less to redo.

Kill the ALTER. KILL QUERY 24961 stops the waiting statement, and its pending request goes away. The queued queries get their locks at once, because the old transaction’s shared lock doesn’t conflict with theirs. The migration fails, and you run it again later with the settings below.

Kill the root’s connection. The root runs no statement, so KILL QUERY does nothing to it. KILL 24802, the same as KILL CONNECTION 24802, closes the session, and InnoDB rolls its transaction back. The ALTER gets its lock and finishes in milliseconds, and the queue drains behind it. The application that owned the session sees a lost connection.

Check trx_rows_modified in INNODB_TRX before you take the second way. A killed session keeps its metadata lock until the rollback ends, and undoing a few million changed rows takes minutes. For a big writer, killing the ALTER frees the queue sooner.

If the root is a forgotten console session or a stuck worker, end it. If it belongs to a report or batch job that has to finish, kill the ALTER and talk to whoever owns the deploy. On Amazon RDS and Aurora MySQL, use CALL mysql.rds_kill(id) and CALL mysql.rds_kill_query(id) if your account can’t kill other users’ threads.

Keeping it from happening again

• Give migrations a short lock_wait_timeout. Run SET SESSION lock_wait_timeout = 5; in the migration’s session before the ALTER; Flyway can do it through initSql. After five seconds the ALTER gives up with ERROR 1205 (Lock wait timeout exceeded; try restarting transaction), and the queries behind it go through. Retry a few times with a pause; in most cases the old transaction has ended by the third try. On MariaDB, ALTER TABLE order_items WAIT 5 ADD COLUMN refunded_at DATETIME; sets the same limit for one statement.
• Bound idle transactions. wait_timeout closes a sleeping connection and rolls back its transaction, but at its default of 28800 seconds (eight hours) it won’t save a migration. MariaDB 10.3 and later add idle_transaction_timeout and its read-only and write variants. MySQL 8.0 and 8.4 have no such setting, so the fix lives in the application: autocommit on, and no network calls inside a transaction.
• Check before you migrate. Have the deploy step query INNODB_TRX for transactions older than a minute and hold the migration while it finds any.
• Use an online schema change tool on big tables. gh-ost and pt-online-schema-change copy the table in the background and still need a metadata lock to create triggers or to swap the tables at cut-over. Both keep that wait short and retry: gh-ost’s --cut-over-lock-timeout-seconds defaults to 3, and pt-online-schema-change sets lock_wait_timeout=60 for its session unless you lower it with --set-vars.

On the phone

RowDeck’s Locks screen puts row locks and metadata locks in one tree. In this case it marks the migration DDL WAITING with Metadata lock · EXCLUSIVE on shop.order_items, counts the queries queued behind it, and shows the transaction that keeps the lock. The Blocked tile on Health turns Critical as soon as queries queue behind a waiting EXCLUSIVE request. With Pro, Kill the waiting ALTER shows how many sessions it unblocks before you confirm. If the metadata lock instrument is off on the server, the screen says so and shows how to turn it on.

More about the screen: MySQL metadata locks on your iPhone.