一個(gè)mysql死鎖場(chǎng)景實(shí)例分析

前言

最近遇到一個(gè)mysql在RR級(jí)別下的死鎖問(wèn)題，感覺(jué)有點(diǎn)意思，研究了一下，做個(gè)記錄。

涉及知識(shí)點(diǎn)：共享鎖、排他鎖、意向鎖、間隙鎖、插入意向鎖、鎖等待隊(duì)列

場(chǎng)景

隔離級(jí)別：Repeatable-Read

表結(jié)構(gòu)如下

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_a_b(a,b),
 unique key uniq_c(c)
);

初始化數(shù)據(jù)

insert into t(a,b,c) values(1,'1','1');

有A/B兩個(gè)session，按如下順序執(zhí)行兩個(gè)事務(wù)

結(jié)果是

• B執(zhí)行完4之后還是一切正常
• A執(zhí)行5的時(shí)候，被block
• B接著執(zhí)行6，B報(bào)死鎖，B回滾，A插入數(shù)據(jù)

show engine innodb status中可以看到死鎖信息，這里先不貼，先解釋幾種鎖的概念，再來(lái)理解死鎖過(guò)程

共享(S)鎖/互斥(X)鎖

• 共享鎖允許事務(wù)讀取記錄
• 互斥鎖允許事務(wù)讀寫記錄

這兩種其實(shí)是鎖的模式可以和行鎖、間隙鎖混搭，多個(gè)事務(wù)可以同時(shí)持有S鎖，但是只有一個(gè)事務(wù)能持有X鎖

意向鎖

一種表鎖(也是一種鎖模式)，表明有事務(wù)即將給對(duì)應(yīng)表的記錄加S或者X鎖。SELECT ... LOCK IN SHARE MODE會(huì)在給記錄加S鎖之前先給表加IS鎖，SELECT ... FOR UPDATE會(huì)在給記錄加X(jué)鎖之前給表加IX鎖。

這是一種mysql的鎖優(yōu)化策略，并不是很清楚意向鎖的優(yōu)化點(diǎn)在哪里，求大佬指教

兩種鎖的兼容情況如下

行鎖

很簡(jiǎn)單，給對(duì)應(yīng)行加鎖。比如update、select for update、delete等都會(huì)給涉及到的行加上行鎖，防止其他事務(wù)的操作

間隙鎖

在RR隔離級(jí)別下，為了防止幻讀現(xiàn)象，除了給記錄本身，還需要為記錄兩邊的間隙加上間隙鎖。
比如列a上有一個(gè)普通索引，已經(jīng)有了1、5、10三條記錄，select * from t where a=5 for update除了會(huì)給5這條記錄加行鎖，還會(huì)給間隙(1,5)和(5,10)加上間隙鎖，防止其他事務(wù)插入值為5的數(shù)據(jù)造成幻讀。
當(dāng)a上的普通索引變成唯一索引時(shí)，不需要間隙鎖，因?yàn)橹滴ㄒ唬?code>select * from t where a=5 for update不可能讀出兩條記錄來(lái)。

間隙鎖相互兼容，因?yàn)槿绻コ猓聞?wù)A持有左半段(1,5)，事務(wù)B持有右半段(1,10)，那么當(dāng)前面那個(gè)例子中a=5的記錄被刪除時(shí)，理論上左右兩個(gè)間隙鎖得合并成一個(gè)新鎖(1,10)，那么這個(gè)新的大范圍鎖屬于誰(shuí)呢？所以間隙鎖相互兼容，不管是S間隙鎖還是X間隙鎖

插入意向鎖

插入意向鎖其實(shí)是一種特殊的間隙鎖，從前面對(duì)間隙鎖的描述中可以得知，兩個(gè)事務(wù)在真正insert之前可以同時(shí)持有一段間隙的間隙鎖，鎖不住真正insert的這個(gè)動(dòng)作。真正insert之前，mysql還會(huì)嘗試獲取對(duì)應(yīng)記錄的插入意向鎖，表明有在間隙中插入一個(gè)值的意向。

插入意向鎖和間隙鎖互斥，比如事務(wù)1鎖了(1,5)這個(gè)間隙，事務(wù)2就不能獲取到a=3的插入意向鎖，所以需要鎖等待。

死鎖過(guò)程分析

接下來(lái)就可以來(lái)分析前面那個(gè)例子中的死鎖過(guò)程了，先看show engine innodb status

 *** (1) TRANSACTION:
TRANSACTION 5967, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140528848688896, query id 537 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5967 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 5968, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140528848484096, query id 538 192.168.128.1 root update
insert into t(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 72 index uniq_a_b of table `t2`.`t` trx id 5968 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

session A(即TRANSACTION 5967)正在等待記錄(a=1,b='1')之前的插入意向鎖，session B(即TRANSACTION 5968)持有記錄(a=1,b='1')之前的間隙鎖，卻也在等待那個(gè)插入意向鎖。這說(shuō)的什么玩意兒，是不是很詭異？

從頭開始分析過(guò)程

1. A、B分別begin，開始事務(wù)
2. A先執(zhí)行select * from t where a=0 and b='0' for update; ，先加了IX鎖，然后原本意圖為給(0, '0')這條記錄加排他行鎖，但是記錄不存在，所以變成了排他間隙鎖(-∞,1)
3. B再執(zhí)行select * from t where a=0 and b='0' for update; ，也是先加了IX鎖，因?yàn)橛涗洸淮嬖?，所以加上了排他間隙鎖(-∞,1)，但是由于間隙鎖相互兼容，所以沒(méi)有block
4. A執(zhí)行insert into t(a,b) values(0,'0'); ，這時(shí)候，要開始真正insert了，A需要獲得(0,'0')上的插入意向鎖，由于和B持有的(-∞,1)排他間隙鎖沖突，所以鎖等待，進(jìn)入記錄(0,'0')的鎖等待隊(duì)列（雖然記錄并不存在）
5. B執(zhí)行insert into t(a,b) values(0,'0'); ，要獲取插入意向鎖，發(fā)現(xiàn)雖然B自己是持有(-∞,1)的排他間隙鎖，但是A也有，所以進(jìn)入等待隊(duì)列，等待A釋放
6. 叮，死鎖發(fā)生

死鎖信息解讀

事務(wù)1(TRANSACTION 5967)，等待獲得鎖index uniq_a_b of table t2.t trx id 5967 lock_mode X locks gap before rec insert intention waiting，即在唯一索引uniq_a_b上的插入意向鎖(lock_mode X locks gap before rec insert intention)
鎖的邊界為

 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

表明兩行記錄

• 0和1表示uniq_a_b上的值，a=1，b=0x31（即'1'的ascii碼）
• a=1,b='1'對(duì)應(yīng)的主鍵id=1，因?yàn)閕nnodb的索引結(jié)構(gòu)決定的，二級(jí)索引(非主鍵索引)指向主鍵索引，主鍵索引再指向數(shù)據(jù)，所以需要給主鍵加索引

至于int值按位或上的0x80000000就不是很清楚為什么了，需要大佬解讀

事務(wù)2(TRANSACTION 5968)，持有間隙鎖index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec，等待插入意向鎖index uniq_a_b of table t2.t trx id 5968 lock_mode X locks gap before rec insert intention，所以死鎖發(fā)生。

原則上是innodb引擎判斷哪個(gè)事務(wù)回滾代價(jià)小就回滾哪個(gè)事務(wù)，但是具體評(píng)判標(biāo)準(zhǔn)不是很清楚（再一次需要大佬），這里innodb選擇了回滾事務(wù)2。至此，死鎖過(guò)程分析完畢

One More Thing

還沒(méi)完。。。有個(gè)神奇的現(xiàn)象是，如果表結(jié)構(gòu)變成

create table t (
 id int not null primary key AUTO_INCREMENT,
 a int not null default 0,
 b varchar(10) not null default '',
 c varchar(10) not null default '',
 unique key uniq_c(c),
 unique key uniq_a_b(a,b)
);
insert into t(a,b,c) values(1,1,1);

只是把c上的唯一索引uniq_c放到了uniq_a_b前面，那么最后的死鎖信息就變了！

 *** (1) TRANSACTION:
TRANSACTION 5801, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140528848688896, query id 380 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5801 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 5802, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140528848484096, query id 381 192.168.128.1 root update
insert into t2(a,b) values(0,'0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 56 page no 5 n bits 72 index uniq_a_b of table `t2`.`t2` trx id 5802 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc  ;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 72 index uniq_c of table `t2`.`t2` trx id 5802 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 0; hex ; asc ;;
 1: len 4; hex 80000002; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

事務(wù)2等待的鎖由前面的插入意向鎖變成了共享鎖。什么鬼？

由于沒(méi)看過(guò)源碼，只能根據(jù)現(xiàn)象倒推：因?yàn)楸斫Y(jié)構(gòu)上c的唯一索引在(a,b)前面，而插入的時(shí)候沒(méi)指定c的值，用的默認(rèn)值0，innodb需要先去查一下有沒(méi)有0這條記錄，有的話就要報(bào)唯一鍵沖突了，所以先要加S鎖，但是在(0,'0')這條記錄上已經(jīng)有了IX鎖，看一下前面的兼容性矩陣，S鎖和IX鎖互斥，所以也只能鎖等待

總結(jié)

看似一句簡(jiǎn)單的select和insert，底下設(shè)計(jì)非常復(fù)雜的鎖機(jī)制，理解這些鎖機(jī)制有利于寫出高效的SQL(至少是正確的😂)

遺留問(wèn)題：

1. 意向鎖的優(yōu)化點(diǎn)是哪
2. 鎖信息里，行記錄按位或上的0x80000000是啥
3. 鎖互斥的判定順序，場(chǎng)景1中，(0,'0')上有兼容的間隙鎖，也有等待隊(duì)列中的鎖，先判定哪個(gè)？
4. innodb計(jì)算事務(wù)回滾代價(jià)的算法

參考資料

• http://hedengcheng.com/?p=771
• https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
• https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)億速云的支持。