RC級別下MySQL死鎖問題的解決
背景
在工作中碰到一次死鎖問題，業務背景是在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死鎖內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- Mysql鎖內部實現機制之C源碼解析
- MySQL InnoDB鎖類型及鎖原理實例解析
- MySQL鎖等待與死鎖問題分析
- MySQL臟讀幻讀不可重復讀及事務的隔離級別和MVCC、LBCC實現
- MySQL中出現lock wait timeout exceeded問題及解決