一沸停、什么是Table Metadata Lock
在MySQL以前的版本中欢际，存在這樣一個(gè)bug：
Description:
If user1 has an active transaction on a table and then user2 drops this table, then user1 does COMMIT, then in the binlog we have something like:
DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;
which is wrong.
MySQL官方文檔鏈接：
http://bugs.mysql.com/bug.php?id=989
這個(gè)bug大致意思是說：當(dāng)一個(gè)會(huì)話在主庫執(zhí)行DML操作還沒提交時(shí)怯伊，另一個(gè)會(huì)話對(duì)同一個(gè)對(duì)象執(zhí)行了DDL操作如drop table火俄，而由于MySQL的binlog是基于事務(wù)提交的先后順序進(jìn)行記錄的逗柴，因此在從庫上應(yīng)用時(shí)侦铜，就出現(xiàn)了先drop table夫啊，然后再向table中insert的情況函卒，導(dǎo)致從庫應(yīng)用出錯(cuò)。
因此撇眯，MySQL在5.5.3版本后引入了Metadata lock报嵌，只有在事務(wù)結(jié)束后才會(huì)釋放Metadata lock，因此在事務(wù)提交或回滾前熊榛，是無法進(jìn)行DDL操作的锚国。
MySQL官方文檔位置：
http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
二、遇到的Metadata Lock問題
前幾天時(shí)候来候，公司的開發(fā)同事讓對(duì)一張表添加字段跷叉，由于該表數(shù)據(jù)量很大，因此使用了pt-online-change-schema工具進(jìn)行字段的添加营搅，在添加的過程中發(fā)現(xiàn)進(jìn)度非常慢云挟，通過shop processlist發(fā)現(xiàn)以及積累了大量的Metadata lock：Waiting for table metadata lock。
這些語句很明顯是被添加字段的DDL所阻塞转质，但是DDL又是被誰阻塞了呢园欣？
查詢當(dāng)前正在進(jìn)行的事務(wù)：
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 7202
trx_state: RUNNING
trx_started: 2016-07-20 23:02:53
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 52402
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
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)
發(fā)現(xiàn)一個(gè)正在運(yùn)行的事務(wù)，從trx_started字段可以判斷出休蟹，該事務(wù)已經(jīng)運(yùn)行了很久沸枯，一直沒有結(jié)束日矫，看來就是這個(gè)事務(wù)阻塞了添加字段的DDL語句。
根據(jù)查詢到的trx_started時(shí)間以及trx_mysql_thread_id到MySQL的general log中查找绑榴，當(dāng)然前提是開啟了general log的功能哪轿，在general日志中對(duì)應(yīng)的時(shí)間發(fā)現(xiàn)該thread執(zhí)行了語句：
set autocommit=0;
關(guān)閉了自動(dòng)提交，再往下看翔怎，oh my god......下面居然是一堆SELECT語句窃诉！
好了，終于找到原因赤套，kill掉先：
mysql> kill 52402;
Query OK, 0 rows affected (0.00 sec)
之后便可以正常執(zhí)行下去了飘痛。
三、測(cè)試驗(yàn)證
為了進(jìn)一步確認(rèn)問題的原因并驗(yàn)證容握，進(jìn)行模擬測(cè)試：
會(huì)話1：顯式開啟事務(wù)宣脉，執(zhí)行SELECT：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----------+
| date |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (0.00 sec)
會(huì)話2：對(duì)test表執(zhí)行DDL：
mysql> alter table test add index `date`(`date`);
語句被阻塞，show processlist查看狀態(tài)：
mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| 16 | system user | | NULL | Connect | 540155 | Waiting for master to send event | NULL |
| 17 | system user | | NULL | Connect | 529732 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 51673 | root | localhost | test | Sleep | 55 | | NULL |
| 51681 | root | localhost | test | Query | 0 | init | show processlist |
| 51683 | root | localhost | test | Query | 29 | Waiting for table metadata lock | alter table test add index `date`(`date`) |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)
可以看到alter table語句的狀態(tài)為Waiting for table metadata lock
會(huì)話3：對(duì)test表進(jìn)行查詢：
mysql> select * from test;
同樣被阻塞：
mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
| 16 | system user | | NULL | Connect | 540305 | Waiting for master to send event | NULL |
| 17 | system user | | NULL | Connect | 529882 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 51673 | root | localhost | test | Sleep | 205 | | NULL |
| 51681 | root | localhost | test | Query | 0 | init | show processlist |
| 51683 | root | localhost | test | Query | 179 | Waiting for table metadata lock | alter table test add index `date`(`date`) |
| 51703 | root | localhost | test | Query | 18 | Waiting for table metadata lock | select * from test |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-------------------------------------------+
6 rows in set (0.00 sec)
接下來我們將會(huì)話1的事務(wù)提交剔氏，效果如下：
- 會(huì)話1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----------+
| date |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 會(huì)話2：
mysql> alter table test add index `date`(`date`);
Query OK, 0 rows affected (3 min 49.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 會(huì)話3：
mysql> select * from test;
+----------+
| date |
+----------+
| 20150616 |
| 20150617 |
| 20150619 |
+----------+
3 rows in set (1 min 8.27 sec)
- show processlist：
mysql> show processlist;
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| 16 | system user | | NULL | Connect | 540411 | Waiting for master to send event | NULL |
| 17 | system user | | NULL | Connect | 529988 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 51673 | root | localhost | test | Sleep | 55 | | NULL |
| 51681 | root | localhost | test | Query | 0 | init | show processlist |
| 51683 | root | localhost | test | Sleep | 285 | | NULL |
| 51703 | root | localhost | test | Sleep | 124 | | NULL |
+-------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)
可以看到塑猖，當(dāng)會(huì)話1提交事務(wù)后，會(huì)話2和會(huì)話3的語句便可以正常執(zhí)行了介蛉，由于被阻塞的原因萌庆，因此執(zhí)行時(shí)間分別為 ( 3 min 49.87 sec ) 溶褪、( 1 min 8.27 sec )
四币旧、總結(jié)
- 對(duì)于純SELECT操作來說，完全沒有必要添加事務(wù)猿妈，MySQL的innodb是基于MVCC多版本控制吹菱，加事務(wù)沒有任何意義
- 需要使用到事務(wù)時(shí)，也要盡量縮小事務(wù)的運(yùn)行時(shí)間彭则，一個(gè)事務(wù)中不要包含太多的語句
