Why Does ALTER TABLE Hang? Understanding MySQL Metadata Locks

Understanding MySQL Metadata Locking and ALTER TABLE Delays

When modifying or adding columns, you may encounter long‑lasting “Waiting for table metadata lock” messages in SHOW PROCESSLIST. This occurs because MySQL 5.5.3+ uses metadata locks to protect object definitions during transactions.

MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects. Metadata locking is not a replacement for the table definition case, and its mutexes and locks differ from the LOCK_open mutex. To ensure transaction serializability, the server must not permit one session to perform a DDL statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. Suppose a session begins a transaction that uses transactional table t and non‑transactional table nt : START TRANSACTION; SELECT * FROM t; SELECT * FROM nt; Metadata locks are held on both t and nt until the transaction ends. If another session attempts a DDL operation on either table, it blocks until the metadata lock is released at transaction end. In autocommit mode, each statement is a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

Scenario 1: An active operation (including reads) on the target table prevents the ALTER TABLE from acquiring an exclusive metadata lock. In MySQL 5.6 online DDL, the lock is taken after the “create” step, allowing reads and writes during the “altering” step.

Scenario 2: No visible operation appears in SHOW PROCESSLIST, but an uncommitted transaction holds the lock. You can discover it in information_schema.innodb_trx.

Scenario 3: No visible operation and no active transaction, yet a failed statement inside an explicit transaction has acquired a lock. The lock persists until the transaction ends because the failed statement is written to the binary log.

MySQL’s rule: if a statement is syntactically valid but fails during execution, its metadata lock is not released early; it is released only when the transaction commits or rolls back.

To avoid dangerous ALTER TABLE blocks, ensure the target table has no ongoing operations, no uncommitted transactions, and no error statements in an explicit transaction. When running ALTER TABLE in production, set lock_wait_timeout to limit wait time.

References:

http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

http://ctripmysqldba.iteye.com/blog/1938150

http://blog.csdn.net/cloudcraft/article/details/9343069