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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL實戰(zhàn)窗口函數(shù)SQL分析班級學(xué)生考試成績及生活消費
這篇文章主要為大家介紹了MySQL實戰(zhàn)，利用窗口函數(shù)SQL來分析班級學(xué)生的考試成績及生活消費的示例過程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步2021-10-10
這篇文章主要給大家介紹了關(guān)于SQL面試之WHERE?1=1到底是什么意思的相關(guān)資料,WHERE 1=1子句只是一些開發(fā)人員采用的一種慣性做法,以簡化靜態(tài)和動態(tài)形式的SQL語句的使用,文中介紹的非常詳細,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了服務(wù)器數(shù)據(jù)庫編碼格式問題解決方案的相關(guān)資料,需要的朋友可以參考下2016-11-11
這篇文章主要介紹了MySql如何查看索引并實現(xiàn)優(yōu)化,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-12-12
最左前綴是在使用innodb存儲引擎索引時,需要遵守的法則,本文主要介紹了mysql最左前綴法則導(dǎo)致索引失效的解決,具有一定的參考價值,感興趣的可以了解一下2024-07-07
這篇文章主要介紹了mysql中的int(5)到底有是多長，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要介紹了MySQL存儲引擎總結(jié),本文講解了什么是存儲引擎、MyISAM、InnoDB、MEMORY、MERGE等內(nèi)容,需要的朋友可以參考下2014-09-09
mysql 字符串截取與拆分在很多地方都可以用得到,本文主要介紹了mysql中字符串截取與拆分的實現(xiàn)示例,具有一定的參考價值,感興趣的可以了解一下2023-12-12
這篇文章主要介紹了mysql數(shù)據(jù)庫找不到表的問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
Mysql數(shù)據(jù)庫中的redo?log?寫入策略和binlog?寫入策略
這篇文章主要介紹了Mysql?redo?log?寫入策略和binlog?寫入策略，通過本文學(xué)習(xí)可以了解redo?log的寫入策略是由InnoDB提供了innodb_flush_log_at_trx_commit參數(shù)，binlog的寫入策略，write?和fsync的時機，是由參數(shù)sync_binlog控制的，需要的朋友可以參考下2022-04-04