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

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

一、問題起源

我們在運維MySQL的過程中，肯定多多少少遇到過Innodb row lock的問題，如果在線上遇到我們可能會看到一大片的session處于堵塞狀態(tài)通常我們在show processlist中會看到如下：

  • select for update語句處于sending data狀態(tài)
  • update/delete語句處于updating狀態(tài)
  • insert語句處于update狀態(tài)

那么遇到這種問題如何快速的殺掉堵塞的會話呢，也許有人會說查看sys.innodb_lock_waits即可得到要殺掉的會話，但是如果我們隨意模擬一下A,B,C,D 四個事務(wù)， B,C,D都同時等待A的鎖（A事務(wù)不提交），那么查看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)

那么到底殺哪一個會話才是事務(wù)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個表的數(shù)據(jù)到底包含了哪些事務(wù)的信息。

三、各自的信息來源

  • information_schema.innodb_trx：這個表的數(shù)據(jù)在每次進行查詢的時候進行裝載，主要包含了全部的讀寫事務(wù)和只讀事務(wù)的全部信息來源為trx_sys.rw_trx_list和trx_sys.mysql_trx_list。在函數(shù)fetch_data_into_cache可以看到它的調(diào)用方式。
  • information_schema.innodb_locks：這個表的數(shù)據(jù)在每次進行查詢的時候進行裝載，主要包含了當(dāng)前處于等待狀態(tài)事務(wù)的等待某個row lock資源隊列上所有request事務(wù)和blocking事務(wù)，內(nèi)部有一個去重操作位于add_lock_to_cache函數(shù)中。但是在8中這里叫做performance_schema.data_locks，包含的信息不同，其會包含所有的已經(jīng)獲取和需要獲取的全部rec lock信息，類似show engine innodb status出來的信息。
  • information_schema.innodb_lock_waits：這個表的數(shù)據(jù)在每次進行查詢的進行裝載，主要包含了每個處于等待狀態(tài)事務(wù)中關(guān)于這個row lock資源隊列的所有事務(wù)信息。在8中叫做performance_schema.data_lock_waits，其基本信息和5.7沒有太大差別。

實際上這個等待隊列實際上存在于row lock的hash查找表中，查詢的時候才會根據(jù)其取出每個事務(wù)的等待鎖資源的隊列信息。其內(nèi)部為一個迭代器lock_queue_iterator_reset(&iter, trx->lock.wait_lock, ULINT_UNDEFINED)，這個迭代器用于迭代row lock hash查找表中的隊列信息，其迭代的開始就是當(dāng)前處于等待狀態(tài)事務(wù)的鎖資源（request事務(wù)的row lock）位于trx->lock.wait_lock中，這個過程位于add_trx_relevant_locks_to_cache函數(shù)中。
其次在5.7.29中這3個信息通常是同時裝載的，他們共同位于函數(shù)fetch_data_into_cache下面，有如下

fetch_data_into_cache
  -> fetch_data_into_cache_low(cache, true, &trx_sys->rw_trx_list) 
     本處循環(huán)每一個讀寫事務(wù)裝載數(shù)據(jù)
      ->add_trx_relevant_locks_to_cache
         本處循環(huán)迭代整個等待隊列進行轉(zhuǎn)載數(shù)據(jù)，包含information_schema.innodb_locks和information_schema.innodb_lock_waits
     將讀寫事務(wù)信息裝載入information_schema.innodb_trx中
  -> fetch_data_into_cache_low(cache, false, &trx_sys->mysql_trx_list)
     本處循環(huán)每一個事務(wù)裝載數(shù)據(jù)，并且會跳過已經(jīng)裝載的讀寫事務(wù)
     將只讀事務(wù)信息裝載入information_schema.innodb_trx中

四、用一個圖來進行說明

未命名文件 (14).png

假設(shè)我們這里有5個讀寫事務(wù)且都沒有提交，是A,B,C,D,E, 其中A事務(wù)獲取了某行的鎖資源X，其他B,C,D都在等待，而事務(wù)E單獨自己獲取了一個行鎖資源Y。那么我們分別對應(yīng)一下三個表中的數(shù)據(jù)如下：

  • information_schema.innodb_trx：包含了A,B,C,D,E這5個事務(wù)的信息。當(dāng)然這里沒有只讀事務(wù)，如果有只讀事務(wù)也會包含在其中，代碼中我可以看一下主要通過（!rw_trx_list && trx->id != 0）來進行只讀事務(wù)的判斷，及通過是否在讀寫隊列和事務(wù)ID是否為0進行判定（因為只讀事務(wù)沒有分配事務(wù)ID為0）
  • information_schema.innodb_locks：包含了圖中紅色部分的全部事務(wù)，因此包含了A,B,C,D這4個事務(wù)，但是由于會去重，因此他們只會出現(xiàn)一次。這個表的信息5.7和8有所不同上面已經(jīng)描述
  • information_schema.innodb_lock_waits：包含了圖中紅色部分的全部事務(wù)，并且有詳細(xì)的隊列信息如下，這表5.7和8沒有太大差距。
事務(wù)D 事務(wù)C 事務(wù)B
waitting 事務(wù)D->blocking 事務(wù)C waitting 事務(wù)C->blocking 事務(wù)B waitting 事務(wù)B->blocking 事務(wù)A
waitting 事務(wù)D->blocking 事務(wù)B waitting 事務(wù)C->blocking 事務(wù)A
waitting 事務(wù)D->blocking 事務(wù)A

那么就會出現(xiàn)6行信息，這一點5.7和8的行數(shù)是一樣的。5.7.29如下：


image.png

8.0.18如下：


image.png

這里我們需要注意了，既然如此那么我們可以發(fā)現(xiàn)information_schema.innodb_lock_waits的blocking_trx_id中出現(xiàn)次數(shù)最多的事務(wù)ID很可能就是堵塞的源頭，而sys.innodb_lock_waits中的信息正是完全來自information_schema.innodb_lock_waits和其他兩個表的join，同樣也是6行如下：


image.png

只不過通過連接找到了blocking事務(wù)的process id而已。

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

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

1. 執(zhí)行語句找出堵塞最多的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當(dāng)前的事務(wù)信息

觀察其事務(wù)狀態(tài)和可能執(zhí)行的語句或者上一條語句判斷是否可以殺掉。

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;

當(dāng)然也可以在殺掉session之前，保存一份show engine innodb status信息用于后期分析所用。

3、循環(huán)這個過程，因為堵塞的row lock資源可能不止一個
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容