Table locks & DDL
Row locks aren't the only game. Every statement that touches a table also holds a metadata lock (MDL) on it for the whole transaction, and DDL needs that lock in exclusive mode. That's how a one-millisecond ALTER TABLE takes a production system down.
DDL commits your open transaction
Every DDL statement on MySQL commits implicitly — whatever your transaction had done so far is committed, and the DDL itself cannot be rolled back. A "transactional" migration that mixes DML and DDL is not transactional — see ORM pitfalls.
The classic migration outage
Even an ALGORITHM=INSTANT column add must wait for every open transaction that has touched the table. While it waits, its exclusive request sits at the head of the queue, and every query that arrives after it — plain SELECTs included — has to wait behind it:
A is any long-lived transaction that has touched the table — a report, a stuck job…
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)The migration needs an exclusive metadata lock, so it waits for A. Expected. But now —
B> ALTER TABLE accounts ADD COLUMN note varchar(50);
⏳ B is waiting for a lock…— every new query on the table queues behind the waiting ALTER. This is the outage.
C> SELECT balance FROM accounts WHERE id = 1;
⏳ C is waiting for a lock…
M> SELECT state, info FROM performance_schema.processlist
WHERE state = 'Waiting for table metadata lock' ORDER BY info;
state | info
---------------------------------+--------------------------------------------------
Waiting for table metadata lock | ALTER TABLE accounts ADD COLUMN note varchar(50)
Waiting for table metadata lock | SELECT balance FROM accounts WHERE id = 1
(2 rows)Only when A ends does the pile-up drain — migration first, then the reads.
A> COMMIT;
Query OK
⏵ B resumes:
Query OK
⏵ C resumes:
balance
---------
100
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
The fix: run DDL with a lock_wait_timeout
MDL waits are governed by lock_wait_timeout (a different variable from InnoDB's innodb_lock_wait_timeout, default: one year). Set it low for migrations so they fail fast instead of camping in the queue:
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- the same long transaction as before
balance
---------
100
(1 row)Same migration — but this time it gives up after a second instead of camping in the queue.
B> SET SESSION lock_wait_timeout = 1;
Query OK
B> ALTER TABLE accounts ADD COLUMN note varchar(50); -- ER_LOCK_WAIT_TIMEOUT
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionNo waiting ALTER in the queue means no outage: C's read is instant.
C> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> COMMIT;
Query OKRetry the migration when it can actually get the lock — now it sails through.
B> ALTER TABLE accounts ADD COLUMN note varchar(50);
Query OKVerified against MySQL 8.4.10 · Run it yourself · Scenario source
The through-line is that a shared MDL outlives every statement that took it, right up to COMMIT, so a long-running read and a migration are natural enemies. The outage isn't the DDL doing work — an INSTANT add does almost none — it's the queue that forms behind the DDL's waiting exclusive request, which you can watch from performance_schema.processlist where every stuck session reports Waiting for table metadata lock. The lever that saves you is lock_wait_timeout, measured in whole seconds, a different knob from PostgreSQL's millisecond lock_timeout. The other way a write gets stuck isn't a queue but a cycle — two transactions each holding what the other needs, which is where deadlocks come in.