原鏈接 作者:大漠孤煙直 背景及現象 線上生產環境在某些時候經常性的出現資料庫操作死鎖，導致業務人員無法進行操作。經過DBA的分析，是某一張表的insert操作和delete操作發生了死鎖。簡單介紹下資料庫的情況（因為涉及到真實數據，這裡做了模擬，不影響具體的分析和分析的結果。）假設存在如下2張表： ...
作者:大漠孤煙直
背景及現象
線上生產環境在某些時候經常性的出現資料庫操作死鎖，導致業務人員無法進行操作。經過DBA的分析，是某一張表的insert操作和delete操作發生了死鎖。簡單介紹下資料庫的情況（因為涉及到真實數據，這裡做了模擬，不影響具體的分析和分析的結果。）假設存在如下2張表：
Order 表的數據如下：
Customer表的數據如下：
Order和Customer 在實體關係上存在一個關聯，即order實體擁有一個指向customer實體的指針。在資料庫設計的時候，order表的customer_id沒有被設計成一個外鍵，這是因為在對order表做操作的時候不希望外鍵影響資料庫的操作性能。這裡把對外鍵約束的檢查放到了應用程式裡面，即資料庫僅僅當成一個持久化和事務性的保證。同時為了查詢方便，對customer_id做了索引。
在這個模擬業務場景中存在一個業務（因為是模擬的，所以不關心現實中是不是正確），一個客戶擁有的訂單會經常性的發生變化。即這個客戶可能刪除他名下的一些已經存在的訂單，又增加一些新的訂單，或者修改一些存在的訂單，這3種操作可能都會發生在一個請求中。這時，應用人員做了一個不太好的實現：當一個客戶把他這次改動的訂單傳到後臺以後。開發人員不管這次有沒有發生變化都針對這個客戶的訂單進行了先delete後insert的操作，來替代update操作。這裡的實現是可以理解的，因為這一次請求中的訂單可能需要delete,insert和update 三種操作，這樣就要分辨出這批從頁面傳入的數據那些是delete,那些是insert,那些是upadte,還不如統一做成先delete再insert操作。
因為上面業務場景的實現的關係，抽象出來的一個事務中的資料庫操作如下：
Start transaction; // 開事務
Delete from `order` where customer_id = XXX; // 先刪除XXX名下所有訂單
Insert into ‘order’ (customer_id) values (xxx); // 再Inset多條XXX名下的訂單
Insert into ‘order’ (customer_id) values (xxx);
Insert into ‘order’ (customer_id) values (xxx);
……….
Commit; //事務提交
這樣的操作在高併發的情況下，經常性的出現資料庫死鎖。
假設我們進行如下2個事務的操作（客戶3和客戶5都想增加一條自己的訂單記錄）：
T1 ：
T2：
如果在mysql伺服器端，執行順序如下：
T1 Start Transaction ;
T2 Start Transaction ;
T1 delete from `order` where customer_id = 3;
T2 delete from `order` where customer_id = 5;
T1 insert into `order` (customer_id) values (3);
T2 insert into `order` (customer_id) values (5);
…….
這個時候，T1 insert 語句沒有辦法執行，一直在等待一個鎖授權。Mysql 的鎖信息如下：
Thread 5 嘗試在 insert 的時候在等待一個鎖授權，已經等待了10秒。可以看到事務0 10248 持有了2個鎖；事務0 10247 有2個鎖，1個等待鎖授權。整個資料庫只有這2個事務，所以導致insert等待的鎖一定被0 10248持有了。
如果 T2 的insert語句繼續執行，那麼死鎖就發生了，mysql的信息如下：
分析
首先我們先要瞭解下基本的資料庫的鎖的知識。
資料庫為了提高併發性，對於讀和寫進行2種不同的鎖控制，分別稱為共用鎖（S鎖）和排他鎖（X鎖）。這兩種鎖不是mysql獨有的，在一般性的資料庫基本原理介紹中都會提到。同時還有相應的意向鎖的概念。
在mysql的innodb 存儲引擎裡面，使用的是行鎖（S，X），以及表鎖（IS，IX）。這裡4種鎖有個相容矩陣（相容矩陣做什麼用的？不需要解釋了吧，可以參考資料庫基本原理的書）如下：
我們打開鎖監控，然後再具體觀察下在事務執行之中的鎖情況。
A ：T1 Start Transaction ;
B ：T2 Start Transaction ;
C ：T1 delete from `order` where customer_id = 3;
D ：T2 delete from `order` where customer_id = 5;
E ：T1 insert into `order` (customer_id) values (3);
F ：T2 insert into `order` (customer_id) values (5);
…….
我們先按照順序執行到E，下麵是mysql的鎖情況：
T1
T2
我們可以清楚的看到 T1 持有（包括等待授權的）3個鎖：一個是對錶order的IX鎖；一個是對錶order上面的index customer_id的 Gap類型的X鎖； 還有一個是對錶Order上面index customer_id 的 Insert intention 類型的X鎖等待被授權。
T2 持有2個鎖：一個是對錶Order的IX鎖； 一個是對錶order上面的index customer_id 的Gap鎖。
註意 T1 的Gap，Insert intention ,T2 的Gap 都是鎖的同一個地方 “space id 0 page no 198 n bits 80”
這裡介紹下mysql innodb下的鎖類型：
常見的三種類型
拿上面的例子來說
Record 類型，簡單的理解就是執行delete from `order` where id = 1,鎖住的order表裡面id =１的記錄。
Gap 類型：簡單的理解就是執行 delete from `order` where customer_id = 3。這裡在order表裡面沒有customer_id=3 的記錄。但是又由於customer_id存在一個索引，mysql根據索引進行搜索，索引的key是(1,2,6)，3不在這些key裡面而是位於(2,6)之間的gap（間隙）中。Mysql對於(2,6)這個間隙加的鎖就叫做Gap鎖。這個例子中的間隙一共有(-∞,1),(1,2),(2,6),(6,+∞)這4個。註意gap只鎖間隙不鎖記錄。
Next-Key 類型 : 簡單的理解就是 Gap + 下一個 Record 。拿上面Gap的例子來說的話，鎖住的就是(2,6]。這裡包括了6這個記錄。
除開以上三種常見的鎖類型，還有一種對於Insert語句的特殊鎖類型
也就是說insert語句會對插入的行加一個X鎖，但是在插入這個行的過程之前，會設置一個Insert intention的Gap鎖，叫做Insert intention鎖。
以上面的例子來說，在執行 insert into `order` (customer_id) values (3)的時候，由於存在customer_id的索引，所以會對這個索引的(2,6)增加一個Insert Intention 類型的X鎖。
瞭解了這些之後，我們回到上面的例子。
這裡我們清楚的知道 --“註意 T1 的Gap，Insert intention ,T2 的Gap 都是鎖的同一個地方 “space id 0 page no 198 n bits 80””—3個鎖鎖住同一個地方的原因了。因為customer_id = 3 和customer_id =5 都是屬於同一個gap（2,6）。
T1 持有 gap (2,6) X鎖，同時有個 insert intention (2,6)的X鎖在等待gap(2,6)的X鎖的釋放；
T2 持有 gap(2,6) X鎖。
這就是導致T1的insert 語句執行不下去的真正原因。 當T2的insert 語句執行的時候，（即F語句）可以預見，T2也會有個 insert intention(2,6)的X鎖在等待gap(2,6)的X鎖的釋放。這樣就形成了死鎖。
分析到這裡就結束了麽？好像那個地方有點不對。T1本身不就是擁有了一個gap(2,6)的X鎖麽？等等，為什麼在T1擁有gap(2,6)X鎖的情況下，T2還可以擁有gap(2,6)X鎖？X鎖同X鎖不是不相容的麽（看看相容矩陣）？
是的，看看上面的相容矩陣。IX與IX相容，X與X不相容。T1和T2 同時擁有對於表order的IX鎖是可以理解的；但是T1和T2 同時擁有對於表order的index customer_id的X鎖似乎就無法理解了。按照相容矩陣的說法，在T2 執行D語句的時候就應該被block，因為它需要獲取Gap(2,6)的X鎖，但是這個鎖已經被T1執行C語句的時候持有了，所以只有在T1事務執行完以後，T2才能繼續執行，按照這個順序下來，是不會發生死鎖的。
Mysql 或者說是 Innodb 是不是弄錯了什麼？
其實，我們分析的沒有錯，Mysql也沒有弄錯，唯一錯的地方是官方文檔上面沒有介紹除了這個（IS,IX,S,X）的相容矩陣外，在Mysql實現內部還有一個更加精確的被稱為“precise mode”的相容矩陣。（該矩陣沒有出現在官方文檔上，是有人通過Mysql lock0lock.c:lock_rec_has_to_wait源代碼推測出來的。）下麵這個是“precise mode”的相容矩陣：（這個相容矩陣發生在X與X，S與X不相容的情況下再進行比對的）
G I R N (已經存在的鎖,包括等待的鎖)
G + + + +
I - + + -
R + + - -
N + + - -
+ 代表相容， -代表不相容. I代表插入意圖鎖,
G代表Gap鎖，I代表插入意圖鎖,R代表記錄鎖，N代表Next-Key鎖.
(http://www.mysqlops.com/2012/05/19/locks_in_innodb.html#more-3169)
這裡需要註意的一點是，存在Insert Intention 鎖時，申請Gap鎖是允許的；但是存在Gap鎖時，申請Insert Intention鎖時是被阻止的。
回到上面的例子，這下就可以解釋清楚了。
執行C語句完畢，T1持有了Gap(2,6)的X鎖；
執行D語句，T2 申請Gap(2,6)的X鎖，根據“precise mode”相容矩陣，該申請被授權，所以T2 持有了Gap(2,6)的X鎖。
執行E語句，T1 申請Insert Intention (2,6)的X鎖，根據“precise mode”相容矩陣，由於T2持有Gap(2,6)的X鎖，該申請被T2 block。
執行F語句，T2 申請 Insert Intention(2,6)的X鎖，根據“precise mode”相容矩陣，由於T1持有Gap(2,6)的X鎖，該申請被T1 block。
這裡一個死鎖很明顯的出現，T1與T2都持有一個鎖，同時都在等對方釋放一個鎖。到這裡，整個死鎖的原因分析清楚了。
解決
我們分析清楚了死鎖形成的原因，就很好去解決這個問題了。可以看出T1，T2 都是持有了Gap 鎖，等待insert intention被授權。
只要消除了Gap鎖，這個死鎖就解決了。方案有幾種：
A delete 表 order上面的index customer_id。這樣在delete的時候就不會產生Gap鎖，insert 的時候也不會有insert intention鎖。不過對於查詢會有影響。
B 在delete的時候，不讓事務獲取到Gap鎖。比如，在執行delete from `order` where customer_id = 3 ;之前，先通過資料庫查詢 select * from `order` where customer_id = 3; 看是否存在記錄。不存在記錄這不執行delete操作。因為insert總是要發生，delete則不是必須一定要發生的。
後記
在真實解決線上這個問題的時候，走過了一些彎路，某些現象也讓我認為是找到了真實的原因，其實那隻是虛幻的假象。
因為死鎖發生在Insert 語句上面，一開始我們認為是`order` 表上面的主鍵id自增鎖引起的（有點主觀臆斷，病急亂投醫）。然後，我們把`order`上面的主鍵id轉換成類似Oracle的sequence 序列，通過應用程式給予其賦值id。大家可以去嘗試操作下，把一張表的主鍵id的auto_increment 給改掉，是多麼噁心的一個操作(不是說多複雜，而是說這個操作的方式讓有“操作潔癖”的人無法忍受)。等到上線以後，確實似乎好了很多，但是根源還是存在，只是它現在不想咬你。又過了段時間，系統壓力上來了，這個問題又暴露出來了。正是應了那句“屋漏偏招連夜雨”，禍不單行，當問題出現的時候，開始我們還是認為是insert語句生成id的方式造成的，慢慢的對於這個問題的分析越來越詳盡，終於意識到“id 生成方式”是替罪羔羊，真正的原因在於過多的無意義的delete操作的時候，這個問題才算是解決。
為了避免大家對主鍵id自增鎖的偏見，我簡單介紹下主鍵id自增鎖的機制，也算是我對冤枉它的一種補償吧。
主鍵自增鎖基本上是通過 select Max(id) from table for update來實現的。很明顯，for update 加的是表鎖而且是X的。和其他的鎖的區別就在於它的釋放時機，其他的鎖是跟隨事務的。自增鎖不跟著事務走，而是跟著那條Insert語句走。
在Mysql 5.1.22版本以後，增加了 innodb_autoinc_lock_mode的參數，來調整主鍵自增鎖的性能。這個時候不一定會進行鎖表操作了，有可能就是直接在記憶體裡面算好id值。在這種情況下麵，mysql會對Insert語句進行分類，不同的分類在不同的參數 innodb_autoinc_lock_mode 下麵會有不同的自增方式。大家可以參考《mysql技術內幕 InnoDB存儲引擎》 。
