以下是筆者看網(wǎng)上并結(jié)合自己的經(jīng)歷整理所得。真真切切的發(fā)生的事情。希望能幫助到你

這是一個(gè)真實(shí)的生產(chǎn)問題，經(jīng)過長時(shí)間的排查和多次尋求 DBA 的幫助，最終我自己花了一個(gè)月的時(shí)間才定位到這個(gè)問題。問題非常有意思，值得大家關(guān)注。

問題現(xiàn)象

某天晚上，同事正在發(fā)布時(shí)，突然線上出現(xiàn)大量報(bào)警，其中很多是關(guān)于數(shù)據(jù)庫死鎖的。報(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'

通過報(bào)警，我們基本可以確定發(fā)生死鎖的數(shù)據(jù)庫和數(shù)據(jù)庫表?，F(xiàn)在先來介紹一下本文案例中涉及的數(shù)據(jù)庫相關(guān)信息。

問題背景

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

數(shù)據(jù)庫版本查詢方法：

SELECT version();

引擎查詢方法：

show create table fund_transfer_stream;

建表語句中會顯示存儲引擎信息，形如：ENGINE=InnoDB

事務(wù)隔離級別查詢方法：

select @@tx_isolation;

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

set session transaction isolation level read committed;

★

PS：請注意，如果數(shù)據(jù)庫是分庫的，以下幾條 SQL 語句需要在單個(gè)庫中執(zhí)行，而不是在邏輯庫中執(zhí)行。

以下是發(fā)生死鎖的表的結(jié)構(gòu)及部分索引情況（已隱去部分無關(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 '支付場景名稱',
  `pay_scene_version` varchar(256) DEFAULT NULL COMMENT '支付場景版本',
  `identifier` varchar(256) NOT NULL COMMENT '唯一性標(biāo)識',
  `seller_id` varchar(64) NOT NULL COMMENT '賣家Id',
  `state` varchar(64) DEFAULT NULL COMMENT '狀態(tài)', `fund_transfer_order_no` varchar(256)
  DEFAULT NULL COMMENT '資金平臺返回的狀態(tài)',
  PRIMARY KEY (`id`),
  KEY `idx_seller` (`seller_id`),
  KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='資金流水';

該數(shù)據(jù)庫具有三個(gè)索引，包括一個(gè)聚簇索引（主鍵索引）和兩個(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。由于歷史原因，該表在引入 idx_seller 之后才引入了 idx_seller_transNo。

死鎖日志

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

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)

簡要解讀死鎖日志，可以得到以下信息：

1. 導(dǎo)致死鎖的兩條 SQL 語句分別是：

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

2. 事務(wù) 1 持有 idx_seller_transNo 索引的鎖，正在等待獲取 PRIMARY 索引的鎖。
3. 事務(wù) 2 持有 PRIMARY 索引的鎖，正在等待獲取 idx_seller_transNo 索引的鎖。
4. 由于事務(wù) 1 和事務(wù) 2 之間發(fā)生了循環(huán)等待，導(dǎo)致了死鎖的發(fā)生。
5. 事務(wù) 1 和事務(wù) 2 當(dāng)前持有的鎖模式均為：lock_mode X locks rec but not gap。

兩個(gè)事務(wù)對記錄都加了 X 鎖，且沒有加間隙鎖（No Gap 鎖），即它們都在針對特定行記錄進(jìn)行鎖定。

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

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

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

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

問題排查

根據(jù)目前已知的數(shù)據(jù)庫相關(guān)信息以及死鎖日志，可以做出一些簡單的判斷。

首先，此次死鎖與 Gap 鎖和 Next-Key Lock 無關(guān)。因?yàn)槲覀兊臄?shù)據(jù)庫使用的是 RC（READ-COMMITED）事務(wù)隔離級別，這種級別不會添加 Gap 鎖。死鎖日志前面也提到了這一點(diǎn)。

接下來，需要查看代碼，詳細(xì)了解我們的事務(wù)實(shí)現(xiàn)方式。以下是核心代碼及相關(guān) SQL：

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

該代碼的目的是先后修改同一條記錄的兩個(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 語句，這里分別查看下兩條 SQL 的執(zhí)行計(jì)劃：

圖片

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

圖片

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

通過執(zhí)行計(jì)劃，我們發(fā)現(xiàn) updateStatus 其實(shí)是有兩個(gè)索引可以用的，執(zhí)行的時(shí)候真正使用的是 idx_seller_transNo 索引。這是因?yàn)镸ySQL 查詢優(yōu)化器是基于代價(jià)（cost-based）的查詢方式。因此，在查詢過程中，最重要的一部分是根據(jù)查詢的 SQL 語句，依據(jù)多種索引，計(jì)算查詢需要的代價(jià)，從而選擇最優(yōu)的索引方式生成查詢計(jì)劃。

我們查詢執(zhí)行計(jì)劃是在死鎖發(fā)生之后做的，事后查詢的執(zhí)行計(jì)劃和發(fā)生死鎖那一刻的索引使用情況并不一定相同的。但是，我們結(jié)合死鎖日志，也可以定位到以上兩條 SQL 語句執(zhí)行的時(shí)候使用到的索引。即 updateFundStreamId 執(zhí)行的時(shí)候使用到的是 PRIMARY 索引，updateStatus 執(zhí)行的時(shí)候使用到的是 idx_seller_transNo 索引。

有了以上已知信息，我們現(xiàn)在可以開始排查死鎖的原因及其根本原理了。通過分析死鎖日志，并結(jié)合我們的代碼和數(shù)據(jù)庫表結(jié)構(gòu)，我們發(fā)現(xiàn)主要問題出在我們的 idx_seller_transNo 索引上：

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

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

因?yàn)?fund_transfer_order_no 只是普通索引，而非唯一性索引。又因?yàn)樵谝环N特殊情況下，會有同一個(gè)用戶的兩個(gè) fund_transfer_order_no 的前 20 位相同，這就導(dǎo)致兩條不同的記錄的索引值一樣（因?yàn)?seller_id 和 fund_transfer_order_no(20)都相同 ）。

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

圖片

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

加鎖原理

我們就拿本次的案例來看一下 MySQL 數(shù)據(jù)庫加鎖的原理是怎樣的，本文的死鎖背后又發(fā)生了什么。我們在數(shù)據(jù)庫上模擬死鎖場景，執(zhí)行順序如下：

在 MySQL 中，行級鎖并不是直接鎖定記錄，而是通過鎖定索引來實(shí)現(xiàn)的。索引分為主鍵索引和非主鍵索引兩種情況：

1. 操作主鍵索引： 如果一條 SQL 語句操作了主鍵索引，MySQL 會直接鎖定這條主鍵索引。
2. 操作非主鍵索引： 如果一條 SQL 語句操作了非主鍵索引，MySQL 會先鎖定該非主鍵索引，然后再鎖定相關(guān)的主鍵索引。

這種策略確保了 MySQL 在實(shí)施行級鎖時(shí)能夠有效地控制并發(fā)訪問，從而避免出現(xiàn)數(shù)據(jù)不一致或者數(shù)據(jù)丟失的情況。

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

非主鍵索引的葉子節(jié)點(diǎn)的內(nèi)容是主鍵的值，在 InnoDB 中，非主鍵索引也被稱為非聚簇索引（secondary index）

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

圖片

死鎖的發(fā)生與否，并不在于事務(wù)中有多少條 SQL 語句，關(guān)鍵在于：兩個(gè)或更多的會話加鎖的順序不一致。接下來看看上面例子中兩個(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)的鎖失?。ㄋ梨i）；

事務(wù)在使用非主鍵索引作為 WHERE 條件進(jìn)行 UPDATE 時(shí)，會先對該非主鍵索引進(jìn)行加鎖，然后查詢該非主鍵索引對應(yīng)的所有主鍵索引，再對這些主鍵索引進(jìn)行加鎖。

解決方法

至此，我們已經(jīng)清楚地分析了導(dǎo)致死鎖的根本原理及其背后的機(jī)制。因此，解決這個(gè)問題并不復(fù)雜。

我們可以從兩個(gè)方面入手，分別是修改索引和修改代碼（包括 SQL 語句）。

在修改索引方面：只需將 idx_seller_transNo 索引中 fund_transfer_order_no 的前綴長度調(diào)整為較小的值，比如改成 50，就可以避免死鎖的發(fā)生。

然而，修改了 idx_seller_transNo 索引的前綴長度后，要注意確保在實(shí)際執(zhí)行 update 語句時(shí)，MySQL 查詢優(yōu)化器確實(shí)選擇了使用 fund_transfer_order_no 索引。如果優(yōu)化器在代價(jià)分析后仍然決定使用索引 KEY idx_seller(seller_id)，那么死鎖問題仍可能存在，這與本文中的原理類似。

因此，根本解決方案是修改代碼：

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

思考總結(jié)

在死鎖發(fā)生后的一周內(nèi)，我?guī)缀趺刻於紩榭者M(jìn)行研究。問題很早就被定位到了，并且修改方案也已經(jīng)有了，但是其中的原理一直沒有搞清楚。

我做過很多種推斷和假設(shè)，但每一次都被自己推翻。最終，我意識到還是需要通過實(shí)踐來驗(yàn)證我的想法。因此，我在本地安裝了數(shù)據(jù)庫，在實(shí)際操作中進(jìn)行了多次測試，并實(shí)時(shí)查看數(shù)據(jù)庫的鎖情況。使用命令 show engine innodb status; 可以查看詳細(xì)的鎖信息。最終，我才徹底搞清楚了背后的原理。

簡要總結(jié)幾點(diǎn)思考：

1. 遇到問題時(shí)，不要僅憑猜測！重要的是親自復(fù)現(xiàn)問題，然后再進(jìn)行分析。
2. 不要忽視上下文！剛開始我只看死鎖日志，卻忽略了代碼中可能執(zhí)行了另一條 SQL 語句（updateFundStreamId）。
3. 理論知識再豐富，關(guān)鍵時(shí)刻也不一定能夠想得起來！
4. 遇到坑，大多是自己挖的！