快捷導(dǎo)航

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

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

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

Insert死鎖問題剖析

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

前置知識

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)', '吳');

好了，開始了，下面開始兩個事務(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鄧艾', '魏');

來了，它來了，這個時候我們就可以注意到事務(wù)2的死鎖報錯了：

# 事務(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

原因

請?zhí)砑訄D片描述

T1先插入name值為g關(guān)羽的記錄，可以插入成功，此時對應(yīng)的唯一索引記錄被隱式鎖保護(hù)
T2隨后插入name值為g關(guān)羽的記錄（必須為同一條記錄），發(fā)生阻塞，并且T2會想要獲取一把S型next-key鎖（只有唯一索引才會發(fā)生）(左開右閉)。此時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)羽’的二級索引記錄上的gap鎖。
T2在等待T1釋放name值為’g關(guān)羽’的二級索引記錄上的X型行鎖。

MySQL 5.7 的死鎖

前提

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

示例

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

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

demo表

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

此時，如果事務(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會被阻塞，然后事務(wù)1提交了，事務(wù)2和事務(wù)3就會發(fā)生死鎖，其中一個爆出死鎖的錯誤然后失敗，另一個則成功執(zhí)行。

原因

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

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