前幾天，線上發(fā)生了一次數(shù)據(jù)庫(kù)死鎖問(wèn)題，這一問(wèn)題前前后后排查了比較久的時(shí)間，這個(gè)過(guò)程中自己也對(duì)數(shù)據(jù)庫(kù)的鎖機(jī)制有了更深的理解。

本文總結(jié)了這次死鎖排查的全過(guò)程，并分析了導(dǎo)致死鎖的原因及解決方案。希望給大家提供一個(gè)死鎖的排查及解決思路。

本文涉及到 MySQL 執(zhí)行引擎、數(shù)據(jù)庫(kù)隔離級(jí)別、InnoDB 鎖機(jī)制、索引、數(shù)據(jù)庫(kù)事務(wù)等多領(lǐng)域知識(shí)。前車(chē)之鑒，后事之師，希望讀者們都可以有所收獲。

現(xiàn)象

某天晚上，同事正在發(fā)布，突然線上大量報(bào)警，很多是關(guān)于數(shù)據(jù)庫(kù)死鎖的，報(bào)警提示信息如下：

{"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org.apache.ibatis.exceptions.PersistenceException:  
Error updating database. Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL]  
Deadlock found when trying to get lock;  
The error occurred while setting parameters\n### SQL:  
update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW' 

通過(guò)報(bào)警，我們基本可以定位到發(fā)生死鎖的數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)表。先來(lái)介紹下本文案例中涉及到的數(shù)據(jù)庫(kù)相關(guān)信息。

背景情況

我們使用的數(shù)據(jù)庫(kù)是 MySQL 5.7，引擎是 InnoDB，事務(wù)隔離級(jí)別是 READ-COMMITED。

數(shù)據(jù)庫(kù)版本查詢(xún)方法：

select version(); 

引擎查詢(xún)方法：

show create table fund_transfer_stream; 

建表語(yǔ)句中會(huì)顯示存儲(chǔ)引擎信息，形如：ENGINE=InnoDB。

事務(wù)隔離級(jí)別查詢(xún)方法：

select @@tx_isolation; 

事務(wù)隔離級(jí)別設(shè)置方法(只對(duì)當(dāng)前 Session 生效)：

set session transaction isolation level read committed; 

PS：注意，如果數(shù)據(jù)庫(kù)是分庫(kù)的，以上幾條 SQL 語(yǔ)句需要在單庫(kù)上執(zhí)行，不要在邏輯庫(kù)執(zhí)行。

發(fā)生死鎖的表結(jié)構(gòu)及索引情況(隱去了部分無(wú)關(guān)字段和索引)：

CREATE TABLE `fund_transfer_stream` (  
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵', 
  `gmt_create` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', 
  `gmt_modified` datetime NOT NULL COMMENT '修改時(shí)間',  
  `pay_scene_name` varchar(256) NOT NULL COMMENT '支付場(chǎng)景名稱(chēng)',  
  `pay_scene_version` varchar(256) DEFAULT NULL COMMENT '支付場(chǎng)景版本', 
  `identifier` varchar(256) NOT NULL COMMENT '唯一性標(biāo)識(shí)', 
  `seller_id` varchar(64) NOT NULL COMMENT '賣(mài)家Id', 
  `state` varchar(64) DEFAULT NULL COMMENT '狀態(tài)', `fund_transfer_order_no` varchar(256)  
  DEFAULT NULL COMMENT '資金平臺(tái)返回的狀態(tài)',  
  PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier`  
  (KEY `idx_seller` (`seller_id`), 
  KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20)) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='資金流水'; 

該數(shù)據(jù)庫(kù)共有三個(gè)索引，1 個(gè)聚簇索引(主鍵索引)，2 個(gè)非聚簇索引(非主鍵索引)。

聚簇索引：

PRIMARY KEY (`id`) 

非聚簇索引：

KEY `idx_seller` (`seller_id`), 
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20)) 

以上兩個(gè)索引，其實(shí) idx_seller_transNo 已經(jīng)覆蓋到了 idx_seller，由于歷史原因，該表以 seller_id 分表，所以是先有的 idx_seller，后有的 idx_seller_transNo。

死鎖日志

當(dāng)數(shù)據(jù)庫(kù)發(fā)生死鎖時(shí)，可以通過(guò)以下命令獲取死鎖日志：

show engine innodb status 

發(fā)生死鎖，***時(shí)間查看死鎖日志，得到死鎖日志內(nèi)容如下：

Transactions deadlock detected, dumping detailed information. 
2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB:  
 
*** (1) TRANSACTION: 
TRANSACTION 173268495, ACTIVE 0 sec fetching rows 
mysql tables in use 1, locked 1 
LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1 
MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating 
 
update `fund_transfer_stream` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857')) 
2019-03-19T21:44:23.516321+08:00 5877341 [Note] InnoDB:  
 
*** (1) HOLDS THE LOCK(S): 
RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap 
Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 
 
2019-03-19T21:44:23.516565+08:00 5877341 [Note] InnoDB:  
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting 
Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0 
2019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB:  
 
*** (2) TRANSACTION: 
TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81 
mysql tables in use 1, locked 1 
302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1 
MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating 
 
update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256')) 
2019-03-19T21:44:23.517855+08:00 5877341 [Note] InnoDB:  
 
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap 
Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0 
 
2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB:  
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waiting 
Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 
 
2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) 

簡(jiǎn)單解讀一下死鎖日志，可以得到以下信息：

①導(dǎo)致死鎖的兩條 SQL 語(yǔ)句分別是：

update `fund_transfer_stream_0056`  
set `gmt_modified` = NOW(), `state` = 'PROCESSING'  
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256')) 

update `fund_transfer_stream_0056`  
set `gmt_modified` = NOW(), `state` = 'PROCESSING'  
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857')) 

②事務(wù) 1，持有索引 idx_seller_transNo 的鎖，在等待獲取 PRIMARY 的鎖。

③事務(wù) 2，持有 PRIMARY 的鎖，在等待獲取 idx_seller_transNo 的鎖。

④因事務(wù) 1 和事務(wù) 2 之間發(fā)生循環(huán)等待，故發(fā)生死鎖。

⑤事務(wù) 1 和事務(wù) 2 當(dāng)前持有的鎖均為：lock_mode X locks rec but not gap。

兩個(gè)事務(wù)對(duì)記錄加的都是 X 鎖，No Gap 鎖，即對(duì)當(dāng)行記錄加鎖(Record Lock)，并未加間隙鎖。

X 鎖：排他鎖、又稱(chēng)寫(xiě)鎖。若事務(wù) T 對(duì)數(shù)據(jù)對(duì)象 A 加上 X 鎖，事務(wù) T 可以讀 A 也可以修改 A，其他事務(wù)不能再對(duì) A 加任何鎖，直到 T 釋放 A 上的鎖。這保證了其他事務(wù)在 T 釋放 A 上的鎖之前不能再讀取和修改 A。

與之對(duì)應(yīng)的是 S 鎖：共享鎖，又稱(chēng)讀鎖，若事務(wù) T 對(duì)數(shù)據(jù)對(duì)象 A 加上 S 鎖，則事務(wù) T 可以讀 A 但不能修改 A，其他事務(wù)只能再對(duì) A 加 S 鎖，而不能加 X 鎖，直到 T 釋放 A 上的 S 鎖。

這保證了其他事務(wù)可以讀 A，但在 T 釋放 A 上的 S 鎖之前不能對(duì) A 做任何修改。

Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身。Gap 鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況。

Next-Key Lock：1+2，鎖定一個(gè)范圍，并且鎖定記錄本身。對(duì)于行的查詢(xún)，都是采用該方法，主要目的是解決幻讀的問(wèn)題。

問(wèn)題排查

根據(jù)我們目前已知的數(shù)據(jù)庫(kù)相關(guān)信息，以及死鎖的日志，我們基本可以做一些簡(jiǎn)單的判定。

首先，此次死鎖一定是和 Gap 鎖以及 Next-Key Lock 沒(méi)有關(guān)系的。因?yàn)槲覀兊臄?shù)據(jù)庫(kù)隔離級(jí)別是 RC(READ-COMMITED)的，這種隔離級(jí)別是不會(huì)添加 Gap 鎖的。前面的死鎖日志也提到這一點(diǎn)。

然后，就要翻代碼了，看看我們的代碼中事務(wù)到底是怎么做的。核心代碼及 SQL 如下：

@Transactional(rollbackFor = Exception.class) 
public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) { 
    fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo); 
    return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo,"PROCESSING"); 
} 

該代碼的目的是先后修改同一條記錄的兩個(gè)不同字段，updateFundStreamId SQL：

update fund_transfer_stream 
        set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo} 
        where id = #{id} and seller_id = #{sellerId} 

updateStatus SQL：

update fund_transfer_stream 
    set gmt_modified=now(),state = #{state} 
    where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId} 
    and state = 'NEW' 

可以看到，我們的同一個(gè)事務(wù)中執(zhí)行了兩條 Update 語(yǔ)句，這里分別查看下兩條 SQL 的執(zhí)行計(jì)劃：

updateFundStreamId 執(zhí)行的時(shí)候使用到的是 PRIMARY 索引。

updateStatus 執(zhí)行的時(shí)候使用到的是 idx_seller_transNo 索引。

通過(guò)執(zhí)行計(jì)劃，我們發(fā)現(xiàn) updateStatus 其實(shí)是有兩個(gè)索引可以用的，執(zhí)行的時(shí)候真正使用的是 idx_seller_transNo 索引。這是因?yàn)? MySQL 查詢(xún)優(yōu)化器是基于代價(jià)(cost-based)的查詢(xún)方式。

因此，在查詢(xún)過(guò)程中，最重要的一部分是根據(jù)查詢(xún)的 SQL 語(yǔ)句，依據(jù)多種索引，計(jì)算查詢(xún)需要的代價(jià)，從而選擇***的索引方式生成查詢(xún)計(jì)劃。

我們查詢(xún)執(zhí)行計(jì)劃是在死鎖發(fā)生之后做的，事后查詢(xún)的執(zhí)行計(jì)劃和發(fā)生死鎖那一刻的索引使用情況并不一定是相同的。

但是，我們結(jié)合死鎖日志，也可以定位到以上兩條 SQL 語(yǔ)句執(zhí)行的時(shí)候使用到的索引。

即 updateFundStreamId 執(zhí)行的時(shí)候使用到的是 PRIMARY 索引，updateStatus 執(zhí)行的時(shí)候使用到的是 idx_seller_transNo 索引。

有了以上這些已知信息，我們就可以開(kāi)始排查死鎖原因及其背后的原理了。

通過(guò)分析死鎖日志，再結(jié)合我們的代碼以及數(shù)據(jù)庫(kù)建表語(yǔ)句，我們發(fā)現(xiàn)主要問(wèn)題出在我們的 idx_seller_transNo 索引上面：

KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20)) 

索引創(chuàng)建語(yǔ)句中，我們使用了前綴索引，為了節(jié)約索引空間，提高索引效率，我們只選擇了 fund_transfer_order_no 字段的前 20 位作為索引值。

因?yàn)?fund_transfer_order_no 只是普通索引，而非唯一性索引。又因?yàn)樵谝环N特殊情況下，會(huì)有同一個(gè)用戶(hù)的兩個(gè) fund_transfer_order_no 的前 20 位相同。

這就導(dǎo)致兩條不同的記錄的索引值一樣(因?yàn)?seller_id 和 fund_transfer_order_no(20) 都相同 )。

就如本文中的例子，發(fā)生死鎖的兩條記錄的 fund_transfer_order_no 字段的這兩個(gè)值就是前 20 位是相同的：

• 99010015000805619031958363857
• 99010015000805619031957477256

那么為什么 fund_transfer_order_no 的前 20 位相同會(huì)導(dǎo)致死鎖呢?

加鎖原理

我們就拿本次的案例來(lái)看一下 MySQL 數(shù)據(jù)庫(kù)加鎖的原理是怎樣的，本文的死鎖背后又發(fā)生了什么。

我們?cè)跀?shù)據(jù)庫(kù)上模擬死鎖場(chǎng)景，執(zhí)行順序如下：

我們知道，在 MySQL 中，行級(jí)鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種：

• 如果一條 SQL 語(yǔ)句操作了主鍵索引，MySQL 就會(huì)鎖定這條主鍵索引。
• 如果一條語(yǔ)句操作了非主鍵索引，MySQL 會(huì)先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引。

主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 中，主鍵索引也被稱(chēng)為聚簇索引(Clustered Index)。

非主鍵索引的葉子節(jié)點(diǎn)的內(nèi)容是主鍵的值，在 InnoDB 中，非主鍵索引也被稱(chēng)為非聚簇索引(Secondary Index)。

所以，本文的示例中涉及到的索引結(jié)構(gòu)(索引是 B+ 樹(shù)，簡(jiǎn)化成表格了)如圖：

死鎖的發(fā)生與否，并不在于事務(wù)中有多少條 SQL 語(yǔ)句，死鎖的關(guān)鍵在于：兩個(gè)(或以上)的 Session 加鎖的順序不一致。

那么接下來(lái)就看下上面的例子中兩個(gè)事務(wù)的加鎖順序是怎樣的：

下圖是分解圖，每一條 SQL 執(zhí)行的時(shí)候加鎖情況：

結(jié)合以上兩張圖，我們發(fā)現(xiàn)了導(dǎo)致死鎖的原因：

• 事務(wù) 1 執(zhí)行 update1 占用 PRIMARY = 1 的鎖;事務(wù) 2 執(zhí)行 update1 占有 PRIMARY = 2 的鎖;。
• 事務(wù) 1 執(zhí)行 update2 占有 idx_seller_transNo = (3111095611，99010015000805619031)的鎖，嘗試占有 PRIMARY = 2 鎖失敗(阻塞)。
• 事務(wù) 2 執(zhí)行 update2 嘗試占有 idx_seller_transNo = (3111095611，99010015000805619031)的鎖失敗(死鎖)。

事務(wù)在以非主鍵索引為 Where 條件進(jìn)行 Update 的時(shí)候，會(huì)先對(duì)該非主鍵索引加鎖，然后再查詢(xún)?cè)摲侵麈I索引對(duì)應(yīng)的主鍵索引都有哪些，再對(duì)這些主鍵索引進(jìn)行加鎖。)

解決方法

至此，我們分析清楚了導(dǎo)致死鎖的根本原理以及其背后的原理。那么這個(gè)問(wèn)題解決起來(lái)就不難了。

可以從兩方面入手，分別是：

• 修改索引
• 修改代碼(包含 SQL 語(yǔ)句)

修改索引：只要我們把前綴索引 idx_seller_transNo 中 fund_transfer_order_no 的前綴長(zhǎng)度修改下就可以了。

比如改成 50，即可避免死鎖。但是，改了 idx_seller_transNo 的前綴長(zhǎng)度后，可以解決死鎖的前提條件是 Update 語(yǔ)句真正執(zhí)行的時(shí)候，會(huì)用到 fund_transfer_order_no 索引。

如果 MySQL 查詢(xún)優(yōu)化器在代價(jià)分析之后，決定使用索引 KEY idx_seller(seller_id)，那么還是會(huì)存在死鎖問(wèn)題。原理和本文類(lèi)似。

所以，根本解決辦法就是改代碼：

• 所有 Update 都通過(guò)主鍵 ID 進(jìn)行。
• 在同一個(gè)事務(wù)中，避免出現(xiàn)多條 Update 語(yǔ)句修改同一條記錄。

總結(jié)與思考

在死鎖發(fā)生之后的一周內(nèi)，我?guī)缀趺刻於紩?huì)抽空研究一會(huì)，問(wèn)題早早的就定位到了，修改方案也有了，但是其中原理一直沒(méi)搞清楚。

前前后后做過(guò)很多種推斷及假設(shè)，又都被自己一次次推翻。最終還是要靠實(shí)踐來(lái)驗(yàn)證自己的想法。

于是我自己在本地安裝了數(shù)據(jù)庫(kù)，實(shí)戰(zhàn)的做了些測(cè)試，并實(shí)時(shí)查看數(shù)據(jù)庫(kù)鎖情況。show engine innodb status ;可以查看鎖情況。最終才搞清楚原理。

簡(jiǎn)單說(shuō)幾點(diǎn)思考：

• 遇到問(wèn)題，不要猜!!!親手復(fù)現(xiàn)下問(wèn)題，然后再來(lái)分析。
• 不要忽略上下文!!!我剛開(kāi)始就是只關(guān)注死鎖日志，一直忽略了代碼中的事務(wù)其實(shí)還執(zhí)行了另外一條 SQL 語(yǔ)句(updateFundStreamId)。
• 理論知識(shí)再充足，關(guān)鍵時(shí)刻不一定想的起來(lái)!!!
• 坑都是自己埋的!!!