前言

  最近遇到一次MySQL死鎖的問題，也算是少見的一件事情。公司的MySQL隔離級別是Read Commited，已經沒有了gap lock，而且代碼里的sql都再簡單不過，沒有顯式加鎖的sql語句。因此抽出時間看了一下原因。
  分析具體問題之前，先整體的了解一下MySQL的加鎖邏輯，之后再分析起來就游刃有余了：

MySQL的鎖

  為什么MySQL要加鎖呢？OLTP數據庫離不開事務，事務也離不開并發操作下一致性的問題。現代數據庫解決事務的并發控制有兩種辦法，2PL和MVCC[1]。
  2PL是加鎖方案的代表，就是將數據操作分為加鎖和解鎖兩個階段，任何數據操作都會將訪問對象加上鎖，后續對這個對象的數據操作就會被阻塞直到鎖釋放（事務提交）。傳統數據庫大都是用2PL來實現并發控制的。
  MVCC(多版本并發控制)是無鎖方案的代表，通過對數據庫每一次變更記錄版本快照，實現讀-寫互不阻塞，寫-寫是否阻塞取決于具體實現（例如postgres的SERIALIZABLE級別下寫-寫互不阻塞，發生沖突拋出異常）。
  對于MySQL(innoDB)來說，是通過MVCC實現讀-寫并發控制，又是通過2PL寫-寫并發控制的，因此依然保留著(悲觀)鎖這個概念，既然有悲觀鎖，自然就有可能產生死鎖問題。
  MySQL的事務我之前在這篇文章里做過一些粗淺的理解：傳送門 （痛心的是網上大部分資料還是顯示mySql在RR隔離級別下會幻讀。。）

那么MySQL會如何加鎖呢[2]：

MySQL鎖的模式：

• 共享/排它鎖 (S鎖/X鎖) (Shared and Exclusive Locks)
  • S鎖與X鎖沖突，S鎖與S鎖不沖突，X鎖和X鎖沖突
    • 鎖沖突意味著無法獲取鎖的事務需要等待，鎖被釋放后才能繼續。當然也有可能等待超時或檢測出死鎖
  • 快照讀(普通select …)不加鎖
  • select..lock in share mode / Serializable下的select 會加S鎖
  • select..for update / 寫操作(insert update delete) 會加X鎖
  • 上述的鎖都是行級別的，S鎖和X鎖同樣可以加在表級別上，對應的語句分別是LOCK TABLE … READ和LOCK TABLE … WRITE
• 意向鎖 (IS鎖/IX鎖) (Intention Locks)
  • 意向鎖是表級別的鎖，用來標識該表上面有數據被鎖住（或即將被鎖）
  • 一個事務在獲取（任何一行/或者全表）S鎖之前，一定會先在所在的表上加IS鎖。同理，獲取X鎖之前一定會加上IX鎖。
  • 意向鎖提出的目的，就是要標識這個表上面有鎖，這樣一來，對于表級別鎖的請求（LOCK TABLE …），就可以直接判斷是否有鎖沖突，而不需要逐行檢查鎖的狀態了。從更大的角度來看，意向鎖就是為了實現不同粒度的鎖共存，每次加鎖都需要先對上面更粗粒度的數據結構加意向鎖，用來表達“這個數據結構中存在被鎖住的數據”。

其兼容矩陣如下（+表示兼容，-表示沖突）：

  上面提到的鎖的模式，指的是如何鎖住數據，各種模式之間是否兼容；下面提到的鎖的類型，定義的是具體鎖在哪里。二者并不沖突，比如record lock可以分成record x lock和record s lock。

MySQL鎖的類型：

• Record Locks
  • 對單條索引記錄上加的鎖。準確的說，鎖是加在索引上的而非行上。因為innodb一定會有一個聚簇索引，因此最終的行鎖都會落到聚簇索引上。
  • 可以加在聚簇索引或者二級索引上。
• Gap Locks
  • gap lock是對索引間隙加的鎖，可以是在一條索引記錄之前，也可以在一條索引記錄之后。
  • gap lock的唯一作用，就是阻止其他事務向鎖住的gap里插入數據。
  • gap lock下的所有鎖的模式都是兼容的，比如同一個位置的gap s lock和gap x lock是可以共存的。其作用也是完全相同的。
  • 在READ COMMITTED隔離級別下，不會使用gap lock。因此下文關于gap lock的加鎖，對于RC隔離級別可以自動忽略。
• Next-Key Locks
  • Next-Key lock與record lock加鎖的粒度一樣，都是加在一條索引記錄上的。一個next-key lock=對應的索引記錄的record lock+該索引前面的間隙的gap lock
  • 雖然說Next-Key Lock代表著record lock+前一個間隙的gap lock，在必要的情況下，最后一條記錄后面的gap也有可能作為一條單獨的gap lock被鎖住[3]。
  • 由于鎖住的是前面的間隙，所以有些資料也會用左開右閉的區間來表示next-key lock，例如(1,3]
• Insert Intention Locks
  • Insert Intention Lock是一種特殊的間隙鎖，執行insert之前會向插入的間隙加上Insert Intention Lock
  • Insert Intention Lock與已有的gap lock沖突，因此gap lock鎖住的間隙是不能插入數據的
  • Insert Intention Lock與Insert Intention Lock之間不沖突，因此允許了同時向同一個間隙插入不同主鍵的數據

其兼容矩陣如下，+表示兼容，-表示沖突：

如何查看事務的加鎖情況

  當存在鎖沖突/等待時，比較方便的查看鎖沖突的方式：

1
2
3
4

// innodb_locks記錄了所有innodb正在等待的鎖，和被等待的鎖
select * from information_schema.innodb_locks;
// innodb_lock_waits記錄了所有innodb鎖的持有和等待關系
select * from information_schema.innodb_lock_waits'

  結果如上圖，可以看到當前事務id 4579持有著’new_table’表的聚簇索引=3的X鎖。事務id 4580正在等待’new_table’表的聚簇索引=3的X鎖。

  但是上述方式只能看到存在鎖沖突的記錄，不能看到每個事務實際鎖住的記錄和范圍。因此更通用的辦法是，直接打開innodb的鎖監控，在控制臺查看詳細鎖狀態：

1
2

mysql> set global innodb_status_output=ON; // 可選。將監控輸出到log_error輸出中，15秒刷新一次
mysql> set global innodb_status_output_locks=ON; // 輸出的內容包含鎖的詳細信息

  通過show engine innodb status;語句，可以輸出每個事務當前持有的鎖結果，常見的結果類型解釋如下。死鎖日志也會記錄如下的鎖記錄，因此可以用同樣的方式來讀MySQL的死鎖日志。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

// 表示事務4641對表`sys`.`new_table`持有了IX鎖
TABLE LOCK table `sys`.`new_table` trx id 4641 lock mode IX
// space id=38，space id可以唯一確定一張表，表示了鎖所在的表
// page no 3，表示鎖所在的頁號
// index PRIMARY 表示鎖位于名為PRIMARY的索引上
// lock_mode X locks rec but not gap 表示x record lock
// 下方的數據表示了被鎖定的索引數據，最上面一行代表索引列的十六進制值，在這里表示的就是id=3的數據
RECORD LOCKS space id 38 page no 3 n bits 80 index PRIMARY of table `sys`.`new_table` trx id 4641 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 0000000011e9; asc       ;;
 2: len 7; hex a70000011b0128; asc       (;;
 3: len 4; hex 8000012c; asc    ,;;
 4: len 1; hex 63; asc c;;
 5: len 4; hex 80000006; asc     ;;
 6: len 3; hex 636363; asc ccc;;
 7: len 2; hex 3333; asc 33;;
// lock_mode X表示的是next-key lock，即當前記錄的record lock+前一個間隙的gap lock
// 這個鎖在名為idx1的索引上，對應的索引列的值為100（hex 64對應十進制），對應聚簇索引的值為1
RECORD LOCKS space id 38 page no 5 n bits 80 index idx1 of table `sys`.`new_table` trx id 4643 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000064; asc    d;;
 1: len 4; hex 00000001; asc     ;;
 
// lock_mode X locks gap before rec表示的是對應索引記錄前一個間隙的gap lock
RECORD LOCKS space id 38 page no 5 n bits 80 index idx1 of table `sys`.`new_table` trx id 4643 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 00000002; asc     ;;

不同語句的加鎖情況

以下實驗數據基于MySQL 5.7。
假設已知一張表my_table，id列為主鍵。

對該表進行讀寫操作，可能產生的加鎖情況如下（僅考慮隔離級別為RR和RC）：

1. 查詢命中聚簇索引（主鍵索引）

1.1 如果是精確查詢，那么會在命中的索引上加record lock。
例如：

1
2
3
4
5

// 在id=1的聚簇索引上加X鎖
update my_table set name='a' where id=1;  
// 在id=1的聚簇索引上加S鎖
select * from my_table where id=1 lock in share mode;

1.2 如果是范圍查詢，那么

• 1.2.1 在RC隔離級別下，會在所有命中的行的聚簇索引上加record locks（只鎖行）

1
2
3
4
5

// 在id=8和10的聚簇索引上加X鎖
update my_table set name='a' where id>7;  
// 在id=1的聚簇索引上加X鎖
update my_table set name='a' where id<=1;

• 1.2.2 在RR隔離級別下，會在所有命中的行的聚簇索引上加next-key locks（鎖住行和間隙）。最后命中的索引的后一條記錄，也會被加上next-key lock。

1
2
3
4
5
6
7

// 在id=8、10(、+∞)的聚簇索引上加X鎖
// 在(5,8)(8,10)(10,+∞)加gap lock
update my_table set name='a' where id>7;  
// 在id=1、5的聚簇索引上加X鎖
// 在(-∞,1)(1,5)加gap lock
update my_table set name='a' where id<=1;

1.3 如果查詢結果為空，那么

• 1.2.1 在RC隔離級別下，什么也不會鎖

• 1.2.2 在RR隔離級別下，會鎖住查詢目標所在的間隙。

1
2

// 在(1,5)加gap lock
update my_table set name='a' where id=2;

2. 查詢命中唯一索引

假設上述表中，num列加了唯一索引
2.1 如果是精確查詢，那么會在命中的唯一索引，和對應的聚簇索引上加record lock。

1
2
3

// 在num=100的唯一索引上加X鎖
// 并在id=1的聚簇索引上加X鎖
update my_table set name='a' where num=100;

2.2 如果是范圍查詢，那么

• 2.2.1 在RC隔離級別下，會在所有命中的唯一索引和聚簇索引上加record lock。同2.1
• 2.2.2 在RR隔離級別下，會在所有命中的行的唯一索引上加next-key locks。最后命中的索引的后一條記錄，也會被加上next-key lock。

1
2
3
4

// 在num=100和num=200的唯一索引上加X鎖
// 并在id=1和id=5的聚簇索引上加X鎖
// 并在唯一索引的間隙(-∞,100)(100,200)加gap lock
update my_table set name='a' where num<150;

2.3 如果查詢結果為空，同1.3。唯一差別在于，此時加的gap lock是位于唯一索引上的。

3. 查詢命中二級索引（非唯一索引）

假設上述表中，name列加了普通二級索引，num列沒有索引
3.1 如果是精確查詢，那么

• 3.1.1 在RC隔離級別下，同2.1，對命中的二級索引和聚簇索引加record lock

1
2
3

// 在name='bbb'的兩條索引記錄上加X鎖
// 并在id=5和id=8的聚簇索引上加X鎖
update my_table set num=10 where name='bbb';

• 3.1.2 在RR隔離級別下，會在命中的二級索引上加next-key lock，最后命中的索引的后面的間隙會加上gap lock。對應的聚簇索引上加record lock。

1
2
3
4

// 在name='bbb'的兩條索引記錄上加X鎖
// 并在id=5和id=8的聚簇索引上加X鎖
// 并在二級索引的間隙('aaa','bbb')('bbb','bbb')('bbb','ccc')加gap lock
update my_table set num=10 where name='bbb';

3.2 范圍查詢、模糊查詢的情況比較復雜，此處不詳述。可以用上述方法自己實驗。

4. 查詢沒有命中索引

假設上述表中，name列加了普通二級索引，num列沒有索引
4.1 如果查詢條件沒有命中索引

• 4.1.1 在RC隔離級別下，對命中的數據的聚簇索引加X鎖。根據MySQL官方手冊[4]，對于update和delete操作，RC只會鎖住真正執行了寫操作的記錄，這是因為盡管innodb會鎖住所有記錄，MySQL Server層會進行過濾并把不符合條件的鎖當即釋放掉[5]。同時對于UPDATE語句，如果出現了鎖沖突（要加鎖的記錄上已經有鎖），innodb不會立即鎖等待，而是執行semi-consistent read：返回改數據上一次提交的快照版本，供MySQL Server層判斷是否命中，如果命中了才會交給innodb鎖等待。因此加鎖情況可以這樣來認為：

1
2
3
4
5
6

// 在id=5的聚簇索引上加X鎖
update my_table set num=1 where num=200;    
// 先在id=1,5,8,10（全表所有記錄）的聚簇索引上加X鎖
// 然后馬上釋放id=1,8,10的鎖，只保留id=5的鎖
delete from my_table where num=200;

• 4.1.2 在RR隔離級別下，事情就很糟糕了，對全表的所有聚簇索引數據加next-key lock

1
2
3
4
5
6

// 在id=1,5,8,10（全表所有記錄）的聚簇索引上加X鎖
// 并在聚簇索引的所有間隙(-∞,1)(1,5)(5,8)(8,10)(10,+∞)加gap lock
update my_table set num=100 where num=200;    
// 盡管name列有索引，但是like '%%'查詢不使用索引，因此此時也是鎖住所有聚簇索引，情況和上面一模一樣
update my_table set num=100 where name like '%b%';

5. 對索引鍵值有修改

假設上述表中，num列加了二級索引
  如果一條update語句，對索引鍵值有修改，那么修改前后的數據如何加鎖呢。這點要結合數據多版本的可見性來考慮：無論是聚簇索引，還是二級索引，只要其鍵值更新，就會產生新版本。將老版本數據deleted bti設置為1；同時插入新版本[6]。因此可以認為，一次索引鍵值的修改實際上操作了兩條索引數據：原索引和修改后的新索引。
  從innodb的事務的角度來看，如果一個事務操作（寫）了一條數據，那么這條數據一定要加鎖。因此可以認為，如果修改了索引鍵值，那么修改前和修改后的索引都會加鎖。另外，由于修改的數據并沒有被作為查詢條件，那么也不會有“不可重復讀”和“幻讀”的問題，因此無需加gap lock，索引修改只會加X record lock。

示例（RC和RR級別效果一樣）：

1
2
3

// 在id=1的聚簇索引上加X鎖
// 并在name='aaa'（name列索引原鍵值）和name='eee'（新鍵值）的索引上加鎖
update my_table set name='eee' where id=1;

6. 插入數據

假設上述表中，num列加了二級索引
insert加鎖過程：

1. 唯一索引沖突檢查：表中一定有至少一個唯一索引，那么首先會做唯一索引的沖突檢查。innodb檢查唯一索引沖突的方式是，對目標的索引項加S鎖（因為不能依賴快照讀，需要一個徹底的當前讀），讀到數據則唯一索引沖突，返回異常，否則檢查通過。
2. 對插入的間隙加上插入意向鎖(Insert Intention Lock)
3. 對插入記錄的所有索引項加X鎖

示例：

1
2
3
4
5

// 先對id=15加S鎖
// 再對間隙id(10,+∞)和name('ccc',+∞)加Insert Intention Lock
// 然后在id=15的聚簇索引上加X鎖（S鎖升級為X鎖）
// 并在name='fff'的索引上加X鎖
insert into my_table (`id`, `name`, `num`) values ('15', 'fff', '800');

  還有一個有趣的問題，如果插入的二級索引鍵值已經存在，那么這個插入意向鎖會加在哪個間隙中呢？
  顧名思義，插入意向鎖鎖定的間隙一定是將要插入的索引的位置，如果二級索引鍵值相同，默認會按照聚簇索引的大小來排序（二級索引在存儲上其實就是{索引值,主鍵值}）。例如：

1
2
3
4
5
6
7
8

// 插入意向鎖加在間隙 ({'aaa',1},{'bbb',5}) 上
insert into my_table (`id`, `name`, `num`) values ('4', 'bbb', '800');
// 插入意向鎖加在間隙 ({'bbb',5},{'bbb',8}) 上
insert into my_table (`id`, `name`, `num`) values ('6', 'bbb', '800');
// 插入意向鎖加在間隙 ({'bbb',8},{'ccc',10}) 上
insert into my_table (`id`, `name`, `num`) values ('11', 'bbb', '800');

隱式鎖

  為了降低鎖的開銷，innodb采用了延遲加鎖機制，即隱式鎖(implicit lock)[7]。
  從數據存儲結構上看，每張表的數據都是掛在聚簇索引的B+樹下面的葉子節點上（每個節點代表一個page，每個page存放著多行數據）。每行存儲的信息項中都會存有一隱藏列事務id。當有事務對這條記錄進行修改時，需要先判斷該行記錄是否有隱式鎖（原記錄的事務id是否是活動的事務），如果有則為其真正創建鎖并等待，否則直接更新數據并寫入自己的事務id。
  二級索引雖然存儲上沒有記錄事務id，但同樣可以存在隱式鎖，只不過判斷邏輯復雜一些，需要依賴對應的聚簇索引做計算。
  當然，隱式鎖只是一個實現細節，顯示還是隱式加鎖并不影響上文對加鎖的判斷。
  另外，聚簇索引每行記錄的事務id，還有一個重要作用就是實現MVCC快照讀：由于事務id是全局遞增的，那么進行快照讀的時候，如果數據的事務id小于當前事務id并且不在活躍事務列表內（尚未提交），則直接返回當前行數據。否則需要根據roll pointer（和事務id一樣，也在每行的隱藏列中）去查找undo日志。

一個RC隔離級別下的死鎖

  其實可以看到，RC隔離級別下的加鎖已經很少了，用官方文檔的話說”greatly reduces the probability of deadlocks”。因此盡管MySQL的默認隔離級別是RR，但是互聯網應用更傾向與使用RC來避免死鎖+提高并發能力。例如阿里電商的MySQL默認級別就是RC。
  尷尬的是，但是我也的的確確碰到了RC的死鎖。還是以這個表來舉例，假設id為主鍵，num列無索引。

按以下順序執行事務：

  對照上文的加鎖邏輯，insert會對聚簇索引加X鎖，因此trx1和trx2首先會分別持有id=16和id=17的X鎖。
  接下來坑爹的事情來了，對于無索引字段，delete操作不會執行semi-consistent read，而是先直接鎖住所有數據的聚簇索引（盡管后面會馬上釋放，但也需要先獲取鎖）。這樣一來，事務1的delete需要鎖住所有記錄，等待事務2持有的id=17的X鎖，而事務2的delete需要等待事務1的id=16的X鎖。死鎖就產生了。
  在這個例子中，如果insert和delete的順序都顛倒一下，或者delete都變為update，死鎖都不會發生。

小結

• 索引記錄的間隙上用來避免幻讀。
• Select（Serializable隔離級別除外）不會加鎖，而是執行快照讀。
• 寫操作都會加鎖，具體加鎖方式取決于隔離級別、索引命中情況以及修改的索引情況。
• 為了減少鎖的范圍，避免死鎖的發生，應該盡量讓查詢條件命中索引，而且命中的越精確加鎖越少。同時如果能接受RC級別對一致性的破壞，可以將隔離級別調整成RC。

參考資料

[1] 蕭美陽, 葉曉俊. 并發控制實現方法的比較研究[J]. 計算機應用研究, 2006, 23(6):19-22.
[2] MySQL 5.7 Reference Manual :: 15.5.1 InnoDB Locking
[3] MySQL 5.7 Reference Manual :: 15.5.4 Phantom Rows
[4] MySQL 5.7 Reference Manual :: 15.5.2.1 Transaction Isolation Levels
[5] MySQL 加鎖處理分析
[6] InnoDB多版本(MVCC)實現簡要分析
[7] Introduction to Transaction Locks in InnoDB Storage Engine