【譯】MySQL InnoDB鎖

本文基于mysql 8.0，官方手冊: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html，同時參考了mysql鎖機制詳解

主要內容如下：

• 共享鎖和排他鎖(Shared and Exclusive Locks)
• 意向鎖(Intention Locks)
• 記錄鎖(Record Locks)
• 間隙鎖(Gap Locks)
• 鄰鍵鎖(Next-Key Locks)
• 插入意向鎖(Insert Intention Locks)
• 自增鎖(AUTO-INC Locks)
• 空間索引的謂詞鎖(Predicate Locks for Spatial Indexes)

共享鎖和排他鎖(Shared and Exclusive Locks)

InnoDB實作標準的行級鎖定，其中有兩種型別的鎖： 共享（S）鎖和排他（X）鎖，

• 共享（S）鎖允許持有鎖，讀取行的事務，
• 獨占（X）鎖允許持有鎖，更新或洗掉行的事務，

如果事務T1在r行持有S鎖，則來自某些不同事務T2的對r行鎖定的請求將按以下方式處理：

• T2請求S鎖可以立即被授予，其結果是，T1與T2都在r上持有S鎖，
• T2請求X鎖不能立即授予，

如果事務T1在r行擁有獨占（X）鎖，則不能立即批準某個不同事務T2對r上任一型別的鎖的請求，相反，事務T2必須等待事務T1釋放對r行的鎖定，

注：共享鎖之間不互斥，簡記為：讀讀可以并行，排他鎖與任何鎖互斥，簡記為：寫讀，寫寫不可以并行，

意向鎖(Intention Locks)

InnoDB支持多種粒度鎖定，允許行鎖和表鎖并存，例如，LOCK TABLES ... WRITE這樣的陳述句在特定表上采用排他鎖（X鎖），為了使在多個粒度級別上的鎖定變得切實可行，InnoDB使用意向鎖，意向鎖是表級鎖，指示事務稍后對表中的行需要哪種型別的鎖（共享鎖或排他鎖），有兩種型別的意向鎖：

• 意向共享鎖（IS）指示一個事務打算在表中每行設定一個共享鎖，
• 意向獨占鎖（IX）指示一個事務打算在表中每行設定一個排他鎖，

例如，SELECT ... FOR SHARE設定IS鎖, SELECT ... FOR UPDATE設定IX鎖.
意向鎖定協議如下：

• 在事務可以獲取表中某行的共享鎖之前，它必須首先獲取表中的IS鎖或更高級別的鎖，
• 在事務可以獲取表中某行的排它鎖之前，它必須首先獲取該表的IX鎖，

表級鎖型別的兼容性匯總在以下矩陣中，

如果一個鎖與現有鎖兼容，則將其授予請求的事務，但如果與現有鎖沖突，則不授予該鎖，事務等待直到沖突的現有鎖被釋放，如果鎖定請求與現有鎖定發生沖突，不能被授予許可，因為可能導致死鎖，發生錯誤，

意向鎖不會阻止除全表請求（例如LOCK TABLES ... WRITE）以外的任何內容，意向鎖的主要目的是表明有人正在鎖定表中的行，或者打算鎖定表中的行，

對于意向鎖定事務資料出現類似于在下面SHOW ENGINE INNODB STATUS和 InnoDB的監視器輸出：

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

記錄鎖(Record Locks)

記錄鎖是索引記錄上的鎖，例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事務插入、更新或洗掉 t.c1值為10的行，

記錄鎖始終鎖定索引記錄，即使定義的表沒有索引，對于這種情況，InnoDB 創建一個隱藏的聚集索引并使用該索引進行記錄鎖定，

記錄鎖的交易資料類似于 SHOW ENGINE INNODB STATUS 和 INNODB 監視器輸出中的以下內容:

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        ;;

間隙鎖(Gap Locks)

間隙鎖是對索引記錄之間的間隙的鎖，或者是對第一個索引記錄之前或最后一個索引記錄之后的間隙的鎖，例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE; 阻止其他事務將值15插入到列 t.c1中，無論列中是否已經有這樣的值，因為范圍中所有現有值之間的間隙都被鎖定，

間隙可能跨越單個索引值、多個索引值，甚至可能為空，

間隙鎖是性能和并發性之間權衡的一部分，并且被用于某些事務隔離級別，而不是其他級別，

使用唯一索引搜索唯一行的鎖定陳述句不需要間隙鎖，例如，如果id列有一個唯一的索引，下面的陳述句只對 id 值為100的行使用一個索引記錄鎖，其他會話是否在前面的間隙中插入行并不重要:

SELECT * FROM child WHERE id = 100;

如果 id 沒有被索引或具有非唯一的索引，則陳述句將鎖定前面的間隙，

這里還值得注意的是，沖突鎖可能由不同的事務在一個間隙上持有，例如，事務A可以對間隙持有共享間隙鎖(gap S-lock) ，而事務B對同一間隙持有排他間隙鎖(gap X-lock)， 允許使用沖突的間隙鎖的原因是，如果從索引中清除記錄，則必須合并不同事務在記錄上持有的間隙鎖，

Innodb中的間隙鎖是“完全禁止的”，這意味著它們的唯一目的是防止其他交易插入到間隙中，間隙鎖可以共存，一個事務獲取的間隙鎖并不阻止另一個事務獲取同一間隙的間隙鎖， 共享間隙鎖和獨占間隙鎖之間沒有區別，它們之間沒有沖突，而且它們執行相同的功能，

可以顯式禁用間隙鎖，如果您將事務隔離級別更改為READ COMMITTED，就會發生這種情況， 在這些情況下，搜索和索引掃描禁用間隙鎖，并且僅用于外鍵約束檢查和重復鍵檢查，

使用 READ COMMITTED 隔離級別還有其他影響， 在 MySQL 評估 WHERE 條件之后，將釋放不匹配行的記錄鎖， 對于 UPDATE 陳述句，InnoDB 執行“半一致(semi-consistent)”讀操作，以便將最新提交的版本回傳給 MySQL，這樣 MySQL 就可以確定該行是否符合 UPDATE 的 WHERE 條件，

鄰鍵鎖(Next-Key Locks)

鄰鍵鎖是索引記錄上的記錄鎖和索引記錄前的間隙鎖的組合，

InnoDB執行行級鎖定的方式是，當它搜索或掃描表索引時，它會在遇到的索引記錄上設定共享鎖或排他鎖， 因此，行級鎖實際上是索引記錄鎖，索引記錄上的鄰鍵鎖也會影響該索引記錄之前的“間隙”，也就是說，鄰鍵鎖是索引記錄鎖加上索引記錄前的間隙鎖， 如果一個會話對索引中的記錄R有一個共享或排他鎖，那么另一個會話就不能按索引順序在緊靠R的間隙中插入新的索引記錄，

假設一個索引包含值10、11、13和20，該索引可能的鄰鍵鎖覆寫以下區間，其中圓括號表示排除了間隔端點，方括號表示包含端點:

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

對于最后一個間隔，鄰鍵鎖定索引中最大值以上的空隙，并鎖定“上確界”偽記錄的值高于索引中實際的任何值，上確界不是真正的索引記錄，因此實際上，這個鄰鍵鎖只鎖定最大索引值之后的空隙，

默認情況下，InnoDB 在 REPEATABLE READ 事務隔離級別運行，在這種情況下，InnoDB 使用鄰鍵鎖進行搜索和索引掃描，以防止幻象行，

鄰鍵鎖的事務資料類似于 SHOW ENGINE INNODB STATUS 和 INNODB 監視器輸出中的以下內容:

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        ;;

插入意向鎖(Insert Intention Locks)

插入意向鎖是由 INSERT 操作在插入行之前設定的一種間隙鎖， 這個鎖標志著插入的意向，以這樣的方式，插入到同一索引間隙中的多個事務如果沒有插入到間隙中的同一位置，則不需要彼此等待，假設有值為4和7的索引記錄， 嘗試插入值5和6的獨立事務，每個事務在獲得插入行的獨占鎖之前用插入意向鎖鎖鎖定4和7之間的間隙，但不會阻塞彼此，因為行之間沒有沖突，

下面的示例演示在獲取所插入記錄的獨占鎖之前使用插入意向鎖的事務，這個例子涉及到兩個客戶端，A和B，

客戶端A創建一個包含兩個索引記錄(90和102)的表，然后啟動一個事務，該事務對 ID 大于100的索引記錄放置排他鎖，排他鎖在記錄102之前包含一個間隔鎖:

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開始一個事務，將一個記錄插入到間隙中，事務在等待獲取排他鎖時接受插入意向鎖，

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

插入意向鎖的事務資料類似于 SHOW ENGINE INNODB STATUS 和 INNODB 監視器輸出中的以下內容:

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  ;;...

自增鎖(AUTO-INC Locks)

自增鎖是一種特殊的表級鎖，它由插入到帶有 AUTO_INCREMENT 列的表中的事務獲得， 在最簡單的情況下，如果一個事務正在向表中插入值，那么任何其他事務都必須等待自己的插入操作，以便第一個事務插入的行接收連續的主鍵值，

該innodb_autoinc_lock_mode 配置選項控制用于自增鎖的演算法，它允許您選擇如何在可預測自增值序列與插入操作的最大并發性之間進行權衡，

空間索引的謂詞鎖(Predicate Locks for Spatial Indexes)

Innodb 支持對包含空間列的列進行 SPATIAL 索引，

為了處理與 SPATIAL 索引有關的操作的鎖定，鄰鍵鎖定不能很好地支持 REPEATABLE READ 或 SERIALIZABLE 事務隔離級別， 多維資料中沒有絕對排序概念，因此不清楚哪個是鄰鍵，

為了支持具有 SPATIAL 索引的表的隔離級別，InnoDB使用謂詞鎖，空間索引包含最小外接矩形值，因此 InnoDB 通過在用于查詢的 MBR 值上設定謂詞鎖來強制對索引進行一致性讀， 其他事務不能插入或修改與查詢條件匹配的行，

標籤：MySQL

上一篇：python Oracle如何連接

下一篇：求助Oracle同義詞權限控制問題