背景

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

業(yè)務代碼簡化成下面

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

也就是說先update ，select ， insert 這么一個順序

表中存在dm_code ,erp 唯一索引

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

結(jié)論

先說結(jié)論：

重點： 無論哪個事務insert，兩個事務必須都update 完成，只要滿足這個條件，兩個insert執(zhí)行的時候就會報死鎖

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

innodb的行鎖，存在間隙鎖，為啥要去有索引，如果沒有索引，第一個update 就直接進行了表鎖，這樣導致另外一個事務無法進入，就只能進行等待了。

有索引的情況下：

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

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

相關知識梳理

InnoDB有三種行鎖的算法：

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

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

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

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

間隙鎖有些副作用，如果要關閉，一是將會話隔離級別改到RC下，或者開啟 innodb_locks_unsafe_for_binlog（默認是OFF）。

間隙鎖（無論是S還是X）只會阻塞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隔離級別

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)

上面都報錯：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這個證明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記錄的插入操作，事實上，除了對于k=5這條記錄上record lock之外，innoDB對于delete和update在輔助索引(非主鍵索引)上的條件時會對掃過的記錄上間隙鎖，為了防止幻讀，會鎖住k=5這條記錄的前面一條記錄（id=2,k=2）到后面一條記錄(id=10,k=10)之間的區(qū)間，即鎖住k在區(qū)間(2,10)的范圍(如果沒有后一條記錄，一直鎖到正無窮)，至于在邊界k=2及k=10上，由于索引內(nèi)是按照主鍵排序的，不會鎖住(id<2,k=2)但是會鎖住(id>2,k=2)，同理不會鎖住(id>10,k=10)但是會鎖住(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)是按照主鍵排序的，不會鎖住(id<2,k=2)但是會鎖住(id>2,k=2)，同理不會鎖住(id>10,k=10)但是會鎖住(id<10,k=10).

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

線上問題還原

重點： insert 之前兩個回話都執(zhí)行完update

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

解決辦法：

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

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

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

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

最新評論