簡單介紹MySQL列印死鎖日誌的方法

導讀	本文主要介紹了MySQL列印死鎖日誌的方法步驟，文中透過示例程式碼介紹的非常詳細，具有一定的參考價值，感興趣的小夥伴們可以參考一下

在 MySQL 運維過程中，難免會遇到 MySQL 死鎖的情況，一旦線上業務日漸複雜，各種業務操作之間往往會產生鎖衝突，有些會導致死鎖異常。這種死鎖異常一般要在特定時間特定資料和特定業務操作才會復現，有時候處理起來毫無頭緒，一般只能從死鎖日誌下手。本篇文章我們一起來看下 MySQL 的死鎖日誌。

當業務發生死鎖時，首先是線上錯誤日誌報警發現死鎖異常，也會提示一些堆疊資訊，然後會反饋到資料庫層面進行排查。我們一般會在 行執行 show engine innodb status\G 來輸出死鎖日誌，\G 的作用是將查詢到的結果，每行顯示一個欄位和欄位值，方便檢視。

show engine innodb status 是 MySQL 提供的一個用於檢視 innodb 引擎系統資訊的工具。它會輸出大量的內部資訊，內容分為很多小段，每一段對應 innodb 儲存引擎不同部分的資訊，其中 LATEST DETECTED DEADLOCK 部分顯示的最近一次的死鎖資訊。

下面我們手動製造一次死鎖，來看一下死鎖日誌相關資訊：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-10 17:03:10 0x7fb040672700
*** (1) TRANSACTION:
TRANSACTION 46913, ACTIVE 142 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating
update test_tb set stu_name = 'lisi' where stu_id = 1006
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ee; asc     ;;
 1: len 4; hex 80000006; asc     ;;
 
*** (2) TRANSACTION:
TRANSACTION 46914, ACTIVE 103 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating
update test_tb set age = 21  where stu_id = 1005
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ee; asc     ;;
 1: len 4; hex 80000006; asc     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ed; asc     ;;
 1: len 4; hex 80000005; asc     ;;
 
*** WE ROLL BACK TRANSACTION (2)
 
# 以上為原文 下面增加個人分析
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-10 17:03:10 0x7fb040672700 #這裡顯示了最近一次發生死鎖的日期和時間
*** (1) TRANSACTION: #死鎖相關的第一個事務
TRANSACTION 46913, ACTIVE 142 sec starting index read
#這行表示事務id為46913，事務處於活躍狀態142s，starting index read表示正在使用索引讀取資料行
mysql tables in use 1, locked 1
#這行表示該事務正在使用1個表，且涉及鎖的表有1個
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
#這行表示在等待4把鎖，佔用記憶體1136位元組，涉及3行記錄
MySQL thread id 2997198, OS thread handle 140394973071104, query id 9145673 localhost root updating
#這行表示該事務的執行緒ID資訊，作業系統控制程式碼資訊，連線來源、使用者
update test_tb set stu_name = 'lisi' where stu_id = 1006
#這行表示事務執行的最後一條SQL資訊
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #事務1想要獲取的鎖
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46913 lock_mode X locks rec but not gap waiting
#這行資訊表示等待的鎖是一個record lock，空間id是224，頁編號為4，大概位置在頁的80位處，鎖發生在表testdb.test_tb的uk_stu_id索引上，是一個X鎖，但是不是gap lock，waiting表示正在等待鎖
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ee; asc     ;;
 1: len 4; hex 80000006; asc     ;;
 
*** (2) TRANSACTION: #死鎖相關的第一個事務
TRANSACTION 46914, ACTIVE 103 sec starting index read
#這行表示事務2的id為46914，事務處於活躍狀態103s
mysql tables in use 1, locked 1
#正在使用1個表，涉及鎖的表有1個
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
#涉及4把鎖，3行記錄
MySQL thread id 2997201, OS thread handle 140394971473664, query id 9145681 localhost root updating
#事務2的執行緒ID資訊，作業系統控制程式碼資訊，連線來源、使用者
update test_tb set age = 21  where stu_id = 1005
#第二個事務的SQL
*** (2) HOLDS THE LOCK(S): # 事務2持有的鎖 正是事務1想要獲取的鎖
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ee; asc     ;;
 1: len 4; hex 80000006; asc     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 4 n bits 80 index uk_stu_id of table `testdb`.`test_tb` trx id 46914 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800003ed; asc     ;;
 1: len 4; hex 80000005; asc     ;;
#上面這部分是事務二正在等待的鎖，從資訊上看，等待的是同一個表，同一個索引，同一個page上的record lock X鎖，但是heap no位置不同，即不同的行上的鎖
 
*** WE ROLL BACK TRANSACTION (2) #表示事務2被回滾

從死鎖日誌中可以看到關聯的兩個事務相關資訊，當一個事務持有了其他事務需要的鎖，同時又想獲得其他事務持有的鎖時，等待關係上就會產生迴圈，Innodb 不會顯示所有持有和等待的鎖，但死鎖日誌也顯示了相關的資訊來幫你確定，排查死鎖發生的索引，這對於你確定能否避免死鎖有較大的價值。

從上面內容我們知道 MySQL 的死鎖可以透過 show engine innodb status 來檢視，但是這個 需要手動執行並且只能顯示最新的一條死鎖，該方式無法完全捕獲到系統發生的死鎖資訊。那有沒有辦法記錄所有的死鎖日誌呢，我們來看下 MySQL 的系統引數。

MySQL 系統內部提供一個 innodb_print_all_deadlocks 引數，該引數預設是關閉的，開啟後可以將死鎖資訊自動記錄到 MySQL 的錯誤日誌中。下面我們來看下這個引數的作用：

# 檢視引數是否開啟
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
 
# 開啟innodb_print_all_deadlocks,此引數是全域性引數，可以動態調整。記得要加入到配置檔案中
mysql> set global innodb_print_all_deadlocks = 1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+

建議將 innodb_print_all_deadlocks 引數設定為 1 ，這樣每次發生死鎖後，系統會自動將死鎖資訊輸出到錯誤日誌中，需要注意的是開啟此引數後，只會記錄死鎖部分資訊而不會記錄 innodb 其他相關資訊，即只會記錄 show engine innodb status 中的 LATEST DETECTED DEADLOCK 部分。

其實 InnoDB 儲存引擎還提供有 InnoDB Monitor 監視器，可以定期將 InnoDB 的狀態資訊輸出到錯誤日誌中，主要由 innodb_status_output 和 innodb_status_output_locks 引數控制，這兩個系統變數是用來啟用標準 InnoDB 監控和 InnoDB 鎖監控的，開啟後會將監控結果輸出錯誤日誌中，大約每隔 15 秒產生一次輸出，輸出內容與 show engine innodb status 一致。不過這會導致錯誤日誌暴增，一般不建議開啟這兩個引數。

本篇文章介紹了 MySQL 死鎖日誌的獲取方法，發生死鎖後，可以根據死鎖日誌還獲取相關資訊。開啟 innodb_print_all_deadlocks 引數可以自動將死鎖資訊輸出到錯誤日誌中，有助於我們及時發現並處理死鎖異常。

原文來自：