MySQL 在 5.5 中引入了 metadata lock. 顧名思義，metadata lock 不是為了保護表中的數據的，而是保護 database objects(數據庫對象)的。包括表結構、schema、存儲過程、函數、觸發器、mysql的調度事件(events). 要理解 metadata lock 最重要的一點就是：將 metadata lock放到數據庫事務的語義中來理解。metadata lock 的作用就是當一個事務在執行時，事務涉及到的所有元數據(metadata，也就是 database objects)，必須是安全的。比如你在一個事物中select一個table，必須保證該table在你的事物完成之前，她不會被刪除了，或者不會被修改了。
相關文檔：http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
1. metadata lock 的作用
MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).
metadata lock管理對database objects的並發訪問，保證數據一致性。
2.metadata lock 會導致性能損耗和鎖爭用
Metadata locking 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 lock 的引入導致一定的性能損耗。對同一個database object的訪問越多，就會越導致該對象上的metadata lock的爭用。
3.
Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex.
metadata lock 並不是 為了替代 表定義緩存。其mutex和lock和 LOCK_open mutex不一樣。
4.
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 explicitly or implicitly started 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.
正在運行中的事務，必須要在事務開始時獲得它要訪問的所有的database objects上的 metadata lock, 然後在事務結束時釋放那些database objects上的metadata lock. 事務和metadata lock的關系是極其緊密的：有事務必然就必然有metadata lock，事物結束就釋放。metadata lock防止事物中的database objects 被修改，比如阻止事物中的table的結構被修改。所以事務中的database objects上執行DDL會被阻塞，直到事務結束。
5.
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;
The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock 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 ...; LOCK TABLE t ... WRITE;
metadata lock不僅僅涉及到事務引擎中的table，同樣也適用於非事務引擎中的table. metadata lock不僅僅阻塞DDL，同時也阻塞 lock table table_name write 語句。
6.
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.
如果一個sql語句語法正確，但是卻執行失敗了，其上的metadata lock並不會馬上釋放，而是要在事務結束之後才釋放。這是為了保證日志的一致性。
7.
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.
自動提交模式(mysql命令行工具默認是自動提交模式)，語句一執行完馬上就釋放metadata lock，因為他是自動提交的單語句事務。
8.
Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.
事務中的metadata lock直到事務結束才釋放，但是有一個特例：事務中的prepare(一般用在存儲過程中的動態語句)語句一執行完馬上釋放對應的metadata lock.
9.
Before MySQL 5.5, 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.
MySQL 5.5 引入了metadata lock，取代了之前版本中的等價物。
但是metadata lock和她之前的等價物有一個區別：metadata lock直到事務結束才釋放，而她的等價物是語句執行完就馬上釋放。metadata lock這樣做的目的是為了保證 binary log 順序的正確。
10. 實驗一(lock table xxx write 語句; 實驗環境Centos下的mysql5.6.27)
首先在終端A執行：
mysql> lock table cats write; Query OK, 0 rows affected (0.01 sec)
然後在終端B執行：
select * from cats;
你會發現被阻塞了。
然後在終端A中執行：
mysql> show processlist; +----+------+-----------+---------+---------+------+---------------------------------+--------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+---------------------------------+--------------------+ | 1 | root | localhost | ngx_lua | Query | 2940 | Waiting for table metadata lock | select * from cats | | 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist | | 3 | root | localhost | NULL | Sleep | 2913 | | NULL | +----+------+-----------+---------+---------+------+---------------------------------+--------------------+ 3 rows in set (0.00 sec)
你可以看到 select * from cats 被阻塞的原因是：Waiting for table metadata lock
而那個 Sleep 中的正是 lock table cats wirte 語句。它也持有了 cats 表上的 metadata lock 的，排斥其它任何事務對該metadata lock的申請。
到這裡你可能會問：說好的MVCC呢？說好的 select 語句可以使用MVCC，不需要用到鎖呢？
所以也許這是MySQL和Oracle的一個區別。
然後我們執行 kill 3 試圖將 lock table cats write 這個session殺掉，期望他是否metadata lock:
mysql> kill 3
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
| 1 | root | localhost | ngx_lua | Query | 3605 | Waiting for table metadata lock | select * from cats |
| 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist |
+----+------+-----------+---------+---------+------+---------------------------------+--------------------+
2 rows in set (0.00 sec)
mysql> unlock table cats;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cats' at line 1
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------+---------+------+-------+------------------+
| 1 | root | localhost | ngx_lua | Sleep | 3757 | | NULL |
| 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist |
+----+------+-----------+---------+---------+------+-------+------------------+
2 rows in set (0.01 sec)
mysql>
然而看到，kill 3 並沒有使 metadata lock 得到釋放。使用了 unlock tables; 語句才釋放了 metadata lock。最後那個select 語句也得以執行完成。
注意：lock table cats read; 語句並不會一致持有 metadata lock 而阻塞其它語句。
11. 實驗二
首先在A終端中修改 autocommit 參數：
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> show session variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from cats; +----+------+ | id | name | +----+------+ | 3 | NULL | | 2 | | | 1 | Andy | +----+------+ 3 rows in set (0.01 sec)
首先修改 session 的 autocommit 參數為 off, 然後開始一個事務。注意該事務一直沒有提交。
然後在終端B中執行一條DDL語句：
發現被阻塞了。而阻塞它的就是那個沒有提交的事務，因為他一致持有 metadata lock，所以導致DDL語句被阻塞。
mysql> show processlist; +----+------+-----------+---------+---------+------+---------------------------------+----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+---------------------------------+----------------------------------+ | 1 | root | localhost | ngx_lua | Query | 0 | init | show processlist | | 2 | root | localhost | ngx_lua | Query | 177 | Waiting for table metadata lock | alter table cats drop index name | | 4 | root | localhost | NULL | Sleep | 322 | | NULL | +----+------+-----------+---------+---------+------+---------------------------------+----------------------------------+ 3 rows in set (0.00 sec)
手動 commit 之後，DDL的阻塞結束，順利執行完成。
mysql> alter table cats drop index name; Query OK, 0 rows affected (4 min 30.81 sec) Records: 0 Duplicates: 0 Warnings: 0
可以看到阻塞了 (4 min 30.81 sec)
上面兩個實驗說明：DDL 語句以及lock table xxx write 和 事務 對 metadata lock 存在互斥爭用。
12. 實驗三
在終端A(autocommit=off)中執行：
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> update cats set name='Linus' where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
然後在終端B(autocommit=on)中執行：
mysql> update cats set name='strup' where id=3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
可以看到終端B並沒有被阻塞。
即使A,B都是 autocommit=off, 並且都不提交，都存在事務中，也不會相互阻塞。這說明普通的update,select,delete並不會在metadata lock上爭用，也就是多個運行中的事物可以同時持有同一個database object上的metadata lock(因為這些非DDL語句並不會修改database objects，它們修改的是表數據而不是表結構).
13. 實驗四
首先在A終端中，設置 autocommit=off; 然後隨便執行一條update,select,delete語句：
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> update uu_test set sex='M' where id=1;
然後在B終端中執行一條 DDL:
alter table uu_test add index(userId);
結果你會發現B終端中的 該條DDL會一直被阻塞，在A查看：
mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+ | 1 | root | localhost | aazj | Query | 0 | init | show processlist | | 2 | root | localhost | aazj | Query | 351 | Waiting for table metadata lock | alter table uu_test add index(userId) | | 4 | root | localhost | NULL | Sleep | 2900 | | NULL | +----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+ 3 rows in set (0.00 sec)
發現是被 A 中為提交事物持有的 metadata lock 所阻塞。可以看到未提交事務的危害有多大！！！！！！它會一直持有 metadata lock.
14. 實驗五：
在終端A中執行一個時間較長的DDL:
mysql> alter table uu_test add index(user_homeTel); Query OK, 0 rows affected (12.86 sec) Records: 0 Duplicates: 0 Warnings: 0
然後在DDL執行完之前，馬上在B終端中執行：
mysql> update uu_test set user_Sex='M' where userId=1; Query OK, 0 rows affected (0.14 sec) Rows matched: 256 Changed: 0 Warnings: 0
我們看到DDL的執行期間，並沒有阻塞其它事務中的update, select, delete 等等語句。也就是說DDL語句對 metadata lock 的持有是瞬時的，並不會再其執行期間一直持有。這點一定要注意。這就是 DDL 語句和 事務還有lock table xxx write語句的區別：DDL語句並不會再執行期間一直持有metadata lock，而是在執行的開始瞬時持有metadata lock，馬上釋放；而事務會在事務期間一直持有metadata lock；lock table xxx write語句也會一直持有metadata lock指定unlock語句發出。
15. 實驗六(DDL最大的危害)：
首先在A終端中設置autocommit=off; 然後隨便執行一個select/update/delete語句，一直不提交，占用 metadata lock:
mysql> select userId,user_Sex from uu_test limit 2; +--------+----------+ | userId | user_Sex | +--------+----------+ | 1 | M | | 2 | F | +--------+----------+ 2 rows in set (0.09 sec)
然後在終端B中執行一條 DDL，很明顯它會被上面的 metadata lock 阻塞：
然後我們在C終端中對同一個表uu_test執行隨便一條：select/update/delete語句，神奇的情況發生！！！！！
可以看到C終端中的對同表uu_test一條select語句盡然被阻塞了！！！！！！
看下終端D中的show processlist:
可以看到：DDL 語句 alter table uu_test add index(user_QQ) 被 未提交的事務阻塞，然後DDL語句進而阻塞了其後事務中所有的針對同表uu_test的任何語句。以為他們都要獲得 metadada lock。這應該是DDL語句的最大危害之處。同理可以推斷：長事物長時間持有 metadata lock, 會阻塞其它DDL語句對metada lock的互斥申請，然後該DDL語句阻塞其後所有的涉及到該database objects的所有語句。這裡按照我們的正常邏輯，C中的語句應該不會被阻塞才對啊？難道是為了防止DDL語句對metadata lock的申請，發生饑餓現象。所以才阻塞了C中的語句。或者對metadata lock的申請維持了一個FIFO的隊列？
然後我們在A終端中執行提交：commit; 然後 B 中的DDL語句立即獲得 metadata lock，然後又馬上釋放；然後C中的 select 也成功獲得metadata lock. B中的DDL語句因為執行時間長，它會在C執行完之後，才執行完成。這也說明了DDL語句對metadata lock的持有是瞬時的，並不會在執行期間一直持有(不然C也不會再B之前執行完成)。其實對應的 innodb status 信息如下：
TRANSACTIONS ------------ Trx id counter 22565 Purge done for trx's n:o < 21037 undo n:o < 0 state: running but idle History list length 486 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4, OS thread handle 0x96cffb70, query id 129 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 3, OS thread handle 0x96e34b70, query id 127 localhost root Waiting for table metadata lock select userId,user_Sex from uu_test limit 1 ---TRANSACTION 0, not started MySQL thread id 2, OS thread handle 0x96e65b70, query id 86 localhost root Waiting for table metadata lock alter table uu_test add index(user_QQ) ---TRANSACTION 22564, ACTIVE 103 sec MySQL thread id 1, OS thread handle 0x96e96b70, query id 46 localhost root cleaning up Trx read view will not see trx with id >= 22565, sees < 22565
此時的處理方法見下：
16. Waiting for table metadata lock 的處理：
在上面的 15. 實驗六(DDL最大的危害)：中，DDL 被未提交事務或者長事務持有的metadata lock阻塞，進而DDL阻塞其它所有涉及到同一個database object 的所有SQL語句。此時該如何處理呢：
1）如果是未提交的事務，則進行提交或者回滾就行了(如何查找未提交的事務見下面的17)；
2）如果是長事務阻塞了DDL，則我們可以 kill sid 的方式殺掉 DDL 所在的session，因為DDL被metadata lock鎖阻塞，所以其實還沒有對表進行任何修改：
mysql> kill 2; Query OK, 0 rows affected (0.01 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 1 | root | localhost | aazj | Query | 0 | init | show processlist | | 3 | root | localhost | aazj | Sleep | 1176 | | NULL | +----+------+-----------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
我們使用 kill 2 將 DDL 語句所在的session殺掉之後，終端C中的waiting from metadata lock, 立即消失了。我們再看DDL所在的終端B的報錯信息：
mysql> alter table uu_test add index(user_QQ); ERROR 2013 (HY000): Lost connection to MySQL server during query
我們這樣 kill 掉DDL所在的session的原因是：DDL因為被metadata lock鎖阻塞，所以實際上並沒有對表結構進行任何修改。所以kill掉是安全。
如果不kill掉的話，它會阻塞其後所有涉及到相同database object的所有的SQL語句(事務中的)。
注意：這裡還涉及到一個如何查找未提交事務的問題：
17. 如何查找一直未提交的事務：
未提交的事務，會導致各種問題，比如死鎖，比如上面的 Waiting for metadata lock 等等，那麼我們在mysql中如何查找因為各種原因而一直未提交的事物呢：
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 22564
trx_state: RUNNING
trx_started: 2015-10-19 13:17:09
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 1
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 312
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
我們看到 trx_id: 22564， trx_state: RUNNING， trx_started: 2015-10-19 13:17:09， trx_mysql_thread_id: 1
事務 22564 的 trx_state 一直是 RUNNING，而且從trx_started知道已經運行很久了，其對應的 trx_mysql_thread_id=1，所以是1號線程忘記了提交。
而且我們從上面的 show engines innodb status 中包括的事務信息，也可以看出：
---TRANSACTION 22564, ACTIVE 103 sec MySQL thread id 1, OS thread handle 0x96e96b70, query id 46 localhost root cleaning up Trx read view will not see trx with id >= 22565, sees < 22565
---TRANSACTION 22564, ACTIVE 103 sec
事務 22564 運行了很久。而且這裡的 22564 和 information_schema.INNODB_TRX 中的 trx_id: 22564 是相符的。
找到了 未提交的 sid之後，在mysql客戶端中執行 kill 1 就可以將其殺掉了。1號session被kill掉之後，被其阻塞的DDL和其它語句都從Waiting for metadata lock 中解脫出來，成功執行。1號session被kill之後，將會被回滾掉。
18. 使用 lock_wait_timeout 來防止 DDL 導致 Waiting metadata lock
既然DDL會被長事務或者為提交的事務該阻塞在 metadata lock 上，那麼我們就可以在執行 DDL 的session中在session級別設置 metadata lock 的超時過期，如果在一定的時間內，DDL沒有獲得 metadata lock，那麼就放棄：
mysql> set lock_wait_timeout=50; Query OK, 0 rows affected (0.07 sec) mysql> alter table uu_test add index(user_QQ); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
上面我們看到 50秒超時，放棄 DDL語句。
lock_wait_timeout 的說明：
lock_wait_timeout
--lock_wait_timeout=#
System Variable
Name
lock_wait_timeout
Variable Scope
Global, Session
Dynamic Variable
Yes
Permitted Values
Type
integer
Default
31536000
Min Value
1
Max Value
31536000
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, andHANDLER statements.
This timeout does not apply to implicit accesses to system tables in the mysql database, such as grant tables modified by GRANT or REVOKE statements or table logging statements. The timeout does apply to system tables accessed directly, such as with SELECT or UPDATE.
The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported.
lock_wait_timeout does not apply to delayed inserts, which always execute with a timeout of 1 year. This is done to avoid unnecessary timeouts because a session that issues a delayed insert receives no notification of delayed insert timeouts.
注意：區分 lock_wait_timeout 和 innodb_lock_wait_timeout, 前者專門用於metadata lock, 默認值是1年, 後者是 innodb 中行鎖的超時過期時間，默認50秒。
19. 總結：
1）metadata lock保護的是元數據，也就是database object(表結構等元數據)，而不是表中的數據；
2）每一個在運行中的事務涉及到的database object，都必須獲得metadata lock，然後在事務結束時進行釋放(parepare語句除外)；
3）DDL 語句以及lock table xxx write 和 事務 對 metadata lock 存在互斥爭用；
普通的update,select,delete並不會在metadata lock上爭用，也就是多個運行中的事物可以同時持有同一個database object上的metadata lock.
4）mysql終端默認是autocommit=on，千萬不要將mysql工具默認修改成autocommit=off； 而JDBC連接默認是 autocommit=off的；
5）metadata lock 因為每一個事務都要先獲得，事物結束時釋放，所以MySQL中一定不要有大事務，特別是運行時間比較長的事物；
不然會導致對metadata lock的長期占用。會阻塞其它事務中任何涉及到該database object的DDL語句和lock table ... write語句；
6）DDL 語句和 事務還有lock table xxx write語句的區別：
DDL 語句並不會再執行期間一直持有metadata lock，而是只在執行的開始瞬時持有metadata lock，馬上釋放；
而事務會在事務期間一直持有metadata lock；lock table xxx write語句也會一直持有metadata lock直到unlock語句解鎖。
7）長的事物 和 lock table ... write語句會長時間持有 metadata lock; 所以在執行DDL語句之前，要使用show processlist語句看DDL語句涉及到的table
是否被某個長時間運行的事物所訪問。不然DDL語句會存在一直被 metadata lock 所阻塞的危險。可怕的不是DDL，而是長事務。或者在DDL語句所在的
session中在session級別設置 lock_wait_timeout 參數，防止 metadata lock 被一直鎖住。
8）mysql命令行工具中執行的 DDL 語句不會受到 autocommit=on/off 的影響，DDL 語句自動開始事務，結束時自動提交事物；
9）DDL語句的最大危害之處：
未提交事物或者長事務，它們會長時間持有 metadata lock, 會阻塞其後的DDL語句對metada lock的互斥申請，
然後該DDL語句對metadata lock的互斥申請，會阻塞其後所有的涉及到該database objects的所有語句，因為它們也要申請metadata lock。
10）未提交事務或者長事務會導致死鎖，waiting metadata lock等等各種問題。解決辦法是找到sid, 然後 kill sid; 讓其回滾。
