20180117MySQL Why waiting for table metadata lock is present and how to fix it

Transfer from http://www.cnblogs.com/digdeep/p/4892953.html

Turn from: http://ctripmysqldba.iteye.com/blog/1938150 (modified)

When MySQL is doing DDL operations such as ALTER TABLE, there are times when waiting for table metadata lock is waiting for the scene. Furthermore, once ALTER TABLE TableA's operation is stuck in the waiting for table metadata lock state, any subsequent operations on TABLEA (including read) will not work because they are also in opening The tables stage enters the lock wait queue for the waiting for table metadata lock. If there is such a lock waiting queue in the core table of the product environment, it will have disastrous results.

The reason for the ALTER table to produce waiting for table metadata lock is actually very simple, typically following a few simple scenarios:

Scenario One: Long things run, Block DDL, and then block all subsequent operations on the same table

With show Processlist you can see that there is an ongoing operation (including read) on the TableA, at which time the ALTER TABLE statement cannot get to the metadata exclusive lock and waits.

This is the most basic scenario, and this does not conflict with the online DDL in MySQL 5.6. During the operation of the general ALTER TABLE (see), when the after create step acquires the metadata exclusive lock, the read-write to the table can be performed normally when the process to altering table (usually the most time-taking step) is performed, which is the online The performance of the DDL does not block writes as before throughout the ALTER table procedure. (Of course, not all types of alter operations can be online, see the Official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
Workaround: kill the session where the DDL is located.

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

There is no action on TableA through show processlist, but there are actually uncommitted transactions that can be found in Information_schema.innodb_trx . The lock on the TableA is not released until the transaction is complete, and ALTER TABLE also acquires an exclusive lock of metadata.

Processing method: Through the SELECT * from information_schema.innodb_trx\g, find the SID of the uncommitted thing, and then kill it and let it roll back.

Scenario Three:

There is no action on TableA through show processlist, and there is no ongoing transaction 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 nonexistent field), and the transaction did not begin, but the lock obtained by the failed statement was still valid and was not released. Failed statements can be found from the performance_schema.events_statements_current table.

This is described in the official manual as follows:

If the server acquires metadata locks for a statement that's 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 are written to the binary log and The locks protect log consistency.

In other words, except for syntax errors, the locks acquired by other error statements are still not released until the transaction commits or rolls back. Because the failed statement is written to the binary log and the locks protect log consistency But the reason for explaining this behavior is difficult to understand because the wrong statement simply does not will be logged to a binary log.

Workaround: find its SID through Performance_schema.events_statements_current and kill the session. You can also kill the session where the DDL is located.

In short, ALTER TABLE's statement is very dangerous (in fact, his danger is the result of uncommitted things or long transactions), it is better to confirm before the operation of the table does not have any in-progress operation, no uncommitted transactions, and no explicit transaction error statements. If there is an ALTER TABLE maintenance task, running unattended, it is best to set the time-out by lock_wait_timeout and avoid long metedata lock waits.

20180117MySQL Why waiting for table metadata lock is present and how to fix it