引言

MySQL死鎖是線上經(jīng)常遇到的現(xiàn)象，但是死鎖分析卻并不總是件容易的事情，MySQL死鎖日志分析方法有助于研發(fā)快速提取信息,提高分析效率,通過了解死鎖觸發(fā)條件、檢測機(jī)制及鎖類型,結(jié)合日志分析工具,可以更有效地解決死鎖問題。本文介紹 MySQL 死鎖日志的分析方法，幫助研發(fā)從日志中快速提取有效信息，從而提高死鎖原因分析的效率。

死鎖介紹

觸發(fā)條件

死鎖的觸發(fā)條件包括四個：

•互斥

•占有且等待

•不可搶占用

•循環(huán)等待

如下圖所示，兩個事務(wù)加鎖順序不同導(dǎo)致死鎖。

發(fā)生死鎖后只需要破壞發(fā)生死鎖四個條件中的任意一個條件就可以解除死鎖狀態(tài)。數(shù)據(jù)庫層面有兩種策略用于打破死鎖狀態(tài)：

•被動，設(shè)置事務(wù)等待鎖的超時時間，事務(wù)鎖等待超時后自動回滾。默認(rèn) 50 秒；

•主動，開啟主動死鎖檢測，檢測到死鎖后回滾其中一個事務(wù)。默認(rèn)開啟。

其中默認(rèn)使用第二種策略，也就是檢測到死鎖后立即回滾，從而解除死鎖狀態(tài)。因此發(fā)生死鎖時業(yè)務(wù)可能報錯死鎖，但不會報錯鎖等待超時。

死鎖檢測

innodb\_deadlock\_detect 參數(shù)用于控制是否開啟死鎖檢測，該參數(shù)是 5.7.15 中引入。

mysql>select@@innodb_deadlock_detect;
+--------------------------+
|@@innodb_deadlock_detect|
+--------------------------+
|                        1|
+--------------------------+
1rowinset(0.00 sec)

死鎖檢測本質(zhì)上是一個搜索問題，5.7 中使用深度優(yōu)先算法實現(xiàn)，具體是判斷鎖等待關(guān)系圖中是否有環(huán)。

高并發(fā)場景下可以考慮關(guān)閉死鎖檢測，原因是如果鎖等待隊列很長，死鎖檢測成本高，會導(dǎo)致實例性能下降。但是前提是應(yīng)用層面可以避免死鎖，因此通常不建議關(guān)閉。

下面通過介紹一個死鎖案例對死鎖日志的格式與分析方法有一個感性認(rèn)識。

死鎖案例

日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-04-14 08:07:05 0x7fb6d39a6700
*** (1) TRANSACTION:
TRANSACTION 13020605130, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 33 lock struct(s), heap size 3520, 33 row lock(s), undo log entries 34
MySQL thread id 2343498932, OS thread handle 140424015394560, query id 28769967039 x.x.x.x xwms_rw updating
UPDATE stock_occupy
        SET update_time = NOW()
        ,update_user = 'WAPS'
        ,qty_out_occupy=qty_out_occupy + 12.0000
        WHERE map_area_id = 608
        AND goods_no='EMG4418433215231'
        AND owner_no='0'
        AND lot_no='-1'
        AND product_level='100'         
            AND org_no = '10'
            AND distribute_no = '10'
            AND warehouse_no = '126'
            AND map_area_id = 608
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 127 page no 5255 n bits 272 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020605130 lock_mode X locks rec but not gap waiting
Record lock, heap no 53 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343333323135323331; asc EMG4418433215231;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000042de4; asc       - ;;

*** (2) TRANSACTION:
TRANSACTION 13020606128, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 8
MySQL thread id 2343006037, OS thread handle 140423210886912, query id 28769967052 x.x.x.x xwms_rw updating
UPDATE stock_occupy
        SET update_time = NOW()
        ,update_user = 'WAPS'
        ,qty_out_occupy=qty_out_occupy + 11.0000
        WHERE map_area_id = 608
        AND goods_no='EMG4418442253742'
        AND owner_no='0'
        AND lot_no='-1'
        AND product_level='100'
            AND org_no = '10'
            AND distribute_no = '10'
            AND warehouse_no = '126'
            AND map_area_id = 608
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 127 page no 5255 n bits 272 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020606128 lock_mode X locks rec but not gap
Record lock, heap no 53 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343333323135323331; asc EMG4418433215231;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000042de4; asc       - ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 127 page no 5276 n bits 240 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020606128 lock_mode X locks rec but not gap waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343432323533373432; asc EMG4418442253742;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000044335; asc       C5;;

*** WE ROLL BACK TRANSACTION (2)

其中：

•加鎖索引相同，都是二級索引；

•兩個事務(wù)中三個鎖對應(yīng)兩個主鍵，包括 8000000000044335（279349）/ 8000000000042de4（273892）；

•binlog 中顯示提交事務(wù)也就是事務(wù) 1 中先后 update 279349 與 273892，因此判斷死鎖原因是交叉更新。

表結(jié)構(gòu)

`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'自增id',
  `map_area_id`bigint(20)NOTNULLCOMMENT'地圖區(qū)域ID',
  `goods_no`varchar(50)NOTNULLCOMMENT'商品編號',
  `product_level`varchar(50)NOTNULLCOMMENT'商品等級',
  `lot_no`varchar(50)NOTNULLCOMMENT'批次號',
  `owner_no`varchar(50)NOTNULLCOMMENT'貨主編號',
  PRIMARYKEY(`id`),
  UNIQUEKEY`idx_map_goods_product_lot_owner`(`map_area_id`,`goods_no`,`product_level`,`lot_no`,`owner_no`)

其中：

•加鎖索引是二級聯(lián)合唯一索引；

•update 根據(jù)二級唯一索引更新非索引字段，因此執(zhí)行時具體原地更新主鍵索引，二級索引不變，且加鎖類型是 X 型 record lock；

•綜合以上信息，判斷死鎖原因是兩個事務(wù)交叉更新同一張表的兩行數(shù)據(jù)導(dǎo)致死鎖。

下面介紹如何從死鎖日志中獲取有效信息，并分析其中最重要的信息-鎖，包括鎖的類型、不同類型鎖的兼容性、常見加鎖規(guī)則。

死鎖分析方法

日志格式

簡化后的死鎖日志格式如下所示。

InnoDB:***(1)TRANSACTION:
InnoDB:***(1) WAITING FOR THIS LOCKTO BE GRANTED:
InnoDB:***(2)TRANSACTION:
InnoDB:***(2) HOLDS THE LOCK(S):
InnoDB:***(2) WAITING FOR THIS LOCKTO BE GRANTED:
InnoDB:*** WE ROLL BACK TRANSACTION(1)

其中主要信息包括：

•兩個事務(wù)

•兩條 SQL

•三部分鎖信息

其中存在的問題包括：

•兩個事務(wù)有等鎖 SQL，沒有可能存在的持鎖 SQL；

•事務(wù) 1 缺少持鎖類型，8.0 中已提供；

•SQL 超長時自動截斷；

•加鎖行數(shù)據(jù)是十六進(jìn)制，因此需要根據(jù)字段的數(shù)據(jù)類型轉(zhuǎn)換成對應(yīng)格式，比如十進(jìn)制或字符串。

其中前兩種信息的缺失直接導(dǎo)致死鎖分析的難度增大，因此死鎖原因分析通常需要反推來處理，也就是從等鎖類型判斷持鎖類型。

缺少部分可以參考以下分析方法：

•binlog，可以獲取提交事務(wù)中已執(zhí)行的 SQL 以及可能存在的更新前的記錄；

•general log，可以獲取提交事務(wù)與回滾事務(wù)中已執(zhí)行的 SQL，包括已執(zhí)行無更新的操作，比如刪除不存在的記錄。

鎖信息

MySQL 中鎖的粒度包括實例、表、行，其中后兩種都可能導(dǎo)致死鎖，本文假設(shè)都是行粒度，也就是行鎖。

注意行鎖是給表的索引的記錄加鎖，且是給訪問過的對象加鎖。

死鎖日志中與鎖相關(guān)的信息包括：

•鎖所屬表，比如分區(qū)表與非分區(qū)表的加鎖規(guī)則不同；

•鎖所屬索引，比如唯一鍵與非唯一鍵的加鎖規(guī)則不同；

•鎖類型，其中不同類型鎖的兼容性不同；

•鎖定數(shù)據(jù)行，其中：

?不同行的加鎖類型可能不同，比如右邊界記錄（supremum pseudo-record）的 next-key lock 無法退化；

?數(shù)據(jù)行是否標(biāo)記刪除可能影響到后續(xù)加鎖，一個字節(jié)中的第六位表示是否標(biāo)記刪除（info bits），因此十進(jìn)制 32 表示標(biāo)記刪除。比如二級唯一索引的唯一性檢查時如果發(fā)現(xiàn)沖突行已標(biāo)記刪除，將循環(huán)給下一行加鎖直到數(shù)據(jù)不沖突。

當(dāng)然也有其他因素影響加鎖的類型，主要包括：

•數(shù)據(jù)庫版本，比如 5.7.26 中針對 replace / insert duplicate 語句的加鎖進(jìn)行優(yōu)化，唯一鍵不沖突時不加間隙鎖；

•事務(wù)隔離級別，比如 RC 中沒有間隙鎖；

這些信息都可以認(rèn)為是死鎖案例的特征，其中鎖類型是最重要的特征。

鎖類型

鎖類型（type\_mode）主要包括以下三部分信息：

•lock\_mode，表示鎖的模式，包括 IS、IX、S、X、AUTO\_INC；

•lock\_type，表示鎖的粒度，包括 RECORD 與 TABLE，對應(yīng)行鎖與表鎖；

•rec\_lock\_type，表示行鎖的類型，包括 record lock、gap lock、next-key lock、insert intention lock。其中：

?gap lock 是事務(wù)隔離級別 RR 中為解決幻讀引入的鎖類型；

?insert intention lock 是一種特殊的 gap lock，表示插入的意向，用于在插入操作存在 gap lock 時表示等待狀態(tài)。

比如死鎖日志中鎖類型顯示 lock\_mode X locks rec but not gap waiting，其中：

•lock\_mode = X

•lock\_type = RECORD

•rec\_lock\_type = record lock

•lock\_status = WAITING

注意鎖的狀態(tài)分兩種，包括已獲取到（GRANTED）與等待中（WAITING）。

死鎖由兩組鎖等待組成，鎖等待發(fā)生在鎖沖突時，鎖沖突根據(jù)鎖兼容矩陣判斷，下面介紹鎖兼容矩陣。

鎖兼容矩陣

不同類型行鎖的兼容性見下表，其中第一行表示已有的鎖，第一列表示要加的鎖，? 表示鎖沖突。

其中：

•insert intention 不影響其他事務(wù)加任何類型的鎖；

•gap lock 只和 insert intention 沖突，用于防止其他事務(wù)在間隙中插入記錄導(dǎo)致幻讀，與其他鎖不沖突；

•如果已有的鎖是等待狀態(tài)，要加的鎖與該鎖沖突，要加的鎖同樣會發(fā)生鎖等待。

常見加鎖規(guī)則

加鎖場景：

•查詢（數(shù)據(jù)定位），不是 MVCC，加鎖讀，包括回表加鎖；

•更新，下面是部分場景與對應(yīng)加鎖類型：

?為防止臟寫，record lock；

?為防止幻讀，gap lock；

?為防止唯一鍵沖突，next-key lock。

加鎖類型：

•顯式鎖；

•隱式鎖，比如 insert、update、delete 語句在沒有鎖沖突時不加顯式鎖，必要時轉(zhuǎn)換成顯式鎖。

加鎖的單位是 next-key lock，部分場景下會發(fā)生退化，其中：

•退化為 record lock：

?唯一索引上的等值查詢；

•退化為 gap lock：

?非唯一索引的等值查詢向右遍歷到第一個不滿足等值條件的記錄；

不退化的場景：

•supremum pseudo-record；

•insert duplicate / replace 語句中根據(jù)唯一鍵定位數(shù)據(jù)；

•分區(qū)表，低于 5.7.23 版本中存在一個 bug，具體是唯一索引的等值查詢遍歷到第一個不滿足等值條件的記錄時加鎖 next-key lock。

下面介紹一種高頻鎖沖突，那就是插入時唯一鍵沖突加鎖，注意加鎖類型與事務(wù)隔離級別無關(guān)，這也是少見的 RC 中使用 gap lock 的場景。

當(dāng)事務(wù)與未提交事務(wù)的唯一鍵沖突時：

•未提交事務(wù)，如果存在隱式鎖，將其轉(zhuǎn)換成顯式鎖，具體類型是 X 型 record lock；

•沖突事務(wù)，等待 S 型 next-key lock；

insert 與 insert duplicate / replace 語句中唯一性檢查時加鎖模式不同：

•insert，S 型鎖

•insert duplcate / replace，X 型鎖

常見解決方案

常見的解決方案包括：

•修改事務(wù)隔離級別，其中：

?有效場景，比如更新不存在的場景時加鎖 gap lock，從 RR 改為 RC 時不加鎖；

?無效場景，比如插入唯一鍵前的唯一性檢查依然加間隙鎖；

•修改 SQL，比如將 insert duplicate 改寫為 insert，唯一鍵不沖突時前者加鎖 gap lock，后者不加鎖；

•數(shù)據(jù)庫版本升級，比如 5.7.26 中刪除 insert duplicate / replace 唯一鍵不沖突時的加鎖 gap lock；

日志分析工具

如下所示，自動分析死鎖日志并將提取出來的特征顯示在表格中，這里分析的是另一個死鎖案例。

功能入口在【易維-SRE開放平臺-MySQL死鎖分析】中，歡迎使用。

結(jié)論

MySQL 死鎖日志中的主要信息包括兩個事務(wù)、兩條 SQL、三部分鎖信息。

其中鎖信息又包括表、索引、鎖類型、數(shù)據(jù)行。

此外，還包括實例級別的信息，包括數(shù)據(jù)庫版本與事務(wù)隔離級別。

上述因素都會影響加鎖規(guī)則，因此提供日志分析工具用于信息的自動提取。

到此這篇關(guān)于MySQL死鎖日志的實例分析技巧總結(jié)的文章就介紹到這了,更多相關(guān)MySQL死鎖日志分析方法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！