Table locks & DDL

MySQL 8.4 metadata locks (MDL) protect object definitions. They differ from InnoDB record and intention locks and explicit MySQL LOCK TABLES locks. The metadata-locking manual documents acquisition and release: a table used by an explicit transaction retains its metadata lock until the transaction ends; under autocommit that transaction can be one statement.

ALTER TABLE commits your open transaction

ALTER TABLE implicitly commits pending work before execution. Its schema change is not undone by an application ROLLBACK. Do not generalize this to every DDL form: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE are exceptions to implicit commit, but their effects still cannot be rolled back. See the implicit-commit manual and ORM pitfalls.

The classic migration outage

A's explicit read transaction holds MDL. B's column addition waits for an exclusive metadata lock, and the later SELECT from C waits too. The transcript asserts both process states and their completion after A commits. It does not specify ALGORITHM=INSTANT or measure DDL runtime. Metadata-lock priority rules, including max_write_lock_count, are Documented contracts, not a universal FIFO rule established here.

A

B

C

SELECT (a long-lived transaction)

ALTER TABLE … ADD COLUMN→ ⏳ waits

SELECT — just a read!→ ⏳ waits

COMMIT

⏵ ALTER TABLE … ADD COLUMN→ completes

⏵ SELECT — just a read!→ completes

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…

C's new SELECT queues behind the waiting ALTER in this schedule.

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.11 · Run it yourself · Scenario source

The fix: run DDL with a lock_wait_timeout

The variable manual documents a metadata-lock timeout in whole seconds, defaulting to 31536000 (one year). It applies separately to each lock acquisition attempt, not to total statement runtime.

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 transaction

After the ALTER times out, C reads successfully while A remains open.

C> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

A> COMMIT;
Query OK

Retry the migration when it can actually get the lock — now it sails through.

B> ALTER TABLE accounts ADD COLUMN note varchar(50);
Query OK

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

B still queues while waiting. The scenario proves that after its one-second timeout (1205), C reads successfully while A remains open; it does not prove C could never be blocked during that second. A later retry succeeds after A commits. Use a timeout to limit migration waits, not as a promise of no outage. Inspect performance_schema.metadata_locks and process states via monitoring locks.

Further reading

• The PostgreSQL counterpart

The reconstructed support assessment records the exact manual support and execution limits for this lesson.