在MySQL資料庫中出現了阻塞問題，如何快速查找定位問題根源？在實驗開始前，我們先梳理一下有什麼工具或命令查看MySQL的阻塞，另外，我們也要一一對比其優劣，因為有些命令可能在實際環境下可能並不適用。 1： show engine innodb status 2： Innotop工具 3： INNO... ...
在MySQL資料庫中出現了阻塞問題，如何快速查找定位問題根源？在實驗開始前，我們先梳理一下有什麼工具或命令查看MySQL的阻塞，另外，我們也要一一對比其優劣，因為有些命令可能在實際環境下可能並不適用。
1： show engine innodb status
2： Innotop工具
3： INNODB_TRX 等系統表
下麵我們理論聯繫實際，通過實驗來測試總結一下這個問題。首先構造測試環境，資料庫測試環境為（ 5.7.21 MySQL Community Server 和5.6.20-enterprise-commercial，這兩個測試環境我都測試驗證過）
mysql> use MyDB;Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changedmysql> create table test_blocking(id int primary key, name varchar(12));
Query OK, 0 rows affected (0.05 sec)mysql> insert into test_blocking-> select 1, 'kerry' from dual;
Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test_blocking-> select 2, 'jimmy' from dual;
Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test_blocking-> select 3, 'kkk' from dual;
Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0
準備好測試環境數據後，那麼我們接下來開始實驗，為了實驗效果，我們先將參數innodb_lock_wait_timeout設置為100。
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_lock_wait_timeout=100 ;
Query OK, 0 rows affected (0.00 sec)mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> select * from test_blocking where id=1 for update;
+----+-------+
| id | name |
+----+-------+
| 1 | kerry |
+----+-------+
1 row in set (0.00 sec)
然後在第二個連接會話中執行更新腳本，構造被阻塞的案例
mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
| 9 |
+-----------------+
1 row in set (0.00 sec)
mysql> update test_blocking set name='kk' where id=1;
在第三個連接會話執行下麵命令，查看TRANSACTIONS相關信息：
mysql> show engine innodb status\G;
使用show engine innodb status命令後，可以查看其輸出的TRANSACTIONS部分信息，如上截圖所示，找到類似TRX HAS BEEN WATING ...部分的信息，
通過那部分信息，我們可以看到update test_blocking set name='kk' where id=1這個SQL語句被阻塞了14秒，一直在等待獲取X Lock。
TRANSACTIONS
------------
Trx id counter 148281 #下一個事務ID
Purge done for trx's n:o < 148273 undo n:o < 0 state: running but idle
History list length 552
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 15, OS thread handle 0x4cc64940, query id 261 localhost root cleaning up
---TRANSACTION 0, not started
MySQL thread id 14, OS thread handle 0x4cbe2940, query id 278 localhost root init
show engine innodb status
---TRANSACTION 148280, ACTIVE 24 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x4cba1940, query id 276 localhost root cleaning up
---TRANSACTION 148279, ACTIVE 313 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 9, OS thread handle 0x4cc23940, query id 277 localhost root updating #線程ID為9， 操作系統線程句柄為0x4cc23940， 查詢ID為277，賬號為root的UPDATE操作
update test_blocking set name='kk' where id=1 #具體SQL語句
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED: #TRX等待授予鎖已經有14秒了
RECORD LOCKS space id 337 page no 3 n bits 72 index `PRIMARY` of table `MyDB`.`test_blocking` trx id 148279 lock_mode X locks rec but not gap waiting
#在space id=337（test_blocking表的表空間），page no=3的頁上，表test_blocking上的主鍵索引在等待X鎖
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;; #第一個欄位是主鍵，制度按長為4，值為1
1: len 6; hex 000000024322; asc C";; #該欄位為6個位元組的事務id，這個id表示最近一次被更新的事務id（對應十進位為148258）
