MySQL如何處理InnoDB并發(fā)事務(wù)中的間隙鎖死鎖

更新時(shí)間：2023年10月15日 11:24:51 作者：jacheut

這篇文章主要為大家介紹了MySQL如何處理InnoDB并發(fā)事務(wù)中的間隙鎖死鎖,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

前提

1、在RR隔離級(jí)別下。

2、查看間隙鎖是否關(guān)閉

區(qū)間鎖(間隙鎖，臨鍵鎖)是InnoDB特有施加在索引記錄區(qū)間的鎖，MySQL5.6可以手動(dòng)關(guān)閉區(qū)間鎖，它由innodb_locks_unsafe_for_binlog參數(shù)控制：

設(shè)置為ON，表示關(guān)閉區(qū)間鎖，此時(shí)一致性會(huì)被破壞（所以是unsafe）
設(shè)置為OFF，表示開啟區(qū)間鎖

show global variables like "innodb_locks%";

3、show global variables like "autocommit"; 查看事務(wù)是否自動(dòng)提交。

數(shù)據(jù)準(zhǔn)備

InnoDB的行鎖都是實(shí)現(xiàn)在索引上的，實(shí)驗(yàn)可以使用主鍵，建表時(shí)設(shè)定為innodb引擎：

create table t (
id int(10) primary key
)engine=innodb;
-- 插入一些實(shí)驗(yàn)數(shù)據(jù)：
start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;

這是實(shí)驗(yàn)的初始狀態(tài)，不同實(shí)驗(yàn)開始之初，都默認(rèn)回到初始狀態(tài)。

實(shí)驗(yàn)一、間隙鎖互斥

開啟區(qū)間鎖，RR的隔離級(jí)別下，上例會(huì)有：

(-infinity, 1)
(1, 3)
(3, 10)
(10, infinity)

這四個(gè)區(qū)間。

事務(wù)A刪除某個(gè)區(qū)間內(nèi)的一條不存在記錄，獲取到共享間隙鎖，會(huì)阻止其他事務(wù)B在相應(yīng)的區(qū)間插入數(shù)據(jù)，因?yàn)椴迦胄枰@取排他間隙鎖。

-- session A：
set session autocommit=0;
start transaction;
delete from t where id=5;

-- session B：
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);

事務(wù)B插入的值：0, 2, 12都不在(3, 10)區(qū)間內(nèi)，能夠成功插入，而7在(3, 10)這個(gè)區(qū)間內(nèi)，會(huì)阻塞。

可以使用：show engine innodb status; 來查看鎖的情況

insert into t values(7); 正在等待共享間隙鎖的釋放。

如果事務(wù)A提交或者回滾，事務(wù)B就能夠獲得相應(yīng)的鎖，以繼續(xù)執(zhí)行。

如果事務(wù)A一直不提交，事務(wù)B會(huì)一直等待，直到超時(shí)，超時(shí)后會(huì)顯示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

實(shí)驗(yàn)二、共享排他鎖死鎖

回到數(shù)據(jù)的初始狀態(tài)，這次需要三個(gè)并發(fā)的session。

-- session A先執(zhí)行：
set session autocommit=0;
start transaction;
insert into t values(7);
 
-- session B后執(zhí)行：
set session autocommit=0;
start transaction;
insert into t values(7);
 
-- session C最后執(zhí)行：
set session autocommit=0;
start transaction;
insert into t values(7);

三個(gè)事務(wù)都試圖往表中插入一條為7的記錄：

(1)A先執(zhí)行，插入成功，并獲取id=7的排他鎖；

(2)B后執(zhí)行，需要進(jìn)行PK校驗(yàn)，故需要先獲取id=7的共享鎖，阻塞；

(3)C后執(zhí)行，也需要進(jìn)行PK校驗(yàn)，也要先獲取id=7的共享鎖，也阻塞；

如果此時(shí)，session A執(zhí)行：

rollback;
id=7排他鎖釋放。

則B，C會(huì)繼續(xù)進(jìn)行主鍵校驗(yàn)：

(1)B會(huì)獲取到id=7共享鎖，主鍵未互斥；

(2)C也會(huì)獲取到id=7共享鎖，主鍵未互斥；

B和C要想插入成功，必須獲得id=7的排他鎖，但由于雙方都已經(jīng)獲取到id=7的共享鎖，它們都無法獲取到彼此的排他鎖，死鎖就出現(xiàn)了。

當(dāng)然，InnoDB有死鎖檢測機(jī)制，B和C中的一個(gè)事務(wù)會(huì)插入成功，另一個(gè)事務(wù)會(huì)自動(dòng)放棄：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

實(shí)驗(yàn)三、并發(fā)間隙鎖的死鎖

共享排他鎖，在并發(fā)量插入相同記錄的情況下會(huì)出現(xiàn)，相應(yīng)的案例比較容易分析。而并發(fā)的間隙鎖死鎖，是比較難定位的。

回到數(shù)據(jù)的初始狀態(tài)，這次需要兩個(gè)并發(fā)的session，其SQL執(zhí)行序列如下：

A：set session autocommit=0;
A：start transaction;
A：delete from t where id=6;
         B：set session autocommit=0;
         B：start transaction;
         B：delete from t where id=7;
A：insert into t values(5);
         B：insert into t values(8);

A執(zhí)行delete后，會(huì)獲得(3, 10)的共享間隙鎖。
B執(zhí)行delete后，也會(huì)獲得(3, 10)的共享間隙鎖。
A執(zhí)行insert后，希望獲得(3, 10)的排他間隙鎖，于是會(huì)阻塞。
B執(zhí)行insert后，也希望獲得(3, 10)的排他間隙鎖，于是死鎖出現(xiàn)。

仍然使用：