一次詭異的線上數(shù)據(jù)庫的死鎖問題排查過程
前幾天，線上發(fā)生了一次數(shù)據(jù)庫死鎖問題，這一問題前前后后排查了比較久的時間，這個過程中自己也對數(shù)據(jù)庫的鎖機制有了更深的理解。本文總結(jié)了這次死鎖排查的全過程，并分析了導(dǎo)致死鎖的原因及解決方案。希望給大家提供一個死鎖的排查及解決思路。
本文涉及到MySQL執(zhí)行引擎、數(shù)據(jù)庫隔離級別、Innodb鎖機制、索引、數(shù)據(jù)庫事務(wù)等多領(lǐng)域知識。前車之鑒，后事之師，希望讀者們都可以有所收獲。
1、現(xiàn)象
某天晚上，同事正在發(fā)布，突然線上大量報警，很多是關(guān)于數(shù)據(jù)庫死鎖的，報警提示信息如下：
- {"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'
通過報警，我們基本可以定位到發(fā)生死鎖的數(shù)據(jù)庫以及數(shù)據(jù)庫表。先來介紹下本文案例中涉及到的數(shù)據(jù)庫相關(guān)信息。
2、背景情況
我們使用的數(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語句需要在單庫上執(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)建時間',
- `gmt_modified` datetime NOT NULL COMMENT '修改時間',
- `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`),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ù)庫共有三個索引，1個聚簇索引(主鍵索引)，2個非聚簇索(非主鍵索引)引。
聚簇索引：
- PRIMARY KEY (`id`)
非聚簇索引：
- KEY `idx_seller` (`seller_id`),
- KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
以上兩個索引，其實idx_seller_transNo已經(jīng)覆蓋到了idx_seller，由于歷史原因，因為該表以seller_id分表，所以是先有的idx_seller，后有的idx_seller_transNo
3、死鎖日志
當(dāng)數(shù)據(jù)庫發(fā)生死鎖時，可以通過以下命令獲取死鎖日志：
- show engine innodb status
發(fā)生死鎖，***時間查看死鎖日志，得到死鎖日志內(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)等待，故發(fā)生死鎖。
5.事務(wù)1和事務(wù)2當(dāng)前持有的鎖均為：lock_mode X locks rec but not gap
兩個事務(wù)對記錄加的都是X 鎖，No Gap鎖，即對當(dāng)行記錄加鎖(Record Lock)，并未加間隙鎖。
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：間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況。
Next-Key Lock：1+2，鎖定一個范圍，并且鎖定記錄本身。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。
4、問題排查
根據(jù)我們目前已知的數(shù)據(jù)庫相關(guān)信息，以及死鎖的日志，我們基本可以做一些簡單的判定。
首先，此次死鎖一定是和Gap鎖以及Next-Key Lock沒有關(guān)系的。因為我們的數(shù)據(jù)庫隔離級別是RC(READ-COMMITED)的，這種隔離級別是不會添加Gap鎖的。前面的死鎖日志也提到這一點。
然后，就要翻代碼了，看看我們的代碼中事務(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");
- }
該代碼的目的是先后修改同一條記錄的兩個不同字段，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'
可以看到，我們的同一個事務(wù)中執(zhí)行了兩條Update語句，這里分別查看下兩條SQL的執(zhí)行計劃：
updateFundStreamId執(zhí)行的時候使用到的是PRIMARY索引。
updateStatus執(zhí)行的時候使用到的是idx_seller_transNo索引。
通過執(zhí)行計劃，我們發(fā)現(xiàn)updateStatus其實是有兩個索引可以用的，執(zhí)行的時候真正使用的是idx_seller_transNo索引。這是因為MySQL查詢優(yōu)化器是基于代價(cost-based)的查詢方式。因此，在查詢過程中，最重要的一部分是根據(jù)查詢的SQL語句，依據(jù)多種索引，計算查詢需要的代價，從而選擇***的索引方式生成查詢計劃。
我們查詢執(zhí)行計劃是在死鎖發(fā)生之后做的，事后查詢的執(zhí)行計劃和發(fā)生死鎖那一刻的索引使用情況并不一定相同的。但是，我們結(jié)合死鎖日志，也可以定位到以上兩條SQL語句執(zhí)行的時候使用到的索引。即updateFundStreamId執(zhí)行的時候使用到的是PRIMARY索引，updateStatus執(zhí)行的時候使用到的是idx_seller_transNo索引。
有了以上這些已知信息，我們就可以開始排查死鎖原因及其背后的原理了。通過分析死鎖日志，再結(jié)合我們的代碼以及數(shù)據(jù)庫建表語句，我們發(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位作為索引值。
因為fund_transfer_order_no只是普通索引，而非唯一性索引。又因為在一種特殊情況下，會有同一個用戶的兩個fund_transfer_order_no的前20位相同，這就導(dǎo)致兩條不同的記錄的索引值一樣(因為seller_id 和fund_transfer_order_no(20)都相同 )。
就如本文中的例子，發(fā)生死鎖的兩條記錄的fund_transfer_order_no字段的值：99010015000805619031958363857和99010015000805619031957477256 這兩個就是前20位相同的。
那么為什么fund_transfer_order_no的前20位相同會導(dǎo)致死鎖呢?
5、加鎖原理
我們就拿本次的案例來看一下MySql數(shù)據(jù)庫加鎖的原理是怎樣的，本文的死鎖背后又發(fā)生了什么。
我們在數(shù)據(jù)庫上模擬死鎖場景，執(zhí)行順序如下：
我們知道，在MySQL中，行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引。
主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在InnoDB中，主鍵索引也被稱為聚簇索引(clustered index)
非主鍵索引的葉子節(jié)點的內(nèi)容是主鍵的值，在InnoDB中，非主鍵索引也被稱為非聚簇索引(secondary index)
所以，本文的示例中涉及到的索引結(jié)構(gòu)(索引是B+樹，簡化成表格了)如圖：

死鎖的發(fā)生與否，并不在于事務(wù)中有多少條SQL語句，死鎖的關(guān)鍵在于：兩個(或以上)的Session加鎖的順序不一致。那么接下來就看下上面的例子中兩個事務(wù)的加鎖順序是怎樣的：
下圖是分解圖，每一條SQL執(zhí)行的時候加鎖情況：
結(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的時候，會先對該非主鍵索引加鎖，然后再查詢該非主鍵索引對應(yīng)的主鍵索引都有哪些，再對這些主鍵索引進(jìn)行加鎖。)
6、解決方法
至此，我們分析清楚了導(dǎo)致死鎖的根本原理以及其背后的原理。那么這個問題解決起來就不難了。
可以從兩方面入手，分別是修改索引和修改代碼(包含SQL語句)。
修改索引：只要我們把前綴索引 idx_seller_transNo中fund_transfer_order_no的前綴長度修改下就可以了。比如改成50。即可避免死鎖。
但是，改了idx_seller_transNo的前綴長度后，可以解決死鎖的前提條件是update語句真正執(zhí)行的時候，會用到fund_transfer_order_no索引。如果MySQL查詢優(yōu)化器在代價分析之后，決定使用索引 KEY idx_seller(seller_id)，那么還是會存在死鎖問題。原理和本文類似。
所以，根本解決辦法就是改代碼：
- 所有update都通過主鍵ID進(jìn)行。
- 在同一個事務(wù)中，避免出現(xiàn)多條update語句修改同一條記錄。
7、總結(jié)與思考
在死鎖發(fā)生之后的一周內(nèi)，我?guī)缀趺刻於紩榭昭芯恳粫?，問題早早的就定位到了，修改方案也有了，但是其中原理一直沒搞清楚。
前前后后做過很多中種推斷及假設(shè)，又都被自己一次次推翻。最終還是要靠實踐來驗證自己的想法。于是我自己在本地安裝了數(shù)據(jù)庫，實戰(zhàn)的做了些測試，并實時查看數(shù)據(jù)庫鎖情況。show engine innodb status ;可以查看鎖情況。最終才搞清楚原理。
簡單說幾點思考：
- 遇到問題，不要猜!!!親手復(fù)現(xiàn)下問題，然后再來分析。
- 不要忽略上下文!!!我剛開始就是只關(guān)注死鎖日志，一直忽略了代碼中的事務(wù)其實還執(zhí)行了另外一條SQL語句(updateFundStreamId)。
- 理論知識再充足，關(guān)鍵時刻不一定想的起來!!!
- 坑都是自己埋的!!!
參考資料：
- http://hedengcheng.com/?p=771
- https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
- 《MySql實戰(zhàn)45講》
- https://www.hollischuang.com/archives/914
