RC級別下MySQL死鎖問題的解決

2022-03-03 16:00:08

背景

在工作中碰到一次死鎖問題，業務背景是在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死鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

RC級別下MySQL死鎖問題的解決

目錄

背景

死鎖分析

死鎖解決

熱門文章