InnoDB鎖機制

1. 鎖類型

鎖是資料庫區別與文件系統的一個關鍵特性，鎖機制用於管理對共用資源的併發訪問。
InnoDB使用的鎖類型，分別有：

• 共用鎖（S）和排他鎖（X）
• 意向鎖（IS和IX）
• 自增長鎖（AUTO-INC Locks）

1.1. 共用鎖和排他鎖

InnoDB實現了兩種標準的行級鎖：共用鎖（S）和排他鎖（X）

共用鎖：允許持有該鎖的事務讀取行記錄。如果事務 T1 擁有記錄 r 的 S 鎖，事務 T2 對記錄 r 加鎖請求：若想要加 S 鎖，能馬上獲得；若想要獲得 X 鎖，則請求會阻塞。

排他鎖：允許持有該鎖的事務更新或刪除行記錄。如果事務 T1 擁有記錄 r 的 X 鎖，事務 T2 對記錄 r 加鎖請求：無論想獲取 r 的 S 鎖或 X 鎖都會被阻塞。

S 鎖和 X 鎖都是行級鎖。

1.2. 意向鎖

InnoDB 支持多粒度的鎖，允許一行記錄同時持有相容的行鎖和表鎖。意向鎖是表級鎖，表明一個事務之後要獲取表中某些行的 S 鎖或 X 鎖。

InnoDB中使用了兩種意向鎖

• 意向共用鎖（IS）：事務 T 想要對錶 t 中的某些記錄加上 S 鎖
• 意向排他鎖（IX）：事務 T 想要對錶 t 中的某些記錄加上 X 鎖

例如：

• SELECT ... LOCK IN SHARE MODE，設置了 IS 鎖
• SELECT ... FOR UPDATE，設置了 IX 鎖

意向鎖協議如下所示：

• 在一個事務對錶 t 中某一記錄 r 加 S 鎖之前，他必須先獲取表 t 的 IS 鎖
• 在一個事務對錶 t 中某一記錄 r 加 X 鎖之前，他必須先獲取表 t 的 IX 鎖

這些規則可以總結為下麵的圖表（橫向表示一個事務已經獲取了對應的鎖，縱向表示另外一個事務想要獲取對應的鎖)：

IX，IS是表級鎖，不會和行級的X，S鎖發生衝突。只會和表級的X，S發生衝突

當請求的鎖與已持有的鎖相容時，則加鎖成功；如果衝突的話，事務將會等待已有的衝突的鎖釋放

IX 和 IS 鎖的主要目的是表明：某個請求正在或者將要鎖定一行記錄。意向鎖的作用：意向鎖是在添加行鎖之前添加。當再向一個表添加表級 X 鎖的時候

• 如果沒有意向鎖的話，則需要遍歷所有整個表判斷是否有行鎖的存在，以免發生衝突
• 如果有了意向鎖，只需要判斷該意向鎖與即將添加的表級鎖是否相容即可。因為意向鎖的存在代表了，有行級鎖的存在或者即將有行級鎖的存在。因而無需遍歷整個表，即可獲取結果

意向鎖使用 SHOW ENGINE INNODB STATUS 查看當前鎖請求的信息：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

1.3. 自增長鎖

InnoDB中，對每個含有自增長值的表都有一個自增長計數器（aito-increment counter）。當對含有自增長計數器的表進行插入操作時，這個計數器會被初始化。執行如下語句會獲得自增長的值

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作會依據這個自增長的計數器值加1賦予到自增長列。這種實現方式是AUTO_INC Locking。這種鎖採用了一種特殊的表鎖機制，為提高插入的性能，鎖不是在一個事務完成後釋放，而是在完成對自增長值插入的SQL語句後立即釋放。雖然AUTO-INC Locking一定方式提升了併發插入的效率，但還是存在性能上的一些問題：

• 首先，對自增長值的列併發插入性能較差，事務必須等待前一個插入SQL的完成
• 其次，對於 insert... select 的大數據量插入會影響插入的性能，因為另一個插入的事務會被阻塞

InnoDB提供了一種輕量級互斥量的自增長實現機制，大大提高了自增長值插入的性能。提供參數innodb_autoinc_lock_mode來控制自增長鎖使用的演算法，預設值為1。他允許你在可預測的自增長值和最大化併發插入操作之間進行權衡。

插入類型的分類：

innodb_autoinc_lock_mode 在不同設置下對自增長的影響：

innodb_autoinc_lock_mode = 0

MySQL 5.1.22版本之前自增長的實現方式，通過表鎖的AUTO-INC Locking方式

innodb_autoinc_lock_mode = 1（預設值）

對於『simple inserts』，該值會用互斥量（mutex）對記憶體中的計數器進行累加操作。對於『bulk inserts』會用傳統的AUTO-INC Locking方式。這種配置下，如果不考慮回滾，自增長列的增長還是連續的。需要註意的是：如果已經使用AUTO-INC Locking方式去產生自增長的值，而此時需要『simple inserts』操作時，還需要等待AUTO-INC Locking的釋放

innodb_autoinc_lock_mode = 2

對於所有『insert-like』自增長的產生都是通過互斥量，而不是AUTO-INC Locking方式。這是性能最高的方式。但會帶來一些問題：

• 因為併發插入的存在，每次插入時，自增長的值是不連續的
• 基於statement-base replication會出現問題

因此，使用這種方式，任何情況下都需要使用row-base replication，這樣才能保證最大併發性能和replication的主從數據的一致 |

2. 鎖的演算法

InnoDB存儲引擎行鎖的演算法

• Record Locks：單個行記錄上的鎖
• Gap Locks：間隙鎖，鎖定一個範圍，不包含記錄本身
• Next-Key Locking：Record Locks + Gap Locks，鎖住一個範圍 + 記錄本身
• Insert Intention Locks：插入易向鎖

2.1. 行鎖

行鎖是加在索引記錄上的鎖，例如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，會阻止其他事務插入、更新或刪除 t.c1 = 10 的記錄

行鎖總是在索引記錄上面加鎖，即使一張表沒有設置任何索引，InnoDB會創建一個隱藏的聚簇索引，然後在這個索引上加上行鎖。

行鎖使用 SHOW ENGINE INNODB STATUS 的輸出如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.2. 間隙鎖

間隙鎖是加在索引記錄間隙之間的鎖，或者在第一條索引記錄之前、最後一條索引記錄之後的區間上加的鎖。例如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 這條語句阻止其他的事務插入一條 t.c1 = 15 的記錄，因為在10-20的範圍值都已經被加上了鎖。

間隙鎖只在RR隔離級別中使用。如果一條sql使用了唯一索引（包括主鍵索引），那麼不會使用到間隙鎖

例如：id 列是唯一索引，下麵的語句只會在 id = 100 行上面使用Record Lock，而不會關心別的事務是否在上述的間隙中插入數據。如果 id 列沒有索引或者不是唯一索引，這個語句會在上述的間隙上加鎖。

SELECT * FROM child WHERE id = 100 FOR UPDATE;

2.3. Next-Key鎖

Next-Key Lock是結合了Gap Lock 和 Record Lock的一種鎖演算法。

當掃描表的索引時，InnoDB以這種形式實現行級的鎖：遇到匹配的的索引記錄，在上面加上對應的 S 鎖或 X 鎖。因此，行級鎖實際上是索引記錄鎖。如果一個事務擁有索引上記錄 r 的一個 S 鎖或 X 鎖，另外的事務無法立即在 r 記錄索引順序之前的間隙上插入一條新的記錄。

假設有一個索引包含值：10，11，13和20。下列的間隔上都可能加上一個Next-Key 鎖（左開右閉）

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

在最後一個區間中，Next-Key鎖 鎖定了索引中的最大值到 正無窮。

預設情況下，InnoDB啟用 RR 事務隔離級別。此時，InnoDB在查找和掃描索引時會使用 Next-Key 鎖，其設計的目的是為瞭解決『幻讀』的出現。

當查詢的列是唯一索引情況下，InnoDB會對Next-Key Lock進行優化，降級為Record Lock，即只鎖住索引本身，而不是範圍。

next-key 鎖 使用 SHOW ENGINE INNODB STATUS 輸出如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4. 插入意向鎖

插入意向鎖是一種在數據行插入前設置的gap鎖。這種鎖用於在多事務插入同一索引間隙時，如果這些事務不是往這段gap的同一位置插入數據，那麼就不用互相等待。假如有4和7兩個索引記錄值。不同的事務嘗試插入5和6的值。在不同事務獲取分別的 X 鎖之前，他們都獲得了4到7範圍的插入意向鎖，但是他們無需互相等待，因為5和6這兩行不衝突。

例如：客戶端A和B，在插入記錄獲取互斥鎖之前，事務正在獲取插入意向鎖。

客戶端A創建了一個表，包含90和102兩條索引記錄，然後去設置一個互斥鎖在大於100的所有索引記錄上。這個互斥鎖包含了在102記錄前的gap鎖。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
 
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客戶端B 開啟一個事務在這段gap上插入新紀錄，這個事務在等待獲取互斥鎖之前，獲取了一把插入意向鎖。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向鎖 使用 SHOW ENGINE INNODB STATUS 輸出如下：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

3. SQL加鎖分析

給定兩個SQL來分析InnoDB下加鎖的過程：

SQL1：select * from t1 where id = 10;

SQL2：delete * from t1 where id = 10;

事務隔離級別為預設隔離級別Repeatable Read。而對於id不同的索引類型，會有不同的結論。（總結自何登成大神的 MySQL 加鎖處理分析）

SQL1：在RC和RR下，因為MVCC併發控制，select操作不需要加鎖，採用快照讀。讀取記錄的可見版本（可能是歷史版本）

針對SQL2：如下分不同情況

3.1. id主鍵

將主鍵上，id=10的記錄加上 X 鎖

3.2. id唯一索引

id不是主鍵，而是一個唯一的二級索引，主鍵是name列。加鎖步驟如下：

1. 會選擇走id列的索引進行where條件的過濾。找到id=10的記錄後，首先將唯一索引上id=10的索引記錄加上 X 鎖
2. 同時，根據讀取到的name列回主鍵索引（聚簇索引），然後將聚簇索引上的 name='d' 對應的主鍵索引記錄添加 X 鎖

聚簇索引加鎖的原因：如果併發的一個SQL是通過主鍵索引來更新：update t1 set id = 100 where name = 'd'; 此時，如果delete語句沒有將主鍵索引上的記錄加鎖，那麼併發的update就會感知不到delete語句的存在。違背同一條記錄的更新/刪除需要串列執行的約束。

3.3. id非唯一索引

加鎖步驟如下：

1. 通過id索引定位到第一條滿足條件的記錄，加上 X 鎖
2. 這條記錄的間隙上加上 GAP鎖
3. 根據讀取到的name列回主鍵聚簇索引，對應記錄加上 X 鎖
4. 返回讀取下一條，重覆進行... 直到第一條不滿足 where id = 10 條件的記錄 [11, f]，此時不需要加 X 鎖，仍舊需要加 GAP 鎖。結束返回

幻讀解決：
這幅圖中多了個GAP鎖，並不是加到記錄上的，而是加在兩個記錄之間的位置。GAP 鎖就是 RR 隔離級別相對於 RC 隔離級別，不會出現幻讀的關鍵。GAP鎖保證兩次當前讀之前，其他的事務不會插入新的滿足條件的記錄並提交。

所謂幻讀，就是同一個事務，連續做兩次當前讀 (例如：select * from t1 where id = 10 for update;)，那麼這兩次當前讀返回的是完全相同的記錄 (記錄數量一致，記錄本身也一致)，第二次的當前讀，不會比第一次返回更多的記錄 (幻象)。

如圖中所示：考慮到B+樹索引的有序性，有哪些位置可以插入新的滿足條件的項 (id = 10)：

• [6,c] 之前，不會插入id=10的記錄
• [6,c] 與 [10,b] 間，可以插入 [10, aa]
• [10,b] 與 [10,d] 間，可以插入[10,bb]，[10,c]
• [10,d] 與 [11, f] 間，可以插入[10,e]，[10,z]
• [11,f] 之後，不會插入id=10的記錄

因此，不僅將滿足條件的記錄鎖上 (X鎖)，同時還通過GAP鎖，將可能插入滿足條件記錄的3個GAP給鎖上，保證後續的Insert不能插入新的id=10的記錄，也就杜絕了同一事務的第二次當前讀，出現幻象的情況。

當id是唯一索引時，則不需要加GAP鎖。因為唯一索引能夠保證唯一性，對於where id = 10 的查詢，最多只能返回一條記錄，而且新的 id= 10 的記錄，一定不會插入進來。

3.4. id無索引

當id無索引時，只能進行全表掃描，加鎖步驟：

1. 聚簇索引上的所有記錄都加 X 鎖
2. 聚簇索引每條記錄間的GAP都加上了GAP鎖。

如果表中有上千萬條記錄，這種情況是很恐怖的。這個情況下，MySQL也做了一些優化，就是所謂的semi-consistent read。semi-consistent read開啟的情況下，對於不滿足查詢條件的記錄，MySQL會提前放鎖。針對上面的這個用例，就是除了記錄[d,10]，[g,10]之外，所有的記錄鎖都會被釋放，同時不加GAP鎖

4. 死鎖分析與案例

死鎖避免的一些辦法：

1. 如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。
2. 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

5.參考

• mysql 官方文檔：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
• MySQL 加鎖處理分析