RC級別下MySQL死鎖問題的解決

更新時間：2022年03月03日 15:42:50 作者：qq_42524262

本文主要介紹了RC級別下MySQL死鎖問題的解決，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下

背景

在工作中碰到一次死鎖問題，業(yè)務(wù)背景是在mq接收商品主數(shù)據(jù)時會更新商品其他數(shù)據(jù)，由于商品主數(shù)據(jù)和商品其他信息是一對多的關(guān)系，所以采用先刪后增的方式，結(jié)果異常監(jiān)管平臺報出來死鎖警告。

這是商品其他信息表，數(shù)據(jù)庫隔離級別是RC,表有一個唯一聯(lián)合索引，這個唯一索引就是引起死鎖的關(guān)鍵。

在這里插入圖片描述

死鎖分析

下面是線上的一個死鎖日志

2021-03-15 16:40:49 0x7f17e97ff700
*** (1) TRANSACTION:
TRANSACTION 2120576727, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 9384894, OS thread handle 139741055362816, query id 309547615 10.96.197.241 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(             cmmdty_code, 			business_field,             business_field_desc,             keyword_code,             lastmodifier,             lastmodified 			) VALUES 			( 			'12256633711', 			'TAX_CODE', 			'1040201230000000000', 			'000001', 			'sys',             now() 			)  ON DUPLICATE KEY UPDATE              business_field = 'TAX_CODE',               business_field_desc = '1040201230000000000',               keyword_code = '000001',               lastmodifier = 'sys',              lastmodified = now()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576727 lock_mode X waiting

*** (2) TRANSACTION:
TRANSACTION 2120576728, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 9481029, OS thread handle 139740678452992, query id 309547616 10.98.61.213 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(             cmmdty_code, 			business_field,             business_field_desc,             keyword_code,             lastmodifier,             lastmodified 			) VALUES 			( 			'12256633763', 			'TAX_CODE', 			'1040201230000000000', 			'000001', 			'sys',             now() 			)  ON DUPLICATE KEY UPDATE              business_field = 'TAX_CODE',               business_field_desc = '1040201230000000000',               keyword_code = '000001',               lastmodifier = 'sys',              lastmodified = now()
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X locks rec but not gap  //持有記錄鎖
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X waiting  //等待X鎖
*** WE ROLL BACK TRANSACTION (2)

RC級別下對于唯一索引的插入只會鎖定記錄，是可以并發(fā)插入的，所以應(yīng)該不是兩個insert 語句并發(fā)產(chǎn)生的問題。

之后查看代碼發(fā)現(xiàn)插入之前有一個delete操作，而且查看數(shù)據(jù)發(fā)現(xiàn)這兩條數(shù)據(jù)是相鄰的。

在這里插入圖片描述

之后我在本地復(fù)現(xiàn)了一下整個過程。

在這里插入圖片描述

查看加鎖信息

在這里插入圖片描述

這里當(dāng)時有兩個疑惑
1.為什么在RC級別下會有間隙鎖
2.為什么兩個事務(wù)會同時去等待12256633763記錄上的X鎖

對于第一個問題，網(wǎng)上很多博客視頻都會說RC下間隙鎖會失效，然后搬出官網(wǎng)的原話

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).

但后面還有一句

In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

意思是RC情況下間隙鎖會用于外鍵和唯一鍵檢查。
而且就算通過innodb_locks_unsafe_for_binlog = 1配置將間隙鎖關(guān)閉也不影響唯一索引對間隙鎖的需要。
但這里又會有個疑問，為什么并發(fā)插入不加間隙鎖，而先刪后增就會加。
我看到一篇博客中的源碼分析解釋了這個問題

在這里插入圖片描述

此刻又有個疑惑，為什么唯一沖突檢查一定要在標(biāo)有delete-marked的記錄之后加間隙鎖，我翻了很多博客資料，包括MySQL官方文檔，都沒有給出明確的解釋。
我思考了很久，間隙鎖是防止插入問題，那可能是為了在回滾時防止將其他事務(wù)的記錄回滾掉，但這種情況不會只出現(xiàn)在唯一索引上，為什么只有在唯一校驗時會加間隙鎖。后來我又覺得應(yīng)該是防止其他事務(wù)在區(qū)間插入相同記錄影響唯一檢驗，然而經(jīng)過測試，在delete之后，其他事務(wù)插入根本無法獲得當(dāng)前記錄的X鎖，所以根本不存在對間隙鎖的需要。
所以這個疑惑至今沒有得到解決，如果有大佬知道的話歡迎在評論區(qū)評論。

至少現(xiàn)在我們從源碼的層面知道了為什么在RC級別下為什么會有間隙鎖存在。

現(xiàn)在還有第二個問題，為什么兩個事務(wù)會同時等待12256633763記錄上的X鎖，在delete時，事務(wù)2已經(jīng)獲取了12256633763的記錄鎖，自身在獲取X鎖時應(yīng)該不會發(fā)生沖突。

在這里插入圖片描述

這里我也找到了加鎖源碼

在這里插入圖片描述

按照源碼理解，事務(wù)1需要鎖住11-63記錄的間隙以及63記錄本身，相當(dāng)于next-key,在對63加X鎖時，由于事務(wù)2已經(jīng)持有了63的記錄鎖，這兩個鎖的都屬于排他鎖但鎖的模式不同，從加鎖記錄中也可以看出。所以事務(wù)1會創(chuàng)建一個鎖對象，lock_mode X waiting放入請求隊列中，等待事務(wù)2記錄鎖釋放。
而事務(wù)2在對63創(chuàng)建X鎖時，發(fā)現(xiàn)已經(jīng)有一個該鎖的請求存在隊列中，所以也會創(chuàng)建一個鎖對象lock_mode X waiting放入請求隊列中，而這時觸發(fā)死鎖檢查發(fā)現(xiàn)有兩個事務(wù)同時等待同一個鎖，發(fā)生死鎖，默認回滾后請求的事務(wù)。

在這里插入圖片描述

死鎖解決

到這里疑惑基本都解決了，而引起該死鎖的原因就是先刪后增的操作。之后我們優(yōu)化了代碼邏輯，因為我們每次都是下發(fā)的全量數(shù)據(jù)，所以mq下發(fā)的記錄數(shù)據(jù)庫中已存在的就更新，沒有的就新增，而數(shù)據(jù)庫中有的mq下發(fā)的沒有的記錄就刪除。至此死鎖問題得到了解決。

到此這篇關(guān)于RC級別下MySQL死鎖問題的解決的文章就介紹到這了,更多相關(guān)RC級別下MySQL死鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: