引言

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

死鎖介紹

觸發(fā)條件

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

•互斥

•占有且等待

•不可搶占用

•循環(huán)等待

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

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

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

•主動(dòng)，開(kāi)啟主動(dòng)死鎖檢測(cè)，檢測(cè)到死鎖后回滾其中一個(gè)事務(wù)。默認(rèn)開(kāi)啟。

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

死鎖檢測(cè)

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

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

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

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

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

死鎖案例

日志

------------------------
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)

其中：

•加鎖索引相同，都是二級(jí)索引；

•兩個(gè)事務(wù)中三個(gè)鎖對(duì)應(yīng)兩個(gè)主鍵，包括 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'商品編號(hào)',
  `product_level`varchar(50)NOTNULLCOMMENT'商品等級(jí)',
  `lot_no`varchar(50)NOTNULLCOMMENT'批次號(hào)',
  `owner_no`varchar(50)NOTNULLCOMMENT'貨主編號(hào)',
  PRIMARYKEY(`id`),
  UNIQUEKEY`idx_map_goods_product_lot_owner`(`map_area_id`,`goods_no`,`product_level`,`lot_no`,`owner_no`)

其中：

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

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

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

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

死鎖分析方法

日志格式

簡(jiǎn)化后的死鎖日志格式如下所示。

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)

其中主要信息包括：

•兩個(gè)事務(wù)

•兩條 SQL

•三部分鎖信息

其中存在的問(wèn)題包括：

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

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

•SQL 超長(zhǎng)時(shí)自動(dòng)截?cái)啵?/p>

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

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

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

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

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

鎖信息

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

注意行鎖是給表的索引的記錄加鎖，且是給訪問(wèn)過(guò)的對(duì)象加鎖。

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

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

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

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

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

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

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

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

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

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

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

鎖類型

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

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

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

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

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

?insert intention lock 是一種特殊的 gap lock，表示插入的意向，用于在插入操作存在 gap lock 時(shí)表示等待狀態(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ā)生在鎖沖突時(shí)，鎖沖突根據(jù)鎖兼容矩陣判斷，下面介紹鎖兼容矩陣。

鎖兼容矩陣

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

其中：

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

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

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

常見(jiàn)加鎖規(guī)則

加鎖場(chǎng)景：

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

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

?為防止臟寫(xiě)，record lock；

?為防止幻讀，gap lock；

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

加鎖類型：

•顯式鎖；

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

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

•退化為 record lock：

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

•退化為 gap lock：

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

不退化的場(chǎng)景：

•supremum pseudo-record；

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

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

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

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

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

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

insert 與 insert duplicate / replace 語(yǔ)句中唯一性檢查時(shí)加鎖模式不同：

•insert，S 型鎖

•insert duplcate / replace，X 型鎖

常見(jiàn)解決方案

常見(jiàn)的解決方案包括：

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

?有效場(chǎng)景，比如更新不存在的場(chǎng)景時(shí)加鎖 gap lock，從 RR 改為 RC 時(shí)不加鎖；

?無(wú)效場(chǎng)景，比如插入唯一鍵前的唯一性檢查依然加間隙鎖；

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

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

日志分析工具

如下所示，自動(dòng)分析死鎖日志并將提取出來(lái)的特征顯示在表格中，這里分析的是另一個(gè)死鎖案例。

功能入口在【易維-SRE開(kāi)放平臺(tái)-MySQL死鎖分析】中，歡迎使用。

結(jié)論

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

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

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

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

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

最新評(píng)論