Reasons and methods for Waiting for table metadata lock in MySQL

When MySQL performs DDL operations such as alter table, the Waiting for table metadata lock scenario may occur. Moreover, once the alter table TableA operation is stuck in the Waiting for table metadata lock state, any subsequent operations on TableA (including reading) cannot be performed because they will also enter the lock waiting queue of Waiting for table metadata lock in the Opening tables stage. If such a lock waiting queue appears in the core table of the production environment, it will have disastrous consequences.

The reason why alter table generates Waiting for table metadata lock is actually very simple, generally due to the following simple scenarios:

Scenario 1: Long transaction execution blocks DDL and subsequently blocks all subsequent operations on the same table

Through show processlist, you can see that there are ongoing operations (including reads) on TableA. At this time, the alter table statement cannot obtain the metadata exclusive lock and will wait.

This is the most basic situation, which does not conflict with the online DDL in MySQL 5.6. During the general alter table operation (see the figure below), an exclusive metadata lock is acquired in the after create step. When the altering table process is performed (usually the most time-consuming step), reading and writing to the table can proceed normally. This is the performance of online DDL, and writing will not be blocked during the entire alter table process as before. (Of course, not all types of alter operations can be performed online. For details, please refer to the official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
Solution: Kill the session where the DDL is located.

Scenario 2: The transaction is not submitted, blocking the DDL and subsequently blocking all subsequent operations on the same table

You cannot see any operations on TableA through show processlist, but there are actually uncommitted transactions, which can be viewed in information_schema.innodb_trx. Before the transaction is completed, the lock on TableA will not be released, and alter table will not obtain the exclusive lock on metadata.

Solution: Use select * from information_schema.innodb_trx\G to find the sid of the uncommitted transaction, and then kill it to roll it back.

Scenario 3:

There are no operations on TableA through show processlist, and there are no ongoing transactions in information_schema.innodb_trx. This is most likely because in an explicit transaction, a failed operation was performed on TableA (for example, a non-existent field was queried). At this time, the transaction was not started, but the lock acquired by the failed statement was still valid and not released. The failed statement can be found from the performance_schema.events_statements_current table.

The official manual states the following:

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

That is to say, except for syntax errors, the locks acquired by other erroneous statements will not be released until the transaction is committed or rolled back. But the reason for this behavior is hard to understand, because the failed statement is written to the binary log and the locks protect log consistency, because the failed statement is not recorded to the binary log at all.

Solution: Find the sid through performance_schema.events_statements_current and kill the session. You can also kill the session where the DDL is located.

In short, the alter table statement is very dangerous (in fact, its danger is actually caused by uncommitted transactions or long transactions). Before the operation, it is best to confirm that there are no ongoing operations on the table to be operated, no uncommitted transactions, and no error statements in explicit transactions. If there is an alter table maintenance task that is run without supervision, it is best to set the timeout through lock_wait_timeout to avoid long metedata lock waits.