兩條一樣的INSERT語句竟然引發(fā)了死鎖?
建議看本文前先看一下：
兩條一樣的INSERT語句竟然引發(fā)了死鎖，這究竟是人性的扭曲，還是道德的淪喪，讓我們不禁感嘆一句：臥槽！這也能死鎖，然后眼中含著悲催的淚水無奈的改起了業(yè)務(wù)代碼。
好的，在深入分析為啥兩條一樣的INSERT語句也會(huì)產(chǎn)生死鎖之前，我們先介紹一些基礎(chǔ)知識(shí)。
準(zhǔn)備一下環(huán)境
為了故事的順利發(fā)展，我們新建一個(gè)用了無數(shù)次的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;
然后向這個(gè)表里插入幾條記錄：
INSERT INTO hero VALUES(1, 'l劉備', '蜀'),(3, 'z諸葛亮', '蜀'),(8, 'c曹操', '魏'),(15, 'x荀彧', '魏'),(20, 's孫權(quán)', '吳');
現(xiàn)在hero表就有了兩個(gè)索引（一個(gè)唯一二級(jí)索引，一個(gè)聚簇索引），示意圖如下：
INSERT語句如何加鎖
讀過《MySQL是怎樣運(yùn)行的：從根兒上理解MySQL》的小伙伴肯定知道，INSERT語句在正常執(zhí)行時(shí)是不會(huì)生成鎖結(jié)構(gòu)的，它是靠聚簇索引記錄自帶的trx_id隱藏列來作為隱式鎖來保護(hù)記錄的。
但是在一些特殊場(chǎng)景下，INSERT語句還是會(huì)生成鎖結(jié)構(gòu)的，我們列舉一下：
1. 待插入記錄的下一條記錄上已經(jīng)被其他事務(wù)加了gap鎖時(shí)
每插入一條新記錄，都需要看一下待插入記錄的下一條記錄上是否已經(jīng)被加了gap鎖，如果已加gap鎖，那INSERT語句應(yīng)該被阻塞，并生成一個(gè)插入意向鎖。
比方說對(duì)于hero表來說，事務(wù)T1運(yùn)行在REPEATABLE READ（后續(xù)簡(jiǎn)稱為RR，后續(xù)也會(huì)把READ COMMITTED簡(jiǎn)稱為RC）隔離級(jí)別中，執(zhí)行了下邊的語句：
# 事務(wù)T1mysql> 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)
這條語句會(huì)對(duì)主鍵值為1、3、8的這3條記錄都添加X型next-key鎖，不信的話我們使用SHOW ENGINE INNODB STATUS語句看一下加鎖情況，圖中箭頭指向的記錄就是number值為8的記錄：
小貼士：
至于SELECT、DELETE、UPDATE語句如何加鎖，我們已經(jīng)在之前的文章中分析過了，這里就不再贅述了。
此時(shí)事務(wù)T2想插入一條主鍵值為4的聚簇索引記錄，那么T2在插入記錄前，首先要定位一下主鍵值為4的聚簇索引記錄在頁面中的位置，發(fā)現(xiàn)主鍵值為4的下一條記錄的主鍵值是8，而主鍵值是8的聚簇索引記錄已經(jīng)被添加了gap鎖（next-key鎖包含了正經(jīng)記錄鎖和gap鎖），那么事務(wù)1就需要進(jìn)入阻塞狀態(tài)，并生成一個(gè)類型為插入意向鎖的鎖結(jié)構(gòu)。
我們?cè)谑聞?wù)T2中執(zhí)行一下INSERT語句驗(yàn)證一下：
mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO hero VALUES(4, 'g關(guān)羽', '蜀');
此時(shí)T2進(jìn)入阻塞狀態(tài)，我們?cè)偈褂肧HOW ENGINE INNODB STATUS看一下加鎖情況：
可見T2對(duì)主鍵值為8的聚簇索引記錄加了一個(gè)插入意向鎖（就是箭頭處指向的lock_mode X locks gap before rec insert intention），并且處在waiting狀態(tài)。
好了，驗(yàn)證過之后，我們?cè)賮砜纯创a里是如何實(shí)現(xiàn)的：
lock_rec_insert_check_and_lock函數(shù)用于看一下別的事務(wù)是否阻止本次INSERT插入，如果是，那么本事務(wù)就給被別的事務(wù)添加了gap鎖的記錄生成一個(gè)插入意向鎖，具體過程如下：
小貼士：
lock_rec_other_has_conflicting函數(shù)用于檢測(cè)本次要獲取的鎖和記錄上已有的鎖是否有沖突，有興趣的同學(xué)可以看一下。
2. 遇到重復(fù)鍵時(shí)
如果在插入新記錄時(shí)，發(fā)現(xiàn)頁面中已有的記錄的主鍵或者唯一二級(jí)索引列與待插入記錄的主鍵或者唯一二級(jí)索引列值相同（不過可以有多條記錄的唯一二級(jí)索引列的值同時(shí)為NULL，這里不考慮這種情況了），此時(shí)插入新記錄的事務(wù)會(huì)獲取頁面中已存在的鍵值相同的記錄的鎖。
如果是主鍵值重復(fù)，那么：
?當(dāng)隔離級(jí)別不大于RC時(shí)，插入新記錄的事務(wù)會(huì)給已存在的主鍵值重復(fù)的聚簇索引記錄添加S型正經(jīng)記錄鎖。?當(dāng)隔離級(jí)別不小于RR時(shí)，插入新記錄的事務(wù)會(huì)給已存在的主鍵值重復(fù)的聚簇索引記錄添加S型next-key鎖。
如果是唯一二級(jí)索引列重復(fù)，那不論是哪個(gè)隔離級(jí)別，插入新記錄的事務(wù)都會(huì)給已存在的二級(jí)索引列值重復(fù)的二級(jí)索引記錄添加S型next-key鎖，再強(qiáng)調(diào)一遍，加的是next-key鎖！加的是next-key鎖！加的是next-key鎖！這是rc隔離級(jí)別中為數(shù)不多的給記錄添加gap鎖的場(chǎng)景。
小貼士：
本來設(shè)計(jì)InnoDB的大叔并不想在RC隔離級(jí)別引入gap鎖，但是由于某些原因，如果不添加gap鎖的話，會(huì)讓唯一二級(jí)索引中出現(xiàn)多條唯一二級(jí)索引列值相同的記錄，這就違背了UNIQUE約束。所以后來設(shè)計(jì)InnoDB的大叔就很不情愿的在RC隔離級(jí)別也引入了gap鎖。
我們也來做一個(gè)實(shí)驗(yàn)，現(xiàn)在假設(shè)上邊的T1和T2都回滾了，現(xiàn)在將隔離級(jí)別調(diào)至RC，重新開啟事務(wù)進(jìn)行測(cè)試。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.01 sec)# 事務(wù)T1mysql> 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'
然后執(zhí)行SHOW ENGINE INNODB STATUS語句看一下T1加了什么鎖：
可以看到即使現(xiàn)在T1的隔離級(jí)別為RC，T1仍然給name列值為'x荀彧'的二級(jí)索引記錄添加了S型next-key鎖（圖中紅框中的lock mode S）。
如果我們的INSERT語句還帶有ON DUPLICATE KEY...?這樣的子句，如果遇到主鍵值或者唯一二級(jí)索引列值重復(fù)的情況，會(huì)對(duì)B+樹中已存在的相同鍵值的記錄加X型鎖，而不是S型鎖（不過具體鎖的具體類型是和前面描述一樣的）。
好了，又到了看代碼求證時(shí)間了，我們看一下吧：
row_ins_scan_sec_index_for_duplicate是檢測(cè)唯一二級(jí)索引列值是否重復(fù)的函數(shù)，具體加鎖的代碼如下所示：
如上圖所示，在遇到唯一二級(jí)索引列重復(fù)的情況時(shí)：
?1號(hào)紅框表示對(duì)帶有ON DUPLICATE ...子句時(shí)的處理方案，具體就是添加X型鎖。?2號(hào)紅框表示對(duì)正常INSERT語句的處理方案，具體就是添加S型鎖。
不過不論是那種情況，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key鎖。
在主鍵重復(fù)時(shí)INSERT語句的加鎖代碼我們就不列舉了。
3. 外鍵檢查時(shí)
當(dāng)我們向子表中插入記錄時(shí)，我們分兩種情況討論：
?當(dāng)子表中的外鍵值可以在父表中找到時(shí)，那么無論當(dāng)前事務(wù)是什么隔離級(jí)別，只需要給父表中對(duì)應(yīng)的記錄添加一個(gè)S型正經(jīng)記錄鎖就好了。
?當(dāng)子表中的外鍵值在父表中找不到時(shí)：那么如果當(dāng)前隔離級(jí)別不大于RC時(shí)，不對(duì)父表記錄加鎖；當(dāng)隔離級(jí)別不小于RR時(shí)，對(duì)父表中該外鍵值所在位置的下一條記錄添加gap鎖。
由于外鍵不太常用，例子和代碼就都不舉例了，有興趣的小伙伴可以打開《MySQL是怎樣運(yùn)行的：從根兒上理解MySQL》查看例子。
死鎖要出場(chǎng)了
好了，基礎(chǔ)知識(shí)預(yù)習(xí)完了，該死鎖出場(chǎng)了。
看下邊這個(gè)平平無奇的INSERT語句：
INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀'), ('d鄧艾', '魏');這個(gè)語句用來插入兩條記錄，不論是在RC，還是RR隔離級(jí)別，如果兩個(gè)事務(wù)并發(fā)執(zhí)行它們是有一定幾率觸發(fā)死鎖的。為了穩(wěn)定復(fù)現(xiàn)這個(gè)死鎖，我們把上邊一條語句拆分成兩條語句：
INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀');INSERT INTO hero(name, country) VALUES('d鄧艾', '魏');
拆分前和拆分后起到的作用是相同的，只不過拆分后我們可以人為的控制插入記錄的時(shí)機(jī)。如果T1和T2的執(zhí)行順序是這樣的：
也就是：
?T1先插入name值為g關(guān)羽的記錄，可以插入成功，此時(shí)對(duì)應(yīng)的唯一二級(jí)索引記錄被隱式鎖保護(hù)，我們執(zhí)行SHOW ENGINE INNODB STATUS語句，發(fā)現(xiàn)啥一個(gè)行鎖（row lock）都沒有（因?yàn)镾HOW ENGINE INNODB STATUS不顯示隱式鎖）：
?接著T2也插入name值為g關(guān)羽的記錄。由于T1已經(jīng)插入name值為g關(guān)羽的記錄，所以T2在插入二級(jí)索引記錄時(shí)會(huì)遇到重復(fù)的唯一二級(jí)索引列值，此時(shí)T2想獲取一個(gè)S型next-key鎖，但是T1并未提交，T1插入的name值為g關(guān)羽的記錄上的隱式鎖相當(dāng)于一個(gè)X型正經(jīng)記錄鎖（RC隔離級(jí)別），所以T2向獲取S型next-key鎖時(shí)會(huì)遇到鎖沖突，T2進(jìn)入阻塞狀態(tài)，并且將T1的隱式鎖轉(zhuǎn)換為顯式鎖（就是幫助T1生成一個(gè)正經(jīng)記錄鎖的鎖結(jié)構(gòu)）。這時(shí)我們?cè)賵?zhí)行SHOW ENGINE INNODB STATUS語句：
可見，T1持有的name值為g關(guān)羽的隱式鎖已經(jīng)被轉(zhuǎn)換為顯式鎖（X型正經(jīng)記錄鎖，lock_mode X locks rec but not gap）；T2正在等待獲取一個(gè)S型next-key鎖（lock mode S waiting）。
?接著T1再插入一條name值為d鄧艾的記錄。在插入一條記錄時(shí)，會(huì)在頁面中先定位到這條記錄的位置。在插入name值為d鄧艾的二級(jí)索引記錄時(shí)，發(fā)現(xiàn)現(xiàn)在頁面中的記錄分布情況如下所示：
很顯然，name值為'd鄧艾'的二級(jí)索引記錄所在位置的下一條二級(jí)索引記錄的name值應(yīng)該是'g關(guān)羽'（按照漢語拼音排序）。那么在T1插入name值為d鄧艾的二級(jí)索引記錄時(shí)，就需要看一下name值為'g關(guān)羽'的二級(jí)索引記錄上有沒有被別的事務(wù)加gap鎖。
有同學(xué)想說：目前只有T2想在name值為'g關(guān)羽'的二級(jí)索引記錄上添加S型next-key鎖（next-key鎖包含gap鎖），但是T2并沒有獲取到鎖呀，目前正在等待狀態(tài)。那么T1不是能順利插入name值為'g關(guān)羽'的二級(jí)索引記錄么？
我們看一下執(zhí)行結(jié)果：
# 事務(wù)T2mysql> INSERT INTO hero(name, country) VALUES('g關(guān)羽', '蜀');ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
很顯然，觸發(fā)了一個(gè)死鎖，T2被InnoDB回滾了。
這是為啥呢？T2明明沒有獲取到name值為'g關(guān)羽'的二級(jí)索引記錄上的S型next-key鎖，為啥T1還不能插入入name值為d鄧艾的二級(jí)索引記錄呢？
這我們還得回到代碼上來，看一下插入新記錄時(shí)是如何判斷鎖是否沖突的：
看一下畫紅框的注釋，意思是：只要?jiǎng)e的事務(wù)生成了一個(gè)顯式的gap鎖的鎖結(jié)構(gòu)，不論那個(gè)事務(wù)已經(jīng)獲取到了該鎖(granted)，還是正在等待獲取（waiting），當(dāng)前事務(wù)的INSERT操作都應(yīng)該被阻塞。
回到我們的例子中來，就是T2已經(jīng)在name值為'g關(guān)羽'的二級(jí)索引記錄上生成了一個(gè)S型next-key鎖的鎖結(jié)構(gòu)，雖然T2正在阻塞（尚未獲取鎖），但是T1仍然不能插入name值為d鄧艾的二級(jí)索引記錄。
這樣也就解釋了死鎖產(chǎn)生的原因：
?T1在等待T2釋放name值為'g關(guān)羽'的二級(jí)索引記錄上的gap鎖。?T2在等待T1釋放name值為'g關(guān)羽'的二級(jí)索引記錄上的X型正經(jīng)記錄鎖。
兩個(gè)事務(wù)相互等待對(duì)方釋放鎖，這樣死鎖也就產(chǎn)生了。
怎么解決這個(gè)死鎖問題？
兩個(gè)方案：
?方案一：一個(gè)事務(wù)中只插入一條記錄。?方案二：先插入name值為'd鄧艾'的記錄，再插入name值為'g關(guān)羽'的記錄
為啥這兩個(gè)方案可行？屏幕前的大腦瓜是不是也該轉(zhuǎn)一下分析一波唄~