背景

線上經(jīng)常偶發(fā)死鎖問(wèn)題，當(dāng)時(shí)處理一張表，也沒(méi)有聯(lián)表處理，但是有兩個(gè)mq入口，并且消息體存在一樣的情況，頻率還不是很低，這么一個(gè)背景，我非常容易懷疑到，兩個(gè)消息同時(shí)近到這一個(gè)事務(wù)里面導(dǎo)致的，但是是偶發(fā)的，又模擬不出來(lái)什么場(chǎng)景會(huì)導(dǎo)致死鎖，只能進(jìn)行代碼分析，問(wèn)題還原的方式去排查問(wèn)題

業(yè)務(wù)代碼簡(jiǎn)化成下面

begin
update test set yn = 0 where dm_code = "3";
SELECT  * from test where dm_code = '3'
INSERT INTO demand_flow_followers (dm_code, erp )
values
('3', 'a')
,
('3', 'b')
,
('3', 'c')

也就是說(shuō)先update ，select ， insert 這么一個(gè)順序

表中存在dm_code ,erp 唯一索引

如果不存在索引 第一行update 會(huì)導(dǎo)致行鎖升級(jí)為表鎖，反而不會(huì)導(dǎo)致問(wèn)題出現(xiàn)，但是并發(fā)太差

結(jié)論

先說(shuō)結(jié)論：

重點(diǎn)： 無(wú)論哪個(gè)事務(wù)insert，兩個(gè)事務(wù)必須都update 完成，只要滿足這個(gè)條件，兩個(gè)insert執(zhí)行的時(shí)候就會(huì)報(bào)死鎖

原因：我先按照自己的理解解釋下：

innodb的行鎖，存在間隙鎖，為啥要去有索引，如果沒(méi)有索引，第一個(gè)update 就直接進(jìn)行了表鎖，這樣導(dǎo)致另外一個(gè)事務(wù)無(wú)法進(jìn)入，就只能進(jìn)行等待了。

有索引的情況下：

兩個(gè)事務(wù)都執(zhí)行update，都拿到了[當(dāng)前值，+∞) 的鎖（記錄鎖+間隙鎖），（update的時(shí)候，無(wú)數(shù)據(jù)命中）

第一個(gè)insert時(shí)，希望等待另外一個(gè)事務(wù)釋放鎖。第二個(gè)事務(wù)希望第一個(gè)事務(wù)釋放鎖，因此出現(xiàn)了死鎖問(wèn)題

相關(guān)知識(shí)梳理

InnoDB有三種行鎖的算法：

1.Record Lock：是加在索引記錄上的。

2.Gap Lock（間隙鎖）：對(duì)索引記錄間的范圍加鎖，或者加在最后一個(gè)索引記錄的前面或者后面

3.Next-Key Lock：前兩種鎖的結(jié)合，鎖定一個(gè)范圍，并且鎖定記錄本身，主要目的是解決幻讀的問(wèn)題。

間隙鎖主要是防止幻象讀，用在Repeated-Read（簡(jiǎn)稱RR）隔離級(jí)別下。在Read-Commited（簡(jiǎn)稱RC）下，一般沒(méi)有間隙鎖（有外鍵情況下例外，此處不考慮）。間隙鎖還用于statement based replication

間隙鎖有些副作用，如果要關(guān)閉，一是將會(huì)話隔離級(jí)別改到RC下，或者開(kāi)啟 innodb_locks_unsafe_for_binlog（默認(rèn)是OFF）。

間隙鎖（無(wú)論是S還是X）只會(huì)阻塞insert操作。

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `k` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT into test values(2,2),(5,5),(10,10)

select @@global.tx_isolation, @@tx_isolation;

RR隔離級(jí)別

delete from test where k=5;

session2

insert into test (id,k) values (3,3)
insert into test (id,k) values (4,4)
insert into test (id,k) values (6,6)
insert into test (id,k) values (7,7)
insert into test (id,k) values (8,8)
insert into test (id,k) values (9,9)

上面都報(bào)錯(cuò)：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這個(gè)證明id （3，5）都被間隙鎖鎖住了

insert into test (id,k) values (1,1)
insert into test (id,k) values (11,11)
delete from test where id in (1,11)

（3，5） 區(qū)間之外都可以執(zhí)行insert，delete操作

可以看到，delete k=5的記錄阻塞了k=3、4、5、6、7、8、9記錄的插入操作，事實(shí)上，除了對(duì)于k=5這條記錄上record lock之外，innoDB對(duì)于delete和update在輔助索引(非主鍵索引)上的條件時(shí)會(huì)對(duì)掃過(guò)的記錄上間隙鎖，為了防止幻讀，會(huì)鎖住k=5這條記錄的前面一條記錄（id=2,k=2）到后面一條記錄(id=10,k=10)之間的區(qū)間，即鎖住k在區(qū)間(2,10)的范圍(如果沒(méi)有后一條記錄，一直鎖到正無(wú)窮)，至于在邊界k=2及k=10上，由于索引內(nèi)是按照主鍵排序的，不會(huì)鎖住(id<2,k=2)但是會(huì)鎖住(id>2,k=2)，同理不會(huì)鎖住(id>10,k=10)但是會(huì)鎖住(id<10,k=10).

insert into test (id,k) values (1,2) ok
insert into test (id,k) values (11,2) no
insert into test (id,k) values (11,9) no
insert into test (id,k) values (11,10) ok
insert into test (id,k) values (1,10) no
insert into test (id,k) values (11,10) ok

由于索引內(nèi)是按照主鍵排序的，不會(huì)鎖住(id<2,k=2)但是會(huì)鎖住(id>2,k=2)，同理不會(huì)鎖住(id>10,k=10)但是會(huì)鎖住(id<10,k=10).

值得注意的是，delete和update在唯一索引（primary key/unique key）上更新存在的記錄時(shí)只會(huì)上行級(jí)記錄鎖（record key），而在唯一索引上更新不存在的記錄時(shí)同輔助索引一樣會(huì)上間隙鎖；在上例中,delete id=5只會(huì)在(id=5,k=5)這條記錄上上X鎖，而delete id=7卻會(huì)鎖住（id>5&&id<10）這個(gè)區(qū)間。

線上問(wèn)題還原

重點(diǎn)： insert 之前兩個(gè)回話都執(zhí)行完update

SQL 錯(cuò)誤 [1213] [40001]: Deadlock found when trying to get lock; try restarting transaction

解決辦法：

避免更新或者刪除不存在的記錄，雖然更新存在的記錄也會(huì)產(chǎn)生間隙鎖，但是間隙鎖鎖住的范圍會(huì)更??；

更新不存在的記錄會(huì)鎖住意想不到的區(qū)間范圍，極其容易導(dǎo)致死鎖問(wèn)題

這些僅僅是解決問(wèn)題的一個(gè)小的技巧，不能從根本上解決問(wèn)題，如果想從根本上解決就從代碼級(jí)別上加鎖，這樣避免了這種問(wèn)題，但是同時(shí)并發(fā)就小了，根據(jù)自己的實(shí)際情況進(jìn)行定奪方案

以上就是解決MySQL innoDB間隙鎖產(chǎn)生的死鎖問(wèn)題的詳細(xì)內(nèi)容，更多關(guān)于MySQL innoDB產(chǎn)生死鎖的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論