InnoDB學習（六）之資料庫鎖

御狐神發表於2021-12-23
資料庫

InnoDB儲存引擎的預設隔離級別事可重複讀，MVCC多版本併發控制僅僅解決了快照讀情況下的資料隔離，而對於當前讀，InnoDB通過鎖來進行併發控制。

InnoDB鎖

本文主要參考了MySQL官方文件，並在上面新增了一些自己的理解，有興趣看英文的也可以看MySQL官方文件。本文分為以下章節：

  1. 共享鎖和獨佔鎖；
  2. 意向鎖；
  3. 行鎖；
  4. 間隙鎖；
  5. Next-Key鎖
  6. 插入意向鎖；
  7. 自增鎖；

共享鎖和排他鎖

InnoDB鎖的最小粒度是行鎖，行鎖可以分為兩大類：共享鎖（S）和獨佔鎖(X)。

  • 共享鎖：持有某行資料共享鎖的事務，可以讀取行鎖對應行的資料；
  • 獨佔鎖：持有某行資料獨佔鎖的事務，可以修改行鎖對應行的資料；

如果事務T1持有行R的共享鎖，那麼對於事務T2對行R的訪問分為兩種情況：

  • 如果事務T2請求行R的共享鎖，則事務T2可以請求成功，請求完成後事務T1和事務T2同時持有行R的共享鎖；
  • 如果事務T2請求行R的排他鎖，事務T2會被阻塞，直到事務T1釋放鎖或者事務超時回滾；

如果事務T1持有行R的共享鎖，那麼不管事務T2請求R行的共享鎖還是排他鎖，都會被事務T1阻塞，直到事務T1釋放鎖或事務T2回滾。

意向鎖

InnoDB支援支援多種粒度的鎖，比如對於以下兩個SQL語句，加鎖的物件就完全不同：

  1. SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE，其中ID是主鍵，ID=1的資料行存在，那麼這句SQL會獲取ID=1的資料行的獨佔鎖；
  2. LOCK TABLES USER_INFO WRITE，其中USER_INFO表存在，那麼這句SQL會獲取USER_INFO表的獨佔鎖；

表鎖和行鎖之間也存在互斥的情況，比如表上的獨佔鎖和表中每一行資料的獨佔鎖之間衝突（鎖表了當然不允許修改表中的內容），這種互斥要怎麼實現呢？InnoDB使用了意向鎖實現表鎖和行鎖之間的互斥，意向鎖是表級別的鎖，對一行資料新增獨佔鎖或排他鎖時，會先向資料行所在的表新增意向鎖，意向鎖分為兩種型別：

  1. 共享意向鎖：事務會對錶中的某一行資料新增共享鎖；
  2. 排他意向鎖：事務會對錶中的某一行資料新增排他鎖；

所以對錶新增意向鎖的情況也分兩種：

  1. 如果事務需要獲取某一行資料的共享鎖，那麼必然會首先獲取資料所在表的共享意向鎖，如SQL語句SELECT * FROM USER_INFO WHERE ID = 1 LOCK IN SHARE MODE會首先向表USER_INFO新增共享意向鎖；
  2. 如果事務需要獲取某一行資料的排他鎖，那麼必然會首先獲取資料所在表的排他意向鎖，如SQL語句SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE會首先向表USER_INFO新增共享排他鎖；

表鎖和意向鎖之間的衝突情況如下所示：

表排他鎖 共享排他鎖 表共享鎖 共享意向鎖
表排他鎖 衝突 衝突 衝突 衝突
共享排他鎖 衝突 不衝突 衝突 不衝突
表共享鎖 衝突 衝突 不衝突 不衝突
共享意向鎖 衝突 不衝突 不衝突 不衝突

如果事務請求的表鎖和表現有的鎖之間不衝突，那麼事務可以請求鎖成功；如果事務請求的鎖和表現有的鎖衝突，那麼事務必須等待表鎖被釋放，或者當前事務需要回滾。

我們可以注意到，意向鎖之間不會互斥，因為意向鎖代表的是修改表中的某一行資料，兩個意向鎖表示修改表中的兩行資料，所以兩個意向鎖不一定會衝突。意圖鎖只會和表鎖之間衝突，如LOCK TABLES USER_INFO WRITE會向表新增表鎖。

InnoDB中，我們可以通過SHOW ENGINE INNODB STATUS語句檢視錶鎖狀況，以下為鎖狀況示例：

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

行鎖

行鎖是新增在索引上的鎖，例如對於SELECT * FROM USER_INFO WHERE ID = 1 FOR UPDATE，在ID是唯一索引的情況下，該SQL語句會對ID對應的索引節點上新增排他鎖，阻止其它事務修改該行資料。行鎖新增的物件是索引節點，如果表沒有定義索引，InnoDB會建立一個隱藏的聚集索引，並使用該索引來新增行鎖。

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

間隙鎖

間隙鎖加鎖的物件是索引之間的間隙，例如如果對於SQL語句SELECT * FROM USER_INFO WHERE ID>10 and ID<20 FOR UPDATE，該事務會向資料庫中ID索引樹上10~20之間的所有節點間隙新增間隙鎖。當另一個事務嘗試向資料庫中插入ID=15的記錄時，會被間隙鎖阻塞。

間隙鎖的間隙中可以包含多個索引節點、單個索引階段或者不包含任何節點。間隙鎖主要用於解決可重複讀隔離級別下的幻讀問題。

對於唯一索引，如果使用等值查詢，那麼間隙鎖會退化為行鎖，如下SQL中，ID是唯一索引列，並且ID=100的資料存在，那麼以下SQL只會新增行鎖：

SELECT * FROM child WHERE id = 100;

如果ID不是唯一索引，那麼上文中的SQL語句則會給ID索引樹中的Id=100和前一個節點之間的間隙新增GAP鎖，間隙鎖之間不衝突，並且兩個間隙鎖之間的節點被刪除之後，兩個間隙鎖還會合併為一個間隙鎖。

InnoDB中的間隙鎖只有一個目的，阻止向間隙內插入資料，間隙鎖只和插入意向鎖衝突，和其它任何鎖都不衝突。可以通過將事務隔離級別更改為讀已提交或啟用innodb_locks_unsafe_for_binlog系統變數來禁用間隙鎖。

在禁用間隙鎖的情況下，InnoDB還會將釋放不匹配行的記錄鎖（違反了加鎖的2PL原則）。對於UPDATE語句，InnoDB執行"半一致"讀取：讀取最新提交的資料，MySQL使用最新提交的資料判斷是否符合UPDATE語句中的WHERE條件。

Next-Key鎖

Next-Key鎖是行鎖和間隙鎖的組合，在InnoDB唯一索引加鎖的過程中，InnoDB會從索引中查詢符合條件的索引節點，並對這些符合條件的索引節點新增行鎖。

如果對某行記錄加Next-Key鎖而不是行鎖，那麼而Next-Key鎖不僅會對記錄本身新增行鎖，還會對行鎖之前的間隙新增間隙鎖，二者組合成了Next-Key。Next-Key不允許其它事務向加鎖的間隙中插入資料。

假設ID索引包含值10、11、13和20，那麼先索引的節點新增Next-Key鎖可能會有以下幾種情況，下文中圓括號表示排除間隙，方括號表示包含端點：

  1. 如果對索引10所在的節點加Next-Key，加鎖範圍為(負無窮, 10]；
  2. 如果對索引11所在的節點加Next-Key，加鎖範圍為(10, 11]；
  3. 如果對索引13所在的節點加Next-Key，加鎖範圍為(11, 13]；
  4. 如果對索引20所在的節點加Next-Key，加鎖範圍為(13, 20]；
  5. 如果20之後的間隙加Next-Key，加鎖範圍為(20, 正無窮）；

對於最後一個間隙，可以理解為：InnoDB中有一個虛擬的最大節點，會在該節點上新增Next-Key.

InnoDB中，我們可以通過SHOW ENGINE INNODB STATUS語句檢視Next-Key鎖狀況，以下為鎖狀況示例：

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

插入意向鎖

插入意向鎖是向資料庫中插入一行新資料時，需要向插入間隙新增的一種間隙鎖。插入意向鎖之間不衝突，例如兩個事務分別打算向(4,7]之間的間隙插入5和6，這兩個事務都會向(4,7]中的間隙新增插入意向鎖，但是二者互不阻塞。

假設有兩個事務，事務A和事務B，資料庫表中包含兩條記錄90和102。事務A對ID大於100的索引記錄新增Next-Key獨佔鎖定：

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嘗試向資料庫中插入一條101的記錄：

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

自增鎖

自增鎖是一種特殊的表級鎖，當表中包含AUTO_INCREAMENT的表中的事務使用。在最簡單的情況下，如果一個事務正在向表中插入資料行，該事務會佔有自增所，其它任何事務在向表中插入資料時都會被該鎖阻塞。我們可以通過innodb_autoinc_lock_mode變數控制自增鎖的自增的演算法，MySQL對自增鎖有很多優化，本文不詳細介紹。

我是御狐神，歡迎大家關注我的微信公眾號:wzm2zsd

qrcode_for_gh_83670e17bbd7_344-2021-09-04-10-55-16

參考文件

MySQL官方文件

本文最先發布至微信公眾號，版權所有，禁止轉載！

相關文章