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

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

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

前提

1、在RR隔離級別下。

2、查看間隙鎖是否關閉

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

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

show global variables like "innodb_locks%";

3、show global variables like "autocommit"; 查看事務是否自動提交。

數(shù)據(jù)準備

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

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

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

實驗一、間隙鎖互斥

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

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

這四個區(qū)間。

事務A刪除某個區(qū)間內的一條不存在記錄，獲取到共享間隙鎖，會阻止其他事務B在相應的區(qū)間插入數(shù)據(jù)，因為插入需要獲取排他間隙鎖。

-- 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);

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

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

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

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

如果事務A一直不提交，事務B會一直等待，直到超時，超時后會顯示：

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

實驗二、共享排他鎖死鎖

回到數(shù)據(jù)的初始狀態(tài)，這次需要三個并發(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);

三個事務都試圖往表中插入一條為7的記錄：

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

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

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

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

rollback;
id=7排他鎖釋放。

則B，C會繼續(xù)進行主鍵校驗：

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

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

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

當然，InnoDB有死鎖檢測機制，B和C中的一個事務會插入成功，另一個事務會自動放棄：

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

實驗三、并發(fā)間隙鎖的死鎖

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

回到數(shù)據(jù)的初始狀態(tài)，這次需要兩個并發(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后，會獲得(3, 10)的共享間隙鎖。
B執(zhí)行delete后，也會獲得(3, 10)的共享間隙鎖。
A執(zhí)行insert后，希望獲得(3, 10)的排他間隙鎖，于是會阻塞。
B執(zhí)行insert后，也希望獲得(3, 10)的排他間隙鎖，于是死鎖出現(xiàn)。

仍然使用：