背景
在工作中碰到一次死鎖問題，業(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)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文地址：https://blog.csdn.net/qq_42524262/article/details/123189023
