Cause method of MySQL appearing Waiting for table metadata lock

When MySQL performs DDL operations such as alter table, the waiting scene of Waiting for table metadata lock sometimes appears. Moreover, the operation of alter table TableA stops in the state of Waiting for table metadata lock, and any subsequent operation (including reading) on TableA cannot be carried out, because they will also enter the lock waiting queue of Waiting ES22table metadata lock at the stage of Opening tables. If such a lock wait queue appears on the core table of a production environment, it will have disastrous consequences.

The reason why alter table produces Waiting for table metadata lock is actually very simple. 1 is generally the following simple scenarios:

Scenario 1: Long things run, blocking DDL, and then blocking 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, alter table statement cannot acquire metadata exclusive lock and will wait.

This is the most basic case, which does not conflict with online ddl in mysql 5.6. 1 In the operation process of alter table (see the figure below), the metadata exclusive lock will be acquired in the after create step. When the altering table process is carried out (usually the most time-consuming step), the reading and writing of this table can be carried out normally, which is the performance of online ddl, and it will not block writing in the whole alter table process as before. (Of course, not all types of alter operations can be online. For details, please refer to the official manual: http://dev.mysql.com/doc/refman/5. 6/en/innodb-create-index-overview.html)
Treatment method: kill drops session where DDL is located.

Scenario 2: Uncommitted things, blocking DDL, and then blocking all subsequent operations on the same table

You can't see any operations on TableA through show processlist, but there are actually uncommitted transactions, which can be seen in information_schema.innodb_trx. The lock on TableA will not be released until the transaction is completed, and alter table will also not acquire the exclusive lock on metadata.

How to deal with it: Find the sid of the uncommitted thing through select * from information_schema.innodb_trx\ G, then drop kill and let it roll back.

Scenario 3:

You can't see any 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 (such as querying a non-existent field), at which time the transaction did not start, but the lock acquired by the failed statement was still valid and not released. Failed statements can be found in the performance_schema. events_statements_current table.

The official manual explains this as follows:

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, except for syntax errors, locks acquired by other error statements will not be released until the transaction is committed or rolled back. because the failed statement is written to the binary log and the locks protect log consistency But it is difficult to understand why this behavior is explained, because incorrect statements are not recorded in binary logs at all.

Processing method: Find its sid through performance_schema.events_statements_current, and kill drops the session. kill can also drop the session where DDL is located.

In a word, the statement of alter table is very dangerous (in fact, its danger is actually caused by uncommitted transactions or long transactions). Before operating, it is best to make sure that there are no in-progress operations on the table to be operated, no uncommitted transactions, and no error statements in explicit transactions. If there is a maintenance task of alter table and it is unsupervised to run, it is best to set the timeout time through lock_wait_timeout to avoid long metedata lock waiting.