背景
在工作中碰到一次死鎖問題，業務背景是在mq接收商品主數據時會更新商品其他數據，由于商品主數據和商品其他信息是一對多的關系，所以采用先刪后增的方式，結果異常監管平臺報出來死鎖警告。
這是商品其他信息表，數據庫隔離級別是RC,表有一個唯一聯合索引，這個唯一索引就是引起死鎖的關鍵。
死鎖分析
下面是線上的一個死鎖日志
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級別下對于唯一索引的插入只會鎖定記錄，是可以并發插入的，所以應該不是兩個insert 語句并發產生的問題。
之后查看代碼發現插入之前有一個delete操作，而且查看數據發現這兩條數據是相鄰的。
之后我在本地復現了一下整個過程。
查看加鎖信息
這里當時有兩個疑惑
1.為什么在RC級別下會有間隙鎖
2.為什么兩個事務會同時去等待12256633763記錄上的X鎖
對于第一個問題，網上很多博客視頻都會說RC下間隙鎖會失效，然后搬出官網的原話
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配置將間隙鎖關閉也不影響唯一索引對間隙鎖的需要。
但這里又會有個疑問，為什么并發插入不加間隙鎖，而先刪后增就會加。
我看到一篇博客中的源碼分析解釋了這個問題
此刻又有個疑惑，為什么唯一沖突檢查一定要在標有delete-marked的記錄之后加間隙鎖，我翻了很多博客資料，包括MySQL官方文檔，都沒有給出明確的解釋。
我思考了很久，間隙鎖是防止插入問題，那可能是為了在回滾時防止將其他事務的記錄回滾掉，但這種情況不會只出現在唯一索引上，為什么只有在唯一校驗時會加間隙鎖。后來我又覺得應該是防止其他事務在區間插入 相同記錄影響唯一檢驗，然而經過測試，在delete之后，其他事務插入根本無法獲得當前記錄的X鎖，所以根本不存在對間隙鎖的需要。
所以這個疑惑至今沒有得到解決，如果有大佬知道的話歡迎在評論區評論。
至少現在我們從源碼的層面知道了為什么在RC級別下為什么會有間隙鎖存在。
現在還有第二個問題，為什么兩個事務會同時等待12256633763記錄上的X鎖，在delete時，事務2已經獲取了12256633763的記錄鎖，自身在獲取X鎖時應該不會發生沖突。
這里我也找到了加鎖源碼
按照源碼理解，事務1需要鎖住11-63記錄的間隙以及63記錄本身，相當于next-key,在對63加X鎖時，由于事務2已經持有了63的記錄鎖，這兩個鎖的都屬于排他鎖但鎖的模式不同，從加鎖記錄中也可以看出。所以事務1會創建一個鎖對象，lock_mode X waiting放入請求隊列中，等待事務2記錄鎖釋放。
而事務2在對63創建X鎖時，發現已經有一個該鎖的請求存在隊列中，所以也會創建一個鎖對象lock_mode X waiting放入請求隊列中，而這時觸發死鎖檢查發現有兩個事務同時等待同一個鎖，發生死鎖，默認回滾后請求的事務。
死鎖解決
到這里疑惑基本都解決了，而引起該死鎖的原因就是先刪后增的操作。之后我們優化了代碼邏輯，因為我們每次都是下發的全量數據，所以mq下發的記錄數據庫中已存在的就更新，沒有的就新增，而數據庫中有的mq下發的沒有的記錄就刪除。至此死鎖問題得到了解決。
到此這篇關于RC級別下MySQL死鎖問題的解決的文章就介紹到這了,更多相關RC級別下MySQL死鎖內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文地址：https://blog.csdn.net/qq_42524262/article/details/123189023
