MySQL ALTER TABLE stuck on "Waiting for table metadata lock" took the site down

On a Tuesday afternoon our helpdesk SaaS went from normal to fully unresponsive in about forty seconds. PHP-FPM workers maxed out, the load balancer started returning 504s, and the database showed 1,180 connections against a usual 140. CPU on the database host was close to idle, which is always a strange combination with an outage.

The deploy pipeline had just started a migration that added a nullable column to tickets. The processlist made the cause visible at once:

mysql> SELECT id, user, time, state, LEFT(info, 70) AS info
    ->   FROM information_schema.processlist
    ->  WHERE state LIKE 'Waiting for table metadata lock' ORDER BY time DESC LIMIT 5;
+--------+--------+------+---------------------------------+------------------------------------------------------------------------+
| id     | user   | time | state                           | info                                                                   |
+--------+--------+------+---------------------------------+------------------------------------------------------------------------+
| 918812 | deploy |  412 | Waiting for table metadata lock | ALTER TABLE tickets ADD COLUMN sla_paused_at DATETIME NULL, ALGORITHM= |
| 918840 | app    |  409 | Waiting for table metadata lock | SELECT t.id, t.subject, t.status FROM tickets t WHERE t.account_id = ? |
| 918841 | app    |  409 | Waiting for table metadata lock | SELECT COUNT(*) FROM tickets WHERE account_id = 4471 AND status = 'ope |
| 918845 | app    |  408 | Waiting for table metadata lock | UPDATE tickets SET last_viewed_at = NOW() WHERE id = 8812331           |
| 918846 | app    |  408 | Waiting for table metadata lock | SELECT t.id, t.subject, t.status FROM tickets t WHERE t.account_id = ? |
+--------+--------+------+---------------------------------+------------------------------------------------------------------------+

The migration used ALGORITHM=INSTANT, which we expected to finish in milliseconds. It had been waiting for almost seven minutes, and every query that touched tickets after it was waiting too.

Reproducing it

Two sessions are enough:

-- session A: open a transaction and read the table, then do nothing
START TRANSACTION;
SELECT id FROM tickets WHERE id = 1;
-- (leave it open)

-- session B: any DDL on the same table
ALTER TABLE tickets ADD COLUMN probe INT NULL, ALGORITHM=INSTANT;   -- hangs

-- session C: a plain read
SELECT id FROM tickets WHERE id = 2;                                 -- also hangs

Session C hangs even though it only wants to read, and session A holds no row locks at all.

What we ruled out

• InnoDB row lock contention. sys.innodb_lock_waits was empty; nothing was waiting on a row.
• A slow table copy. The statement was ALGORITHM=INSTANT; if MySQL could not do it instantly it would have errored, not copied.
• Disk or replication pressure. IOPS were low and replicas were current, because nothing was actually executing.

Root cause

We asked performance_schema who held the metadata lock on tickets:

SELECT ml.lock_type, ml.lock_status, t.processlist_id, t.processlist_user,
       t.processlist_command, t.processlist_time
  FROM performance_schema.metadata_locks ml
  JOIN performance_schema.threads t ON t.thread_id = ml.owner_thread_id
 WHERE ml.object_schema = 'helpdesk' AND ml.object_name = 'tickets'
 ORDER BY ml.lock_status, t.processlist_time DESC;

+---------------------+-------------+----------------+------------------+---------------------+------------------+
| lock_type           | lock_status | processlist_id | processlist_user | processlist_command | processlist_time |
+---------------------+-------------+----------------+------------------+---------------------+------------------+
| SHARED_READ         | GRANTED     |         903117 | reports          | Sleep               |              688 |
| SHARED_UPGRADABLE   | GRANTED     |         918812 | deploy           | Query               |              412 |
| EXCLUSIVE           | PENDING     |         918812 | deploy           | Query               |              412 |
| SHARED_READ         | PENDING     |         918840 | app              | Query               |              409 |
| SHARED_WRITE        | PENDING     |         918845 | app              | Query               |              408 |
...

Connection 903117, user reports, state Sleep for 688 seconds, holding SHARED_READ. That was our SLA report worker, which had recently been moved from nightly to hourly. information_schema.innodb_trx confirmed it had an open transaction that started 11 minutes earlier. The worker ran with autocommit disabled by its database library, executed a few SELECTs, and then spent several minutes calling an external analytics API before committing. Nothing it did was slow from MySQL's point of view. It just never closed the transaction while it waited.

What is happening under the hood

MySQL protects table definitions with metadata locks (MDL), separate from InnoDB row locks. Any statement that uses a table takes a shared MDL on it, and a transaction keeps that MDL until the transaction ends, not until the statement ends. That is what lets a transaction see a consistent table structure.

DDL needs an exclusive MDL, even an INSTANT change, because it swaps the table definition. Online DDL takes it briefly at the start and end; instant DDL only needs it for a moment. But "briefly" still means it must wait for every existing shared holder to finish.

The part that turns a wait into an outage is the queue. MDL requests are granted roughly in order, and a pending exclusive request blocks new shared requests behind it so DDL cannot be starved forever. So the sequence was:

1. The report worker holds SHARED_READ inside an idle transaction.
2. The ALTER asks for EXCLUSIVE and waits for the worker.
3. Every new SELECT and UPDATE asks for SHARED_READ or SHARED_WRITE and waits behind the pending EXCLUSIVE.
4. Connections pile up until the application runs out of workers.

How long does the ALTER wait by default? lock_wait_timeout defaults to 31,536,000 seconds, which is one year.

Environment

Minimal reproduction

The shape of the worker, simplified:

import os
from sqlalchemy import create_engine, text

engine = create_engine(os.environ["REPORTS_DB_URL"])  # mysql+mysqldb://...

def build_sla_report(account_id: int) -> None:
    with engine.connect() as conn:            # implicit transaction begins on first query
        rows = conn.execute(
            text("SELECT id, opened_at, first_reply_at FROM tickets WHERE account_id = :a"),
            {"a": account_id},
        ).all()
        enriched = call_analytics_api(rows)   # takes 3-12 minutes, transaction still open
        conn.execute(text("INSERT INTO sla_reports (account_id, payload) VALUES (:a, :p)"),
                     {"a": account_id, "p": enriched})
        conn.commit()

The SELECT takes a shared MDL on tickets that lives until commit().

Who is affected

Any MySQL or MariaDB version with any storage engine. Typical holders are idle transactions in workers, ORM sessions left open across HTTP calls, interactive clients with autocommit off, long mysqldump runs without --single-transaction, and analytic queries on large tables. The triggering DDL can be anything: ADD COLUMN, ADD INDEX, RENAME, OPTIMIZE, even TRUNCATE.

The fix

During the incident we killed the idle transaction. The ALTER completed in 0.04 seconds and the queue drained in about five seconds:

KILL 903117;

Then we made three permanent changes.

1. DDL fails fast instead of queueing the world. Our migration runner now sets a short lock_wait_timeout for its own session and retries a few times with backoff:

// database/migrations/2025_08_20_000001_add_sla_paused_at_to_tickets.php (excerpt)
public function up(): void
{
    DB::statement('SET SESSION lock_wait_timeout = 5');
    Schema::table('tickets', function (Blueprint $table) {
        $table->dateTime('sla_paused_at')->nullable();
    });
}

# deploy step: retry the migration if it times out on the metadata lock
for attempt in 1 2 3 4 5; do
  php artisan migrate --force && break
  echo "migrate attempt $attempt failed, retrying in $((attempt * 10))s"
  sleep $((attempt * 10))
done

If a long transaction is present, the ALTER now fails with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction after five seconds, and the application only stalls for those five seconds.

2. The worker no longer holds a transaction across network calls.

 def build_sla_report(account_id: int) -> None:
-    with engine.connect() as conn:
-        rows = conn.execute(SELECT_TICKETS, {"a": account_id}).all()
-        enriched = call_analytics_api(rows)
-        conn.execute(INSERT_REPORT, {"a": account_id, "p": enriched})
-        conn.commit()
+    with engine.connect() as conn:
+        rows = conn.execute(SELECT_TICKETS, {"a": account_id}).all()
+        conn.commit()                       # release the metadata lock immediately
+    enriched = call_analytics_api(rows)     # no transaction open here
+    with engine.begin() as conn:
+        conn.execute(INSERT_REPORT, {"a": account_id, "p": enriched})

3. A pre-flight check before migrations. The deploy refuses to run DDL while any transaction has been open for more than 60 seconds:

SELECT trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s
  FROM information_schema.innodb_trx
 WHERE trx_started < NOW() - INTERVAL 60 SECOND;

Alternatives and trade-offs

• gh-ost or pt-online-schema-change. Both copy the table in the background and swap at the end. They are the right tool for changes that are not instant, such as most index builds on very large tables, but the final rename still needs a brief metadata lock, so the same idle transaction can still block the cut-over. They do let you postpone and retry the cut-over.
• Lower wait_timeout for worker users. Kills idle connections, but a connection sleeping inside a transaction for 10 minutes is still allowed if wait_timeout is longer. We set wait_timeout=300 for the reports user as defence in depth.
• Run DDL off-hours. Reduces impact but not the risk; our report worker ran every hour.

How to test the fix

We added an integration test that holds a transaction open and asserts the migration fails fast:

# terminal 1
mysql -h 127.0.0.1 -u root helpdesk -e "START TRANSACTION; SELECT id FROM tickets LIMIT 1; DO SLEEP(60);"

# terminal 2, within the 60 s
time php artisan migrate --path=database/migrations/2025_08_20_000001_add_sla_paused_at_to_tickets.php
# expect: ERROR 1205 ... after about 5 seconds, not a hang

Confirming in production

• The next eight deploys with DDL ran with the pre-flight check; two were delayed by about a minute while a report finished, none hung.
• performance_schema.metadata_locks sampled every minute by our exporter shows no PENDING EXCLUSIVE lock older than 5 seconds since the change.
• The report worker's longest transaction, from innodb_trx, dropped from 11 minutes to 0.3 seconds.

Regression guard

• A Prometheus alert fires when any transaction is older than 120 seconds, labelled with the user.
• The migration base class sets lock_wait_timeout automatically; code review rejects DDL that bypasses it.
• A linter in the worker repository flags network calls made inside engine.connect() blocks.

Security implications

None beyond availability. A long-held metadata lock is an easy way for any database user with SELECT rights to stall writes during a deploy, so read-only reporting users now have a lower wait_timeout and MAX_USER_CONNECTIONS. KILL requires the CONNECTION_ADMIN privilege (or SUPER on older versions) for other users' threads; we granted it only to the on-call role.

Prevention checklist

• Never keep a database transaction open across network calls, sleeps or user input.
• Set a short lock_wait_timeout in every session that runs DDL, and retry.
• Check information_schema.innodb_trx for old transactions before migrations.
• Alert on transaction age, not just query duration.
• Know that ALGORITHM=INSTANT avoids the copy, not the exclusive metadata lock.

References

• MySQL 8.0 Reference Manual: Metadata Locking — https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html
• MySQL 8.0 Reference Manual: The metadata_locks Table — https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html
• MySQL 8.0 Reference Manual: lock_wait_timeout — https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lock_wait_timeout
• MySQL 8.0 Reference Manual: Online DDL Operations — https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
• MySQL 8.0 Reference Manual: The schema_table_lock_waits view — https://dev.mysql.com/doc/refman/8.0/en/sys-schema-table-lock-waits.html
• gh-ost documentation — https://github.com/github/gh-ost