236
兩個INSERT發生死鎖原因剖析
兩個INSERT也能發生死鎖？貌似不可思議，實際上是正常的。
本文整理過程中，先後向高鵬、王少華、蘇斌等幾位朋友請教確認，感謝。
開始之前，關於鎖、死鎖，我們要先統一下幾點認知：
- 死鎖是由於多個事務相互持有其他事務所需要的鎖，結果導致事務都無法繼續，進而觸發死鎖檢測，其中某個事務會被回滾，釋放相應的鎖，其他事務得以正常繼續；簡言之，就是多個事務之間的鎖等待產生了回路，死循環了；
- 死鎖發生時，會立刻被檢測到，並且回滾其中某個事務，而不會長時間阻塞、等待；
- 從MySQL 5.7.15開始，新增選項 innodb_deadlock_detect，沒記錯的話應該是阿裏團隊率先實現的。當它設置為 OFF 時（默認值是 ON），InnoDB會不檢測死鎖，在高並發場景（例如“秒殺”）業務中特別有用，可以有效提高事務並發性能；
- 在啟用死鎖檢測時，InnoDB默認的最大檢測深度為200，在上麵提到的高並發高競爭場景下，在熱點數據上的鎖等待隊列可能很長，死鎖檢測代價很大。或者當等待隊列中所有的行鎖總數超過 100萬 時，也會被認為認為發生死鎖了，直接觸發死鎖檢測處理機製；
- InnoDB行鎖等待超時默認為50秒，一般建議設置5-10秒就夠了；
- 有時候，可能會口誤把 長時間的行鎖等待 說成是 死鎖，其實二者完全不一樣，不要犯這種常識性口誤。
好了，正式開始今天的案例。
先看測試表DDL：
yejr@imysql.com [yejr]>show create table d\G
********************** 1. row **********************
Table: d
Create Table: CREATE TABLE `d` (
`i` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
yejr@imysql.com [yejr]>select * from d;
+---+
| i |
+---+
| 1 |
+---+
然後我們執行下麵的測試：
這時候我們看下InnoDB STATUS的輸出：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-02 14:59:08 0x700004208000
*** (1) TRANSACTION:
TRANSACTION 274616, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 16, OS thread handle 123145373167616, query id 398 localhost root executing
insert into d select 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274616 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000430b3; asc 0 ;;
2: len 7; hex 3b0000018027a4; asc ; ' ;;
*** (2) TRANSACTION:
TRANSACTION 274617, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 18, OS thread handle 123145371549696, query id 400 localhost root executing
insert into d select 1
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274617 lock mode S(想想，哪裏冒出來的S鎖？)Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000430b3; asc 0 ;;
2: len 7; hex 3b0000018027a4; asc ; ' ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 510 page no 3 n bits 72 index PRIMARY of table `yejr`.`d` trx id 274617 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000430b3; asc 0 ;;
2: len 7; hex 3b0000018027a4; asc ; ' ;;
*** WE ROLL BACK TRANSACTION (2)
從上麵這個輸出來看，我們看到的現場是兩個 insert 請求發生了死鎖。單純看這2個SQL的話，應該是產生鎖等待才對，而不是死鎖。
按照我們常規理解，session1 未 commit 前，應該是持有 i=1 上的record lock(X)，而session2 和 session3 則都在等待這個鎖的釋放。而實際上呢，肯定不是這樣的，否則也不至於發生死鎖了。
關於InnoDB行鎖更詳細的知識點我們以後找時間再說。這次的案例其實在MySQL官方文檔上已經解釋過了，而且也給了演示案例（如本例）。文檔中是這麼說的：
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
【敲黑板、劃重點】If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
劃重點的核心內容是：當需要進行唯一性衝突檢測時，需要先加一個 S 鎖。
這樣的話，上麵案例的加鎖過程就不是之前推測的那樣，而是像下麵這樣了：
下麵是另一個類似的案例：
通過上麵這兩個案例，其實想要告訴大家的是：發生死鎖時，不能隻看現場，還得分析過程，才能知道真正的原因，死鎖發生的原因也並不複雜，但是得能想辦法還原過程。
原文發布時間為：2017-09-05
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾號
最後更新：2017-09-07 12:32:41