mysql出現Waiting for table metadata lock的原因及解決方案

標籤：des   http   io   os   ar   使用   for   sp   資料   

最近經常遇到mysql資料庫死結，鬱悶死，
show processlist; 時 Waiting for table metadata lock 能一直鎖很久


下面有官網的一段話，可以理解下
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 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 mutxes and locks differ from the LOCK_open mutex. 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 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. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.


This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that 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 are 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 these operations:


DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
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.


In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.


Metadata locks acquired during a PREPARE statement are 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 that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order


一個沒提交的事務使用了A表， 另外一個session 對A表進行alter，出現waiting for table metadata lock


在insert into t select * from share 運行時， 同時執行alter table t add index(play_count)，
alter table語句會Waiting for table metadata lock， 直到insert into … select 語句結束。


不是傳說5.6支援online DDL嗎？ 怎麼還會Waiting for table metadata lock?
後來想想， online DDL應該是指在alter table進行的時候， 插入/修改/刪除資料的sql語句不會Waiting for table metadata lock.


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 altered. 
This combination OF features IS now known AS online DDL.
那麼就讓alter table wait去吧。


後來又發現另外一個神奇的事：
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(100) FROM t            |
| 10 | msandbox | localhost | spc  | Query   |    0 | init                            | SHOW processlist                    |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+


重啟後再試一次：
mysql [localhost] {msandbox} (spc) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | USER     | Host      | db   | Command | TIME | State                           | Info                                |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
|  1 | msandbox | localhost | spc  | Query   |  129 | USER sleep                      | SELECT sleep(100) 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                    |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+


這個sleep的時間。。。已經超過100秒了…


結論：
在準備alter table tbl 的時候，先觀察一下，有沒有正在啟動並執行，且在短時間內無法結束的sql語句在操作tbl表

mysql出現Waiting for table metadata lock的原因及解決方案