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ā)生死鎖，默認(rèn)回滾后請求的事務(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官方壓力測試工具 Mysqlslap的使用方法，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
這篇文章主要介紹了mysql中的跨庫關(guān)聯(lián)查詢方法,需要的朋友可以參考下2017-05-05
windows下安裝mysql-8.0.18-winx64的教程(圖文詳解)
這篇文章主要介紹了windows下安裝mysql-8.0.18-winx64,需要的朋友可以參考下2019-12-12
這篇文章主要介紹了Mysql如何刪除數(shù)據(jù)庫表中的某一列，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-06-06
SQLyog?是一個快速而簡潔的圖形化管理MYSQL數(shù)據(jù)庫的工具，它能夠在任何地點有效地管理你的數(shù)據(jù)庫，這篇文章主要介紹了SQLyog12.08安裝詳細(xì)教程,需要的朋友可以參考下2023-04-04
這篇文章主要給大家介紹了關(guān)于Mysql join連接查詢的相關(guān)資料，文中介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-10-10
mysql decimal數(shù)據(jù)類型轉(zhuǎn)換的實現(xiàn)
這篇文章主要介紹了mysql decimal數(shù)據(jù)類型轉(zhuǎn)換的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
在面試中被提問，mysql 中的 user 表的 id 默認(rèn)是自增的，但是數(shù)據(jù)庫存儲的結(jié)果卻不是連續(xù)的，你知道是什么原因嗎，本文就詳細(xì)的介紹一下，感興趣的可以了解一下2021-09-09
mysql中url時區(qū)的陷阱該如何規(guī)避詳解
最近在工作中發(fā)現(xiàn)一個問題，是關(guān)于mysql中url時區(qū)的，發(fā)現(xiàn)這個陷阱如果大家不注意可能都會遇到，所以給大家總結(jié)下，這篇文章主要給大家介紹了關(guān)于mysql中url時區(qū)的陷阱該如何規(guī)避的相關(guān)資料，需要的朋友可以參考借鑒，下面來一起看看吧。2017-08-08
今天在寫sql語句的時候，想確認(rèn)下mysql的不等于運算符是用什么符號表示的2013-08-08