快捷導(dǎo)航

MySQL?insert死鎖問題解決詳細(xì)記錄

更新時(shí)間：2022年11月04日 12:01:58 作者：jhgdike

上周遇到一個(gè)因insert而引發(fā)的死鎖問題,其成因比較令人費(fèi)解,下面這篇文章主要給大家介紹了關(guān)于MySQL?insert死鎖問題解決詳細(xì)記錄的相關(guān)資料,需要的朋友可以參考下

Insert死鎖問題剖析

線上有個(gè)批量的insert … on duplicate key update語句引發(fā)的死鎖問題，查過很多資料并且親自嘗試過后，發(fā)現(xiàn)好多博客說的都是錯(cuò)的，其實(shí)本身只跟insert的順序有關(guān)，在此記錄一下備忘。

前置知識(shí)

X型鎖：排他鎖

S型鎖：共享鎖

行鎖：鎖住一行記錄

Next-Key鎖：左開右閉區(qū)間

Gap鎖：左右開區(qū)間

構(gòu)造死鎖

建表：

CREATE TABLE hero (
    number INT AUTO_INCREMENT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;

構(gòu)造初始數(shù)據(jù)：

INSERT INTO hero VALUES
    (1, 'l劉備', '蜀'),
    (3, 'z諸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孫權(quán)', '吳');

好了，開始了，下面開始兩個(gè)事務(wù)，按順序執(zhí)行：

事務(wù)1

begin:
INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀');

事務(wù)2

begin:
INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀');

事務(wù)1

INSERT INTO hero(name, country) VALUES('d鄧艾', '魏');

來了，它來了，這個(gè)時(shí)候我們就可以注意到事務(wù)2的死鎖報(bào)錯(cuò)了：

# 事務(wù)T2
mysql> INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

原因

請(qǐng)?zhí)砑訄D片描述

T1先插入name值為g關(guān)羽的記錄，可以插入成功，此時(shí)對(duì)應(yīng)的唯一索引記錄被隱式鎖保護(hù)
T2隨后插入name值為g關(guān)羽的記錄（必須為同一條記錄），發(fā)生阻塞，并且T2會(huì)想要獲取一把S型next-key鎖（只有唯一索引才會(huì)發(fā)生）(左開右閉)。此時(shí)T1的隱式鎖轉(zhuǎn)化為顯示鎖（X型行鎖）
T1想要插入d鄧艾的記錄，由于T2的next-key鎖（雖然沒被T2持有，但鎖已存在）而進(jìn)入阻塞等待狀態(tài)，進(jìn)而發(fā)生死鎖

故死鎖產(chǎn)生的原因

T1在等待T2釋放name值為’g關(guān)羽’的二級(jí)索引記錄上的gap鎖。
T2在等待T1釋放name值為’g關(guān)羽’的二級(jí)索引記錄上的X型行鎖。

MySQL 5.7 的死鎖

前提

在比較新的版本中都可以遇見的，只要是insert … on duplicate key update 觸發(fā)了后面的update操作，那么此時(shí)其他的insert語句都會(huì)被阻塞，這主要是為了解決RR下的一些幻讀問題。

示例

在5.7版本中又有一些特殊情況。還是舉例

假如有如下表和數(shù)據(jù)

demo表

id	name	value
1	111	2
1	222	2
1	333	2

此時(shí)，如果事務(wù)1執(zhí)行了：

insert into demo (name, value) VALUES ("333", 1) ON duplicate KEY UPDATE value = value + 1;

事務(wù)2執(zhí)行了：

insert into demo (name, value) VALUES ("223", 1) ON duplicate KEY UPDATE value = value + 1;

事務(wù)3執(zhí)行了：

insert into demo (name, value) VALUES ("224", 1) ON duplicate KEY UPDATE value = value + 1;

那么首先事務(wù)2和事務(wù)3會(huì)被阻塞，然后事務(wù)1提交了，事務(wù)2和事務(wù)3就會(huì)發(fā)生死鎖，其中一個(gè)爆出死鎖的錯(cuò)誤然后失敗，另一個(gè)則成功執(zhí)行。

原因

當(dāng)insert … on duplicate key 執(zhí)行成功之時(shí)，會(huì)在當(dāng)前唯一鍵和之前唯一鍵之間加一個(gè)隱式GAP鎖，如上會(huì)在222和333之間加上GAP鎖，此時(shí)，事務(wù)2和事務(wù)3想插入新數(shù)據(jù)都會(huì)被GAP鎖阻塞，此時(shí)GAP鎖轉(zhuǎn)為顯式，事務(wù)2和事務(wù)3同時(shí)也分別想要獲取X型的插入意向鎖。

然后事務(wù)1提交，此時(shí)GAP鎖并不會(huì)被釋放，由于5.7的bug，事務(wù)2和事務(wù)3都會(huì)拿到GAP鎖，此時(shí)他們?nèi)カ@取插入意向鎖的時(shí)候由于GAP鎖被對(duì)方拿到而矛盾，進(jìn)而死鎖。