Reasons and solutions for MySQL appearing waiting for table metadata lock

Recently often encountered MySQL database deadlock, depressed dead,
Show Processlist; Waiting for table metadata lock can remain locked for a long time.


Below is a section of the official website, can understand the next
Http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html


8.10.4. Metadata Locking
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 Increa Ses. Metadata contention increases the more this multiple queries attempt to access the same objects.


Metadata locking is not a replacement for the table definition case, and its mutxes and locks differ from the Lock_open mu Tex. The following discussion provides some information about how metadata locking works.


To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) Statement on a table, used in a uncompleted transaction in another session. The server achieves the acquiring metadata locks on tables used within a transaction and deferring release of those lo Cks until the transaction ends. A metadata lock on a table prevents changes to the table ' s structure. This locking approach have the implication that a table that's being used by a transaction within one session cannot be us Ed in DDL statements by other sessions until the transaction ends.


This principle applies isn't only to transactional tables and also to nontransactional tables. Suppose a session begins a transaction that uses transactional table T and nontransactional table NT as follows:


START TRANSACTION;
SELECT * from T;
SELECT * from NT;
Metadata locks is held on both T and NT until the transaction ends. If Another session attempts a DDL operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of the these operations:


DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE NT;
ALTER TABLE nt ...;
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 autocommit mode, each statement was in effect a complete transaction, so metadata locks acquired for the statement are H Eld only to the end of the statement.


Metadata Locks acquired during a PREPARE statement is released once the statement has been prepared, even if preparation Occurs within a multiple-statement transaction.


Before MySQL 5.5.3, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it Released the lock at the end of the statement. This approach had the disadvantage, if a DDL statement occurred for a table is being used by another session in An active transaction, statements could is written to the binary log in the wrong order


An uncommitted transaction uses the A-table, and the other session has an alter on the a table, which appears waiting for table metadata lock


When the INSERT into t select * from Share is run, the ALTER TABLE T add index (PLAY_COUNT) is executed simultaneously,
The ALTER TABLE statement waiting for table metadata lock until the INSERT INTO ... SELECT statement ends.


It's not a legend. 5.6 Support Online DDL? Why would you waiting for table metadata lock?
Later, consider that the online DDL should mean that the SQL statement inserting/modifying/deleting data will not waiting for table metadata lock when ALTER TABLE is in progress.


MySQL 5.6 Enhances many other types of ALTER TABLE operations to avoid copying the table.
Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the TABLE is being ing altered.
This combination of features are now known as online DDL.
Then let ALTER TABLE wait.


Then another magical thing was discovered:
mysql [localhost] {msandbox} (SPC) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+
| Id | USER | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+
| 5 | Msandbox | localhost | SPC |    Query | 1 | Waiting for TABLE metadata LOCK | ALTER TABLE T ADD INDEX (Play_count) |
| 8 | Msandbox | localhost | SPC |    Query | 3 | USER Sleep | SELECT Sleep from T |
| 10 | Msandbox | localhost | SPC |    Query | 0 | init | SHOW Processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+


Reboot and try again:
mysql [localhost] {msandbox} (SPC) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+
| Id | USER | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+
| 1 | Msandbox | localhost | SPC |  Query | 129 | USER Sleep | SELECT Sleep from T |
| 2 | Msandbox | localhost | SPC |  Query | 102 | Waiting for TABLE metadata LOCK | ALTER TABLE t DROP INDEX play_count |
| 3 | Msandbox | localhost | SPC |    Query | 0 | init | SHOW Processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------ -------------+


This time of sleep ... It's been over 100 seconds.


Conclusion:
When preparing the ALTER TABLE TBL, first observe that there are no running SQL statements that cannot be completed in a short period of time in the operation of the TBL table

Reasons and solutions for MySQL appearing waiting for table metadata lock