後設資料鎖（Metadata Lock，以下簡稱MDL）計劃寫三篇，這篇主要是介紹MDL的引入背景和基本概念，後兩篇會著重介紹MySQL 5.7（包括8.0）及5.6中如何定位MDL問題。
線上上進行DDL操作時，相對於其可能帶來的系統負載，其實，我們最擔心的還是MDL其可能導致的阻塞問題。
一旦DDL操作因獲取不到MDL被阻塞，後續其它針對該表的其它操作都會被阻塞。典型如下，如阻塞稍久的話，我們會看到Threads_running飆升，CPU告警。
mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Sleep | 57 | | NULL |
| 12 | root | localhost | employees | Query | 40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
| 13 | root | localhost | employees | Query | 35 | Waiting for table metadata lock | select * from slowtech.t1 |
| 14 | root | localhost | employees | Query | 30 | Waiting for table metadata lock | select * from slowtech.t1 |
| 15 | root | localhost | employees | Query | 19 | Waiting for table metadata lock | select * from slowtech.t1 |
| 16 | root | localhost | employees | Query | 10 | Waiting for table metadata lock | select * from slowtech.t1 |
| 17 | root | localhost | employees | Query | 0 | starting | show processlist |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)
如果發生線上上，無疑會影響到業務。所以，一般建議將DDL操作放到業務低峰期做，其實有兩方面的考慮，1. 避免對系統負載產生較大影響。2. 減少DDL被阻塞的概率。
MDL引入的背景
MDL是MySQL 5.5.3引入的，主要用於解決兩個問題，
RR事務隔離級別下不可重複讀的問題
如下所示，演示環境，MySQL 5.5.0。
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
rows in set (0.00 sec)
session2> alter table t1 add c1 int;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
session1> select * from t1;
Empty set (0.00 sec)
session1> commit;
Query OK, 0 rows affected (0.00 sec)
session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
rows in set (0.00 sec)
可以看到，雖然是RR隔離級別，但在開啟事務的情況下，第二次查詢卻沒有結果。
主從複製問題
包括主從資料不一致，主從複製中斷等。
如下面的主從資料不一致。
session1> create table t1(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)
session2> truncate table t1;
Query OK, 0 rows affected (0.46 sec)
session1> commit;
Query OK, 0 rows affected (0.35 sec)
session1> select * from t1;
Empty set (0.00 sec)
再來看看從庫的結果
session1> select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
row in set (0.00 sec)
看看binlog的內容，可以看到，truncate操作記錄在前，insert操作記錄在後。
# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;
# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;
# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1,'a')
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;
如果會話2執行的是drop table操作，還會導致主從中斷。
有意思的是，如果會話2執行的是alter table操作，其依舊會被阻塞，阻塞時間受innodb_lock_wait_timeout引數限制。
mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)
MDL的基本概念
首先，看看官方的說法，
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.
從上面的描述可以看到，
1. MDL出現的初衷就是為了保護一個處於事務中的表的結構不被修改。
2. 這裡提到的事務包括兩類，顯式事務和AC-NL-RO（auto-commit non-locking read-only）事務。顯式事務包括兩類：1. 關閉AutoCommit下的操作，2. 以begin或start transaction開始的操作。AC-NL-RO可理解為AutoCommit開啟下的select操作。
3. MDL是事務級別的，只有在事務結束後才會釋放。在此之前，其實也有類似的保護機制，只不過是語句級別的。
需要注意的是，MDL不僅僅適用於表，同樣也適用於其它物件，如下表所示，其中，"等待狀態"對應的是"show processlist"中的State。
為了提高資料庫的併發度，MDL被細分為了11種型別。
常用的有MDL_SHARED_READ,MDL_SHARE D_WRITE及MDL_EXCLUSIVE，其分別用於SELECT操作，DML操作及DDL操作。
其它型別的對應操作可參考原始碼sql/mdl.h。
對於MDL_EXCLUSIVE，官方的解釋是，
An exclusive metadata lock.
A connection holding this lock can modify both table's metadata and data.
No other type of metadata lock can be granted while this lock is held.
To be used for CREATE/DROP/RENAME TABLE statements and for execution of certain phases of other DDL statements.
簡而言之，MDL_EXCLUSIVE是獨佔鎖，在其持有期間是不允許其它型別的MDL被授予，自然也包括SELECT和DML操作。
這也就是為什麼DDL操作被阻塞時，後續其它操作也會被阻塞。
關於MDL的補充
1. MDL的最大等待時間由lock_wait_timeout引數決定，其預設值為31536000（365天）。在使用工具進行DDL操作時，這個值就不太合理。事實上，pt-online-schema-change和gh-ost對其就進行了相應的調整，其中，前者60s，後者3s。
2. 如果一個SQL語法上有效，但執行時報錯，如，列名不存在，其同樣會獲取MDL鎖，直到事務結束才釋放。
在上篇文章《MySQL表結構變更，不可不知的Metadata Lock》中，我們介紹了MDL引入的背景，及基本概念，從“道”的層面知道了什麼是MDL。下面就從“術”的層面看看如何定位MDL的相關問題。
在MySQL 5.7中，針對MDL，引入了一張新表performance_schema.metadata_locks，該表可對外展示MDL的相關資訊，包括其作用物件，型別及持有等待情況。
開啟MDL的instrument
但是相關instrument並沒有開啟（MySQL 8.0是預設開啟的），其可通過如下兩種方式開啟，
臨時生效
修改performance_schema.setup_instrume nts表，但例項重啟後，又會恢復為預設值。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效
在配置檔案中設定
[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
測試場景
下面結合一個簡單的Demo，來看看在MySQL 5.7中如何定位DDL操作的阻塞問題。
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> delete from slowtech.t1 where id=2;
Query OK, 1 row affected (0.00 sec)
session1> select * from slowtech.t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
row in set (0.00 sec)
session1> update slowtech.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2> alter table slowtech.t1 add c1 int; ##被阻塞
session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| 2 | root | localhost | NULL | Sleep | 51 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)
session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| object_type | object_schema | object_name | lock_type | lock_duration | lock_status | owner_thread_id |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| TABLE | slowtech | t1 | SHARED_WRITE | TRANSACTION | GRANTED | 27 |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 29 |
| SCHEMA | slowtech | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 29 |
| TABLE | slowtech | t1 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 29 |
| TABLE | slowtech | t1 | EXCLUSIVE | TRANSACTION | PENDING | 29 |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 28 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
rows in set (0.00 sec)
這裡，重點關注lock_status，"PENDING"代表執行緒在等待MDL，而"GRANTED"則代表執行緒持有MDL。
如何找出引起阻塞的會話
結合owner_thread_id，可以可到，是29號執行緒在等待27號執行緒的MDL，此時，可kill掉52號執行緒。
但需要注意的是，owner_thread_id給出的只是執行緒ID，並不是show processlist中的ID。如果要查詢執行緒對應的processlist id，需查詢performance_schema.threads表。
session3> select * from performance_schema.threads where thread_id in (27,29)\G
*************************** 1. row ***************************
THREAD_ID: 27
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 2
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 214
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 9800
*************************** 2. row ***************************
THREAD_ID: 29
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 4
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 172
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 9907
rows in set (0.00 sec)
將這兩張表結合，借鑑sys.innodb_lock _waits的輸出，實際上我們也可以直觀地呈現MDL的等待關係。
SELECT
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G
*************************** 1. row ***************************
locked_schema: slowtech
locked_table: t1
locked_type: Metadata Lock
waiting_processlist_id: 4
waiting_age: 259
waiting_query: alter table slowtech.t1 add c1 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 2
blocking_age: 301
blocking_query: NULL
sql_kill_blocking_connection: KILL 2
row in set (0.00 sec)
輸出一目瞭然，DDL操作如果要獲得MDL，執行kill 2即可。
官方的sys.schematablelock_waits
實際上，MySQL 5.7在sys庫中也整合了類似功能，同樣的場景，其輸出如下，
mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
object_schema: slowtech
object_name: t1
waiting_thread_id: 29
waiting_pid: 4
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table slowtech.t1 add c1 int
waiting_query_secs: 446
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 27
blocking_pid: 2
blocking_account: root@localhost
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
*************************** 2. row ***************************
object_schema: slowtech
object_name: t1
waiting_thread_id: 29
waiting_pid: 4
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table slowtech.t1 add c1 int
waiting_query_secs: 446
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 29
blocking_pid: 4
blocking_account: root@localhost
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
rows in set (0.00 sec)
具體分析下官方的輸出，
只有一個alter table操作，卻產生了兩條記錄，而且兩條記錄的kill物件竟然還不一樣，對錶結構不熟悉及不仔細看記錄內容的話，難免會kill錯物件。
不僅如此，如果有N個查詢被DDL操作堵塞，則會產生N*2條記錄。在阻塞操作較多的情況下，這N*2條記錄完全是個噪音。
而之前的SQL，無論有多少操作被阻塞，一個alter table操作，就只會輸出一條記錄。
如何檢視阻塞會話已經執行過的操作
但上面這個SQL也有遺憾，其blocking_query為NULL，而在會話1中，其明明已經執行了三個SQL。
這個與performance_schema.threads（類似於show processlist）有關，其只會輸出當前正在執行的SQL，對於已經執行過的，實際上是沒辦法看到。
但線上上，kill是一個需要謹慎的操作，畢竟你很難知道kill的是不是業務關鍵操作？又或者，是個批量update操作？那麼，有沒有辦法抓到該事務之前的操作呢？
答案，有。
即Performance Schema中記錄Statement Event（操作事件）的表，具體包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。
常用的是前面三個。
三者的表結構完全一致，其中，events_statements_history又包含了events_statements_current的操作，所以我們這裡會使用events_statements_history。
終極SQL如下，
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
locked_schema: slowtech
locked_table: t1
locked_type: Metadata Lock
waiting_processlist_id: 4
waiting_age: 294
waiting_query: alter table slowtech.t1 add c1 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 2
blocking_age: 336
blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
sql_kill_blocking_connection: KILL 2
row in set, 1 warning (0.00 sec)
從上面的輸出可以看到，blocking_query中包含了會話1中當前事務的所有操作，按執行的先後順序輸出。
需要注意的是，預設情況下，events_statements_history只會保留每個執行緒最近的10個操作，如果事務中進行的操作較多，實際上也是沒辦法抓全的。
Anyway, it is better than nothing！
在上一篇文章《MySQL 5.7中如何定位DDL被阻塞的問題》中，對於DDL被阻塞問題的定位，我們主要是基於MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法，頗有種"錦上添花"的意味，而且，也只適用於MySQL 5.7開始的版本。
但在實際生產中，MySQL 5.6還是佔絕不多數。雖然MySQL 8.0都已經GA了，但鑑於資料庫的特殊性，在對待升級的這個事情上，相當一部分人還是秉持著一種“不主動”的態度。
既然MySQL 5.6用者眾多，有沒有一種方法，來解決MySQL 5.6的這個痛點呢？
還是之前的測試Demo
會話1開啟了事務並執行了三個操作，但未提交，此時，會話2執行了alter table操作，被阻塞。
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> delete from slowtech.t1 where id=2;
Query OK, 1 row affected (0.00 sec)
session1> select * from slowtech.t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
row in set (0.00 sec)
session1> update slowtech.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2> alter table slowtech.t1 add c1 int; ##被阻塞
session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| 2 | root | localhost | NULL | Sleep | 51 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)
其實，導致DDL阻塞的操作，無非兩類：
1. 慢查詢
2. 表上有事務未提交
其中，第一類比較好定位，通過show processlist即能發現。而第二類基本沒法定位，因為未提交事務的連線在show processlist中的輸出同空閒連線一樣。
如下面Id為2的連線，雖然Command顯示為“Sleep”，其實是事務未提交。
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| 2 | root | localhost | NULL | Sleep | 77 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 4 | root | localhost | NULL | Query | 44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)
所以，網上有kill空閒（Command為Sleep）連線的說法，其實也不無道理，但這樣做就太簡單粗暴了，難免會誤殺。
其實，既然是事務，在information_schema. innodb_trx中肯定會有記錄，如會話1中的事務，在表中的記錄如下，
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 1050390
trx_state: RUNNING
trx_started: 2018-07-17 08:55:32
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
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: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
row in set (0.00 sec)
其中trx_mysql_thread_id是執行緒id，結合performance_schema.threads，可以知道當前哪些連線上存在著活躍事務，這樣就進一步縮小了可被kill的執行緒範圍。
但從影響程度上，和kill所有Command為Sleep的連線沒太大區別，畢竟，kill真正的空閒連線對業務的影響不大。
此時，依然可以藉助performance_schema. events_statements_history表。
在上篇MySQL 5.7的分析中，我們是首先知道引發阻塞的執行緒ID，然後利用events_statements_history表，檢視該執行緒的相關SQL。
而在MySQL 5.6中，我們並不知道引發阻塞的執行緒ID，但是，我們可以反其道而行之，利用窮舉法，首先統計出所有執行緒在當前事務執行過的所有SQL，然後再判斷這些SQL中是否包含目標表。
具體SQL如下，
SELECT
processlist_id,
sql_text
FROM
(
SELECT
c.processlist_id,
substring_index( sql_text, "transaction_begin;",-1 ) sql_text
FROM
information_schema.innodb_trx a,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY
thread_id
) b,
performance_schema.threads c
WHERE
a.trx_mysql_thread_id = c.processlist_id
AND b.thread_id = c.thread_id
) t
WHERE
sql_text LIKE '%t1%';
+----------------+---------------------------------------------------------------------------------------------------------+
| processlist_id | sql_text |
+----------------+---------------------------------------------------------------------------------------------------------+
| 2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |
+----------------+---------------------------------------------------------------------------------------------------------+
row in set (0.01 sec)
從輸出來看，確實也達到了預期效果。
需要注意的是，在MySQL5.6中，events_statements_history預設是沒有開啟的。
mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| statements_digest | YES |
+--------------------------------+---------+
rows in set (0.00 sec)
About Me
|
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2650567/，如需轉載，請註明出處，否則將追究法律責任。
