在修改/增加表字段的時候，發現很慢，
show processlist; 時， Waiting for table metadata lock 能一直鎖很久。
官網的一段話，可以理解下
Mysql鎖，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.alter database？
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.Waiting for table metadata lock。
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
?
datatables warning table id。?
MySQL版本為5.6.12。
在進行alter table操作時，有時會出現Waiting for table metadata lock的等待場景。而且，一旦alter table TableA的操作停滯在Waiting for table metadata lock的狀態，后續對TableA的任何操作（包括讀）都無法進行，也會在Opening tables的階段進入Waiting for table metadata lock的隊列。如果是產品環境的核心表出現了這樣的鎖等待隊列，就會造成災難性的后果。
造成alter table產生Waiting for table metadata lock的原因其實很簡單，一般是以下幾個簡單的場景：
alter table，場景一：
通過show processlist可以看到TableA上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨占鎖，會進行等待。
這是最基本的一種情形，這個和mysql 5.6中的online ddl并不沖突。一般alter table的操作過程中（見下圖），在after create步驟會獲取metadata 獨占鎖，當進行到altering table的過程時（通常是最花時間的步驟），對該表的讀寫都可以正常進行，這就是online ddl的表現，并不會像之前在整個alter table過程中阻塞寫入。（當然，也并不是所有類型的alter操作都能online的，具體可以參見官方手冊：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）
場景二：
innodb_lock_wait_timeout。通過show processlist看不到TableA上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，TableA上的鎖不會釋放，alter table同樣獲取不到metadata的獨占鎖。
場景三：
通過show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。這很可能是因為在一個顯式的事務中，對TableA進行了一個失敗的操作（比如查詢了一個不存在的字段），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效。從performance_schema.events_statements_current表中可以查到失敗的語句。
官方手冊上對此的說明如下：
oracle lock table、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.
也就是說除了語法錯誤，其他錯誤語句獲取到的鎖在這個事務提交或回滾之前，仍然不會釋放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解釋這一行為的原因很難理解，因為錯誤的語句根本不會被記錄到二進制日志。
總之，alter table的語句是很危險的，在操作之前最好確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。
?
?
MySQL alter。參考：
http://ctripmysqldba.iteye.com/blog/1938150
http://blog.csdn.net/cloudcraft/article/details/9343069
?
