一個mysql死鎖場景實例分析
最近遇到一個mysql在RR級別下的死鎖問題,感覺有點意思,研究了一下,做個記錄。
涉及知識點:共享鎖、排他鎖、意向鎖、間隙鎖、插入意向鎖、鎖等待隊列
場景
隔離級別:Repeatable-Read
表結構如下
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兩個session,按如下順序執(zhí)行兩個事務
結果是
- B執(zhí)行完4之后還是一切正常
- A執(zhí)行5的時候,被block
- B接著執(zhí)行6,B報死鎖,B回滾,A插入數(shù)據(jù)
show engine innodb status
中可以看到死鎖信息,這里先不貼,先解釋幾種鎖的概念,再來理解死鎖過程
共享(S)鎖/互斥(X)鎖
- 共享鎖允許事務讀取記錄
- 互斥鎖允許事務讀寫記錄
這兩種其實是鎖的模式可以和行鎖、間隙鎖混搭,多個事務可以同時持有S鎖,但是只有一個事務能持有X鎖
意向鎖
一種表鎖(也是一種鎖模式),表明有事務即將給對應表的記錄加S或者X鎖。SELECT ... LOCK IN SHARE MODE會在給記錄加S鎖之前先給表加IS鎖,SELECT ... FOR UPDATE會在給記錄加X鎖之前給表加IX鎖。
這是一種mysql的鎖優(yōu)化策略,并不是很清楚意向鎖的優(yōu)化點在哪里,求大佬指教
兩種鎖的兼容情況如下
行鎖
很簡單,給對應行加鎖。比如update、select for update、delete等都會給涉及到的行加上行鎖,防止其他事務的操作
間隙鎖
在RR隔離級別下,為了防止幻讀現(xiàn)象,除了給記錄本身,還需要為記錄兩邊的間隙加上間隙鎖。
比如列a上有一個普通索引,已經(jīng)有了1、5、10三條記錄,select * from t where a=5 for update
除了會給5這條記錄加行鎖,還會給間隙(1,5)和(5,10)加上間隙鎖,防止其他事務插入值為5的數(shù)據(jù)造成幻讀。
當a上的普通索引變成唯一索引時,不需要間隙鎖,因為值唯一,select * from t where a=5 for update
不可能讀出兩條記錄來。
間隙鎖相互兼容,因為如果互斥,事務A持有左半段(1,5),事務B持有右半段(1,10),那么當前面那個例子中a=5的記錄被刪除時,理論上左右兩個間隙鎖得合并成一個新鎖(1,10),那么這個新的大范圍鎖屬于誰呢?所以間隙鎖相互兼容,不管是S間隙鎖還是X間隙鎖
插入意向鎖
插入意向鎖其實是一種特殊的間隙鎖,從前面對間隙鎖的描述中可以得知,兩個事務在真正insert之前可以同時持有一段間隙的間隙鎖,鎖不住真正insert的這個動作。真正insert之前,mysql還會嘗試獲取對應記錄的插入意向鎖,表明有在間隙中插入一個值的意向。
插入意向鎖和間隙鎖互斥,比如事務1鎖了(1,5)這個間隙,事務2就不能獲取到a=3的插入意向鎖,所以需要鎖等待。
死鎖過程分析
接下來就可以來分析前面那個例子中的死鎖過程了,先看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')之前的間隙鎖,卻也在等待那個插入意向鎖。這說的什么玩意兒,是不是很詭異?
從頭開始分析過程
- A、B分別begin,開始事務
- A先執(zhí)行
select * from t where a=0 and b='0' for update;
,先加了IX鎖,然后原本意圖為給(0, '0')這條記錄加排他行鎖,但是記錄不存在,所以變成了排他間隙鎖(-∞,1) - B再執(zhí)行
select * from t where a=0 and b='0' for update;
,也是先加了IX鎖,因為記錄不存在,所以加上了排他間隙鎖(-∞,1),但是由于間隙鎖相互兼容,所以沒有block - A執(zhí)行
insert into t(a,b) values(0,'0');
,這時候,要開始真正insert了,A需要獲得(0,'0')上的插入意向鎖,由于和B持有的(-∞,1)排他間隙鎖沖突,所以鎖等待,進入記錄(0,'0')的鎖等待隊列(雖然記錄并不存在) - B執(zhí)行
insert into t(a,b) values(0,'0');
,要獲取插入意向鎖,發(fā)現(xiàn)雖然B自己是持有(-∞,1)的排他間隙鎖,但是A也有,所以進入等待隊列,等待A釋放 - 叮,死鎖發(fā)生
死鎖信息解讀
事務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'對應的主鍵id=1,因為innodb的索引結構決定的,二級索引(非主鍵索引)指向主鍵索引,主鍵索引再指向數(shù)據(jù),所以需要給主鍵加索引
至于int值按位或上的0x80000000就不是很清楚為什么了,需要大佬解讀
事務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引擎判斷哪個事務回滾代價小就回滾哪個事務,但是具體評判標準不是很清楚(再一次需要大佬),這里innodb選擇了回滾事務2。至此,死鎖過程分析完畢
One More Thing
還沒完。。。有個神奇的現(xiàn)象是,如果表結構變成
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)
事務2等待的鎖由前面的插入意向鎖變成了共享鎖。什么鬼?
由于沒看過源碼,只能根據(jù)現(xiàn)象倒推:因為表結構上c的唯一索引在(a,b)前面,而插入的時候沒指定c的值,用的默認值0,innodb需要先去查一下有沒有0這條記錄,有的話就要報唯一鍵沖突了,所以先要加S鎖,但是在(0,'0')這條記錄上已經(jīng)有了IX鎖,看一下前面的兼容性矩陣,S鎖和IX鎖互斥,所以也只能鎖等待
總結
看似一句簡單的select和insert,底下設計非常復雜的鎖機制,理解這些鎖機制有利于寫出高效的SQL(至少是正確的😂)
遺留問題:
- 意向鎖的優(yōu)化點是哪
- 鎖信息里,行記錄按位或上的0x80000000是啥
- 鎖互斥的判定順序,場景1中,(0,'0')上有兼容的間隙鎖,也有等待隊列中的鎖,先判定哪個?
- innodb計算事務回滾代價的算法
參考資料
- 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
總結
以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對本站的支持。
版權聲明:本站文章來源標注為YINGSOO的內容版權均為本站所有,歡迎引用、轉載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內容涉嫌侵權,請聯(lián)系alex-e#qq.com處理。