---
本文主要講述MySQL 5.7.29，也會加入和8的對比

公眾號 MySQL學習

---
###一、問題起源
我們在運維MySQL的過程中，肯定多多少少遇到過Innodb row lock的問題，如果線上上遇到我們可能會看到一大片的session處於堵塞狀態通常我們在show processlist中會看到如下：
- select for update語句處於sending data狀態
- update/delete語句處於updating狀態
- insert語句處於update狀態

那麼遇到這種問題如何快速的殺掉堵塞的會話呢，也許有人會說檢視sys.innodb_lock_waits即可得到要殺掉的會話，但是如果我們隨意模擬一下A,B,C,D 四個事務， B,C,D都同時等待A的鎖（A事務不提交），那麼檢視sys.innodb_lock_waits會看到如下的kill語句：
```
mysql> select sql_kill_blocking_connection from sys.innodb_lock_waits  ;
+------------------------------+
| sql_kill_blocking_connection |
+------------------------------+
| KILL 19                      |
| KILL 18                      |
| KILL 19                      |
| KILL 14                      |
| KILL 18                      |
| KILL 19                      |
+------------------------------+
6 rows in set (0.34 sec)
```
那麼到底殺哪一個會話才是事務A的會話呢？帶著這個問題我們進行分析，其中加入一些程式碼入口。

###二、sys.innodb_lock_waits的來源

實際上sys.innodb_lock_waits是一個檢視，5.7.29來源為如下：
```
SELECT r.trx_wait_started AS wait_started,
       TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
       TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
       rl.lock_table AS locked_table,
       rl.lock_index AS locked_index,
       rl.lock_type AS locked_type,
       r.trx_id AS waiting_trx_id,
       r.trx_started as waiting_trx_started,
       TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
       r.trx_rows_locked AS waiting_trx_rows_locked,
       r.trx_rows_modified AS waiting_trx_rows_modified,
       r.trx_mysql_thread_id AS waiting_pid,
       sys.format_statement(r.trx_query) AS waiting_query,
       rl.lock_id AS waiting_lock_id,
       rl.lock_mode AS waiting_lock_mode,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_pid,
       sys.format_statement(b.trx_query) AS blocking_query,
       bl.lock_id AS blocking_lock_id,
       bl.lock_mode AS blocking_lock_mode,
       b.trx_started AS blocking_trx_started,
       TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
       b.trx_rows_locked AS blocking_trx_rows_locked,
       b.trx_rows_modified AS blocking_trx_rows_modified,
       CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
       CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
  FROM information_schema.innodb_lock_waits w
       INNER JOIN information_schema.innodb_trx b    ON b.trx_id = w.blocking_trx_id
       INNER JOIN information_schema.innodb_trx r    ON r.trx_id = w.requesting_trx_id
       INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
       INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
 ORDER BY r.trx_wait_started;
```
可以看到它的來源實際上就是information_schema.innodb_lock_waits、information_schema.innodb_trx、information_schema.innodb_locks 3個表。那麼我們有必要搞清楚這3個表的資料到底包含了哪些事務的資訊。

###三、各自的資訊來源

- information_schema.innodb_trx：這個表的資料在每次進行查詢的時候進行裝載，主要包含了全部的讀寫事務和只讀事務的全部資訊來源為trx_sys.rw_trx_list和trx_sys.mysql_trx_list。在函式fetch_data_into_cache可以看到它的呼叫方式。
- information_schema.innodb_locks：這個表的資料在每次進行查詢的時候進行裝載，主要包含了當前處於等待狀態事務的等待某個row lock資源佇列上所有request事務和blocking事務，內部有一個去重操作位於add_lock_to_cache函式中。但是在8中這裡叫做performance_schema.data_locks，包含的資訊不同，其會包含所有的已經獲取和需要獲取的全部rec lock資訊，類似show engine innodb status出來的資訊。
- information_schema.innodb_lock_waits：這個表的資料在每次進行查詢的進行裝載，主要包含了每個處於等待狀態事務中關於這個row lock資源佇列的所有事務資訊。在8中叫做performance_schema.data_lock_waits，其基本資訊和5.7沒有太大差別。

實際上這個等待佇列實際上存在於row lock的hash查詢表中，查詢的時候才會根據其取出每個事務的等待鎖資源的佇列資訊。其內部為一個迭代器lock_queue_iterator_reset(&iter, trx->lock.wait_lock, ULINT_UNDEFINED)，這個迭代器用於迭代row lock  hash查詢表中的佇列資訊，其迭代的開始就是當前處於等待狀態事務的鎖資源（request事務的row lock）位於trx->lock.wait_lock中，這個過程位於add_trx_relevant_locks_to_cache函式中。
其次在5.7.29中這3個資訊通常是同時裝載的，他們共同位於函式fetch_data_into_cache下面，有如下
```
fetch_data_into_cache
  -> fetch_data_into_cache_low(cache, true, &trx_sys->rw_trx_list) 
     本處迴圈每一個讀寫事務裝載資料
      ->add_trx_relevant_locks_to_cache
         本處迴圈迭代整個等待佇列進行轉載資料，包含information_schema.innodb_locks和information_schema.innodb_lock_waits
     將讀寫事務資訊裝載入information_schema.innodb_trx中
  -> fetch_data_into_cache_low(cache, false, &trx_sys->mysql_trx_list)
     本處迴圈每一個事務裝載資料，並且會跳過已經裝載的讀寫事務
     將只讀事務資訊裝載入information_schema.innodb_trx中
```

###四、用一個圖來進行說明
![未命名檔案 (14).png](https://upload-images.jianshu.io/upload_images/7398834-0e993239d13e9786.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

假設我們這裡有5個讀寫事務且都沒有提交，是A,B,C,D,E, 其中A事務獲取了某行的鎖資源X，其他B,C,D都在等待，而事務E單獨自己獲取了一個行鎖資源Y。那麼我們分別對應一下三個表中的資料如下：

- information_schema.innodb_trx：包含了A,B,C,D,E這5個事務的資訊。當然這裡沒有隻讀事務，如果有隻讀事務也會包含在其中，程式碼中我可以看一下主要透過（!rw_trx_list && trx->id != 0）來進行只讀事務的判斷，及透過是否在讀寫佇列和事務ID是否為0進行判定（因為只讀事務沒有分配事務ID為0） 
- information_schema.innodb_locks：包含了圖中紅色部分的全部事務，因此包含了A,B,C,D這4個事務，但是由於會去重，因此他們只會出現一次。這個表的資訊5.7和8有所不同上面已經描述
- information_schema.innodb_lock_waits：包含了圖中紅色部分的全部事務，並且有詳細的佇列資訊如下，這表5.7和8沒有太大差距。

|事務D|事務C|事務B|
|-|-|-|
| waitting 事務D->blocking 事務C|waitting 事務C->blocking 事務B|waitting 事務B->blocking 事務A|
|waitting 事務D->blocking 事務B|waitting 事務C->blocking 事務A||
|waitting 事務D->blocking 事務A|||

那麼就會出現6行資訊，這一點5.7和8的行數是一樣的。5.7.29如下：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-be7d0380997a5cb5.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

8.0.18如下：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-79260214c39d53f0.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

這裡我們需要注意了，既然如此那麼我們可以發現information_schema.innodb_lock_waits的blocking_trx_id中出現次數最多的事務ID很可能就是堵塞的源頭，而sys.innodb_lock_waits中的資訊正是完全來自information_schema.innodb_lock_waits和其他兩個表的join，同樣也是6行如下：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-820672c2def650f5.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
只不過透過連線找到了blocking事務的process id而已。

###五、如何快速殺掉可能的堵塞源頭

既然有了前面的分析就很簡單了，我們可以透過如下方法（5.7/8.0通用）：

######1. 執行語句找出堵塞最多的session
```
select trim(LEADING 'KILL ' from sql_kill_blocking_connection),count(*)
from sys.innodb_lock_waits  
group by trim(LEADING 'KILL ' from sql_kill_blocking_connection) order by count(*) desc;

+---------------------------------------------------------+----------+
| trim(LEADING 'KILL ' from sql_kill_blocking_connection) | count(*) |
+---------------------------------------------------------+----------+
| 407                                                     |       12 |
| 408                                                     |       11 |
| 409                                                     |       10 |
| 410                                                     |        9 |
| 411                                                     |        8 |
| 412                                                     |        7 |
| 413                                                     |        6 |
| 414                                                     |        5 |
| 415                                                     |        4 |
| 416                                                     |        3 |
| 417                                                     |        2 |
| 418                                                     |        1 |
+---------------------------------------------------------+----------+
```
排名第一個的說明堵塞的會話越多。

######2、找到process id  407當前的事務資訊
觀察其事務狀態和可能執行的語句或者上一條語句判斷是否可以殺掉。

```
select trx_id,trx_operation_state,trx_mysql_thread_id prs_id,now(),trx_started,
to_seconds(now())-to_seconds(trx_started) trx_es_time,
user,db,host,state,Time,info current_sql,PROCESSLIST_INFO last_sql
from information_schema.innodb_trx t1,information_schema.processlist t2,performance_schema.threads  t3
where t1.trx_mysql_thread_id=t2.id 
and   t1.trx_mysql_thread_id=t3.PROCESSLIST_ID
and   t1.trx_mysql_thread_id!=connection_id()
and   t2.id=407;
```
當然也可以在殺掉session之前，儲存一份show engine innodb status資訊用於後期分析所用。

######3、迴圈這個過程，因為堵塞的row lock資源可能不止一個

MySQL:Innodb如何快速殺掉堵塞會話的思考

相關文章