MySQL insert死鎖問題解決詳細記錄

2022-11-05 14:01:49

Insert死鎖問題剖析

線上有個批次的insert … on duplicate key update語句引發的死鎖問題，查過很多資料並且親自嘗試過後，發現好多部落格說的都是錯的，其實本身只跟insert的順序有關，在此記錄一下備忘。

前置知識

X型鎖：排他鎖

S型鎖：共用鎖

行鎖：鎖住一行記錄

Next-Key鎖：左開右閉區間

Gap鎖：左右開區間

構造死鎖

建表：

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;

構造初始資料：

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

好了，開始了，下面開始兩個事務，按順序執行：

事務1

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

事務2

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

事務1

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

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

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

原因

T1先插入name值為g關羽的記錄，可以插入成功，此時對應的唯一索引記錄被隱式鎖保護
T2隨後插入name值為g關羽的記錄（必須為同一條記錄），發生阻塞，並且T2會想要獲取一把S型next-key鎖（只有唯一索引才會發生）(左開右閉)。此時T1的隱式鎖轉化為顯示鎖（X型行鎖）
T1想要插入d鄧艾的記錄，由於T2的next-key鎖（雖然沒被T2持有，但鎖已存在）而進入阻塞等待狀態，進而發生死鎖

故死鎖產生的原因

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

MySQL 5.7 的死鎖

前提

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

範例

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

假如有如下表和資料

demo表

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

此時，如果事務1執行了：

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

事務2執行了：

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

事務3執行了：

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

那麼首先事務2和事務3會被阻塞，然後事務1提交了，事務2和事務3就會發生死鎖，其中一個爆出死鎖的錯誤然後失敗，另一個則成功執行。

原因

當insert … on duplicate key 執行成功之時，會在當前唯一鍵和之前唯一鍵之間加一個隱式GAP鎖，如上會在222和333之間加上GAP鎖，此時，事務2和事務3想插入新資料都會被GAP鎖阻塞，此時GAP鎖轉為顯式，事務2和事務3同時也分別想要獲取X型的插入意向鎖。

然後事務1提交，此時GAP鎖並不會被釋放，由於5.7的bug，事務2和事務3都會拿到GAP鎖，此時他們去獲取插入意向鎖的時候由於GAP鎖被對方拿到而矛盾，進而死鎖。

解決方案

網上有很多方法，這裡我提出一個另類的想法。

我們可以先用非事務的insert ignore去初始化資料，後面在用事務的update操作去更新。

參考：https://zhuanlan.zhihu.com/p/457191971

總結

到此這篇關於MySQL insert死鎖問題的文章就介紹到這了,更多相關MySQL insert死鎖問題內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！