解決MySQL共享鎖引發(fā)的死鎖問題

更新時(shí)間：2023年11月08日 09:22:04 作者：程序員清風(fēng)

這篇文章主要給大家介紹了MySQL共享鎖引發(fā)的死鎖問題的原因和解決辦法,文中通過代碼示例和圖文介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下

問題現(xiàn)象

在一個(gè)事務(wù)內(nèi)只會(huì)鎖一行的數(shù)據(jù)，沒有鎖多行數(shù)據(jù)才會(huì)出現(xiàn)的順序問題，但是會(huì)偶爾報(bào)個(gè)Deadlock

事務(wù)內(nèi)sql執(zhí)行順序如下：

前提

數(shù)據(jù)庫隔離級(jí)別為 RC

建表語句：

CREATE TABLE `user_money_account_test` (
  `id` bigint(20) NOT NULL COMMENT '主鍵',
  `userId` bigint(20) NOT NULL COMMENT '用戶id',
  `accountTypeId` int(11) NOT NULL COMMENT '賬戶類型id',
  `currencyId` int(11) NOT NULL COMMENT '貨幣id',
  `moneyBalance` decimal(30,4) NOT NULL COMMENT '貨幣余額',
  `createdTime` bigint(20) NOT NULL,
  `updatedTime` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId_accountTypeId` (`userId`,`accountTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  dbpartition by hash(`userId`) tbpartition by hash(`userId`) tbpartitions 4;

問題初探之一 —— 死鎖日志

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-11-03 15:54:57 0x7f556ddca700
*** (1) TRANSACTION: TRANSACTION 7103074779, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 1528, OS thread handle 140005316216576, query id 109102299 100.104.192.64 athyxrnr statistics /*DRDS /10.11.43.140/117db74e43c34001-4/ */SELECT `user_money_account`.`id`, `user_money_account`.`userId`, `user_money_account`.`accountTypeId`, `user_money_account`.`currencyId`, `user_money_account`.`moneyBalance`, `user_money_account`.`createdTime`, `user_money_account`.`updatedTime` FROM `user_money_account_eGz3_008` AS `user_money_account` WHERE ((`user_money_account`.`userId` = 474232840) AND (`user_money_account`.`accountTypeId` = 202)) FOR UPDATE
?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 526 page no 8711 n bits 704 index userId_accountTypeId of table `bolt_money_account_e5l6_0001`.`user_money_account_egz3_008` trx id 7103074779 lock_mode X locks rec but not gap waiting Record lock, heap no 318 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000001c443808; asc D8 ;; 1: len 4; hex 800000ca; asc ;; 2: len 8; hex 8139b4e738c01045; asc 9 8 E;;
?
?
?
?
*** (2) TRANSACTION: TRANSACTION 7103074777, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 6870, OS thread handle 140004892124928, query id 109102300 100.104.192.122 athyxrnr statistics /*DRDS /10.11.107.138/117db74e42312000-4/ */SELECT `user_money_account`.`id`, `user_money_account`.`userId`, `user_money_account`.`accountTypeId`, `user_money_account`.`currencyId`, `user_money_account`.`moneyBalance`, `user_money_account`.`createdTime`, `user_money_account`.`updatedTime` FROM `user_money_account_eGz3_008` AS `user_money_account` WHERE ((`user_money_account`.`userId` = 474232840) AND (`user_money_account`.`accountTypeId` = 202)) FOR UPDATE
?
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 526 page no 8711 n bits 704 index userId_accountTypeId of table `bolt_money_account_e5l6_0001`.`user_money_account_egz3_008` trx id 7103074777 lock mode S Record lock, heap no 318 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000001c443808; asc D8 ;; 1: len 4; hex 800000ca; asc ;; 2: len 8; hex 8139b4e738c01045; asc 9 8 E;;
?
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 526 page no 8711 n bits 704 index userId_accountTypeId of table `bolt_money_account_e5l6_0001`.`user_money_account_egz3_008` trx id 7103074777 lock_mode X locks rec but not gap waiting Record lock, heap no 318 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000001c443808; asc D8 ;; 1: len 4; hex 800000ca; asc ;; 2: len 8; hex 8139b4e738c01045; asc 9 8 E;

問題一:

答:Tx1獲取到了某行數(shù)據(jù)的間隙共享鎖，之后Tx1和Tx2都去獲取該行的排他行鎖導(dǎo)致的死鎖

問題探究之二:沒有顯式地lock in share mode 為什么會(huì)有共享間隙鎖？

問題2.1 同時(shí)進(jìn)行的只有兩個(gè)事務(wù)嗎？

問題2.1:答:第三個(gè)事務(wù)可能是產(chǎn)生共享間隙鎖的關(guān)鍵

事務(wù)內(nèi)sql語句還原：

時(shí)間點(diǎn)1時(shí)的鎖狀態(tài)：

select * from information_schema.innodb_trx;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

時(shí)間點(diǎn)2時(shí)的鎖狀態(tài)：

select * from information_schema.innodb_trx;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

問題二:答:同時(shí)進(jìn)行的兩個(gè)事務(wù)同時(shí)insert帶來的共享間隙鎖

問題追問之三 —— 為什么同時(shí)進(jìn)行的兩個(gè)事務(wù)同時(shí)insert會(huì)產(chǎn)生共享間隙鎖

問題三答 ——

INSERT操作在插入或更新記錄時(shí)，檢查到Duplicate key（或者有一個(gè)被標(biāo)記刪除的duplicate key），對(duì)于普通的INSERT/UPDATE，會(huì)加LOCK_S鎖，而對(duì)于類似REPLACE INTO或者INSERT ..ON DUPLICATE這樣的SQL加的是X鎖。而針對(duì)不同的索引類型也有所不同：

對(duì)于聚集索引（參閱函數(shù)row_ins_duplicate_error_in_clust），隔離級(jí)別小于等于RC時(shí)，加的是LOCK_REC_NOT_GAP類似的S或者X記錄鎖。否則加LOCK_ORDINARY類型的記錄鎖（NEXT-KEY LOCK）
對(duì)于二級(jí)唯一索引，若檢查到重復(fù)鍵，當(dāng)前版本總是加LOCK_ORDINARY類型的記錄鎖(函數(shù) row_ins_scan_sec_index_for_duplicate)。實(shí)際上按照RC的設(shè)計(jì)理念，不應(yīng)該加GAP鎖（bug#68021），官方也事實(shí)上嘗試修復(fù)過一次，即對(duì)于RC隔離級(jí)別加上LOCK_REC_NOT_GAP，但卻引入了另外一個(gè)問題，導(dǎo)致二級(jí)索引的唯一約束失效(bug#73170)，由于這個(gè)嚴(yán)重bug，官方很快又把這個(gè)fix給revert掉了。

問題擴(kuò)展之四 —— sql執(zhí)行時(shí)機(jī)發(fā)生變化時(shí)的效果

會(huì)產(chǎn)生什么結(jié)果呢？？

一句話結(jié)論

Tx1和Tx2兩個(gè)事務(wù)并行insert相同唯一索引的數(shù)據(jù)，導(dǎo)致先執(zhí)行insert的Tx1獲得了排他鎖，Tx2等待獲得共享鎖；

在Tx1釋放排他鎖的時(shí)候，Tx2拿到了共享間隙鎖，但此時(shí)另一個(gè)事務(wù)Tx3請(qǐng)求該行的排他鎖，被阻塞；

之后Tx2也去請(qǐng)求該行的排他鎖，至此，形成了Tx2和Tx3組成的環(huán)形等待，形成死鎖