INSERT語句引發的死鎖實例分析

這篇文章主要介紹“INSERT語句引發的死鎖實例分析”，在日常操作中，相信很多人在INSERT語句引發的死鎖實例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”INSERT語句引發的死鎖實例分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

兩條一樣的INSERT語句竟然引發了死鎖，這究竟是人性的扭曲，還是道德的淪喪，讓我們不禁感嘆一句：臥槽！這也能死鎖，然后眼中含著悲催的淚水無奈的改起了業務代碼。

好的，在深入分析為啥兩條一樣的INSERT語句也會產生死鎖之前，我們先介紹一些基礎知識。

準備一下環境

為了故事的順利發展，我們新建一個用了無數次的hero表：

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

現在hero表就有了兩個索引（一個唯一二級索引，一個聚簇索引），示意圖如下：

INSERT語句如何加鎖

讀過《MySQL是怎樣運行的：從根兒上理解MySQL》的小伙伴肯定知道，INSERT語句在正常執行時是不會生成鎖結構的，它是靠聚簇索引記錄自帶的trx_id隱藏列來作為隱式鎖來保護記錄的。

但是在一些特殊場景下，INSERT語句還是會生成鎖結構的，我們列舉一下：

1. 待插入記錄的下一條記錄上已經被其他事務加了gap鎖時

每插入一條新記錄，都需要看一下待插入記錄的下一條記錄上是否已經被加了gap鎖，如果已加gap鎖，那INSERT語句應該被阻塞，并生成一個插入意向鎖。

比方說對于hero表來說，事務T1運行在REPEATABLE READ（后續簡稱為RR，后續也會把READ COMMITTED簡稱為RC）隔離級別中，執行了下邊的語句：

# 事務T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l劉備      | 蜀      |
|      3 | z諸葛亮    | 蜀      |
+--------+------------+---------+
2 rows in set (0.02 sec)

這條語句會對主鍵值為1、3、8的這3條記錄都添加X型next-key鎖，不信的話我們使用SHOW ENGINE INNODB STATUS語句看一下加鎖情況，圖中箭頭指向的記錄就是number值為8的記錄：

小貼士：

至于SELECT、DELETE、UPDATE語句如何加鎖，我們已經在之前的文章中分析過了，這里就不再贅述了。

此時事務T2想插入一條主鍵值為4的聚簇索引記錄，那么T2在插入記錄前，首先要定位一下主鍵值為4的聚簇索引記錄在頁面中的位置，發現主鍵值為4的下一條記錄的主鍵值是8，而主鍵值是8的聚簇索引記錄已經被添加了gap鎖（next-key鎖包含了正經記錄鎖和gap鎖），那么事務1就需要進入阻塞狀態，并生成一個類型為插入意向鎖的鎖結構。

我們在事務T2中執行一下INSERT語句驗證一下：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(4, 'g關羽', '蜀');

此時T2進入阻塞狀態，我們再使用SHOW ENGINE INNODB STATUS看一下加鎖情況：

可見T2對主鍵值為8的聚簇索引記錄加了一個插入意向鎖（就是箭頭處指向的lock_mode X locks gap before rec insert intention），并且處在waiting狀態。

好了，驗證過之后，我們再來看看代碼里是如何實現的：

lock_rec_insert_check_and_lock函數用于看一下別的事務是否阻止本次INSERT插入，如果是，那么本事務就給被別的事務添加了gap鎖的記錄生成一個插入意向鎖，具體過程如下：

小貼士：

lock_rec_other_has_conflicting函數用于檢測本次要獲取的鎖和記錄上已有的鎖是否有沖突，有興趣的同學可以看一下。

2. 遇到重復鍵時

如果在插入新記錄時，發現頁面中已有的記錄的主鍵或者唯一二級索引列與待插入記錄的主鍵或者唯一二級索引列值相同（不過可以有多條記錄的唯一二級索引列的值同時為NULL，這里不考慮這種情況了），此時插入新記錄的事務會獲取頁面中已存在的鍵值相同的記錄的鎖。

如果是主鍵值重復，那么：

• 當隔離級別不大于RC時，插入新記錄的事務會給已存在的主鍵值重復的聚簇索引記錄添加S型正經記錄鎖。

• 當隔離級別不小于RR時，插入新記錄的事務會給已存在的主鍵值重復的聚簇索引記錄添加S型next-key鎖。

如果是唯一二級索引列重復，那不論是哪個隔離級別，插入新記錄的事務都會給已存在的二級索引列值重復的二級索引記錄添加S型next-key鎖，再強調一遍，加的是next-key鎖！加的是next-key鎖！加的是next-key鎖！這是rc隔離級別中為數不多的給記錄添加gap鎖的場景。

小貼士：

本來設計InnoDB的大叔并不想在RC隔離級別引入gap鎖，但是由于某些原因，如果不添加gap鎖的話，會讓唯一二級索引中出現多條唯一二級索引列值相同的記錄，這就違背了UNIQUE約束。所以后來設計InnoDB的大叔就很不情愿的在RC隔離級別也引入了gap鎖。

我們也來做一個實驗，現在假設上邊的T1和T2都回滾了，現在將隔離級別調至RC，重新開啟事務進行測試。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
# 事務T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(30, 'x荀彧', '魏');
ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'uk_name'

然后執行SHOW ENGINE INNODB STATUS語句看一下T1加了什么鎖：

可以看到即使現在T1的隔離級別為RC，T1仍然給name列值為'x荀彧'的二級索引記錄添加了S型next-key鎖（圖中紅框中的lock mode S）。

如果我們的INSERT語句還帶有ON DUPLICATE KEY... 這樣的子句，如果遇到主鍵值或者唯一二級索引列值重復的情況，會對B+樹中已存在的相同鍵值的記錄加X型鎖，而不是S型鎖（不過具體鎖的具體類型是和前面描述一樣的）。

好了，又到了看代碼求證時間了，我們看一下吧：

row_ins_scan_sec_index_for_duplicate是檢測唯一二級索引列值是否重復的函數，具體加鎖的代碼如下所示：

如上圖所示，在遇到唯一二級索引列重復的情況時：

• 1號紅框表示對帶有ON DUPLICATE ...子句時的處理方案，具體就是添加X型鎖。

• 2號紅框表示對正常INSERT語句的處理方案，具體就是添加S型鎖。

不過不論是那種情況，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key鎖。

在主鍵重復時INSERT語句的加鎖代碼我們就不列舉了。

3. 外鍵檢查時

當我們向子表中插入記錄時，我們分兩種情況討論：

• 當子表中的外鍵值可以在父表中找到時，那么無論當前事務是什么隔離級別，只需要給父表中對應的記錄添加一個S型正經記錄鎖就好了。

• 當子表中的外鍵值在父表中找不到時：那么如果當前隔離級別不大于RC時，不對父表記錄加鎖；當隔離級別不小于RR時，對父表中該外鍵值所在位置的下一條記錄添加gap鎖。

死鎖要出場了

好了，基礎知識預習完了，該死鎖出場了。

看下邊這個平平無奇的INSERT語句：

INSERT INTO hero(name, country) VALUES('g關羽', '蜀'), ('d鄧艾', '魏');

這個語句用來插入兩條記錄，不論是在RC，還是RR隔離級別，如果兩個事務并發執行它們是有一定幾率觸發死鎖的。為了穩定復現這個死鎖，我們把上邊一條語句拆分成兩條語句：

INSERT INTO hero(name, country) VALUES('g關羽', '蜀');
INSERT INTO hero(name, country) VALUES('d鄧艾', '魏');

拆分前和拆分后起到的作用是相同的，只不過拆分后我們可以人為的控制插入記錄的時機。如果T1和T2的執行順序是這樣的：

也就是：

• T1先插入name值為g關羽的記錄，可以插入成功，此時對應的唯一二級索引記錄被隱式鎖保護，我們執行SHOW ENGINE INNODB STATUS語句，發現啥一個行鎖（row lock）都沒有（因為SHOW ENGINE INNODB STATUS不顯示隱式鎖）：

• 接著T2也插入name值為g關羽的記錄。由于T1已經插入name值為g關羽的記錄，所以T2在插入二級索引記錄時會遇到重復的唯一二級索引列值，此時T2想獲取一個S型next-key鎖，但是T1并未提交，T1插入的name值為g關羽的記錄上的隱式鎖相當于一個X型正經記錄鎖（RC隔離級別），所以T2向獲取S型next-key鎖時會遇到鎖沖突，T2進入阻塞狀態，并且將T1的隱式鎖轉換為顯式鎖（就是幫助T1生成一個正經記錄鎖的鎖結構）。這時我們再執行SHOW ENGINE INNODB STATUS語句：

可見，T1持有的name值為g關羽的隱式鎖已經被轉換為顯式鎖（X型正經記錄鎖，lock_mode X locks rec but not gap）；T2正在等待獲取一個S型next-key鎖（lock mode S waiting）。

• 接著T1再插入一條name值為d鄧艾的記錄。在插入一條記錄時，會在頁面中先定位到這條記錄的位置。在插入name值為d鄧艾的二級索引記錄時，發現現在頁面中的記錄分布情況如下所示：

很顯然，name值為'd鄧艾'的二級索引記錄所在位置的下一條二級索引記錄的name值應該是'g關羽'（按照漢語拼音排序）。那么在T1插入name值為d鄧艾的二級索引記錄時，就需要看一下name值為'g關羽'的二級索引記錄上有沒有被別的事務加gap鎖。

有同學想說：目前只有T2想在name值為'g關羽'的二級索引記錄上添加S型next-key鎖（next-key鎖包含gap鎖），但是T2并沒有獲取到鎖呀，目前正在等待狀態。那么T1不是能順利插入name值為'g關羽'的二級索引記錄么？

我們看一下執行結果：

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

很顯然，觸發了一個死鎖，T2被InnoDB回滾了。

這是為啥呢？T2明明沒有獲取到name值為'g關羽'的二級索引記錄上的S型next-key鎖，為啥T1還不能插入入name值為d鄧艾的二級索引記錄呢？

這我們還得回到代碼上來，看一下插入新記錄時是如何判斷鎖是否沖突的：

看一下畫紅框的注釋，意思是：只要別的事務生成了一個顯式的gap鎖的鎖結構，不論那個事務已經獲取到了該鎖(granted)，還是正在等待獲取（waiting），當前事務的INSERT操作都應該被阻塞。

回到我們的例子中來，就是T2已經在name值為'g關羽'的二級索引記錄上生成了一個S型next-key鎖的鎖結構，雖然T2正在阻塞（尚未獲取鎖），但是T1仍然不能插入name值為d鄧艾的二級索引記錄。

這樣也就解釋了死鎖產生的原因：

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

• T2在等待T1釋放name值為'g關羽'的二級索引記錄上的X型正經記錄鎖。

兩個事務相互等待對方釋放鎖，這樣死鎖也就產生了。

怎么解決這個死鎖問題？

兩個方案：

• 方案一：一個事務中只插入一條記錄。

• 方案二：先插入name值為'd鄧艾'的記錄，再插入name值為'g關羽'的記錄

到此，關于“INSERT語句引發的死鎖實例分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！