MySQL InnoDB鎖介紹及不同SQL語句分別加什么樣的鎖的示例分析

161次閱讀

沒有評論

共計 19037 個字符，預計需要花費 48 分鐘才能閱讀完成。

行業資訊
數據庫
MySQL InnoDB 鎖介紹及不同 SQL 語句分別加什么樣的鎖的示例分析

今天就跟大家聊聊有關 MySQL InnoDB 鎖介紹及不同 SQL 語句分別加什么樣的鎖的示例分析，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

文章正文開始

“加什么樣的鎖”與以下因素相關

當前事務的隔離級別

SQL 是一致性非鎖定讀 (consistent nonlocking read) 還是 DML(INSERT/UPDATE/DELETE)或鎖定讀(locking read)

SQL 執行時是否使用了索引，所使用索引的類型(主鍵索引，輔助索引、唯一索引)

我們先分別介紹這幾個因素

一、隔離級別(isolation level)

數據庫事務需要滿足 ACID 原則，“I”即隔離性，它要求兩個事務互不影響，不能看到對方尚未提交的數據。數據庫有 4 種隔離級別 (isolation level)，按著隔離性從弱到強(相應的，性能和并發性從強到弱) 分別是

Read Uncommitted。下面簡稱 RU

Read Committed。下面簡稱 RC

Repeatable Read(MySQL 的默認隔離級別)。下面簡稱 RR

Serializable

“I”即隔離性正是通過鎖機制來實現的。提到鎖就會涉及到死鎖，需要明確的是死鎖的可能性并不受隔離級別的影響，因為隔離級別改變的是讀操作的行為，而死鎖是由于寫操作產生的。

--  查看事務的   全局和 session  隔離級別( MySQL 5.7.19 及之前使用 tx_isolation) select @@global.transaction_isolation, @@session.transaction_isolation; --  設置   全局   事務隔離級別為 repeatable read set global transaction isolation level repeatable read --  設置   當前 session  事務隔離級別為 read uncommitted set session transaction isolation level read uncommitted

事務隔離級別設置和查看的詳細語法請見：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

二、一致性非鎖定讀和鎖定讀

InnoDB 有兩種不同的 SELECT，即普通 SELECT 和鎖定讀 SELECT。鎖定讀 SELECT 又有兩種，即 SELECT … FOR SHARE 和 SELECT … FOR UPDATE; 鎖定讀 SELECT 之外的則是普通 SELECT。

不同的 SELECT 是否都需要加鎖呢?

普通 SELECT 時使用一致性非鎖定讀，不加鎖;

鎖定讀 SELECT 使用鎖定讀，加鎖;

此外，DML(INSERT/UPDATE/DELETE)時，需要先查詢表中的記錄，此時也使用鎖定讀，加鎖;

FOR SHARE 語法是 MySQL 8.0 時加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等價的，但，FOR SHARE 用于替代 LOCK IN SHARE MODE，不過，為了向后兼容，LOCK IN SHARE MODE 依然可用。

1、一致性非鎖定讀(consistent nonlocking read)

InnoDB 采用多版本并發控制 (MVCC, multiversion concurrency control) 來增加讀操作的并發性。MVCC 是指，InnoDB 使用基于時間點的快照來獲取查詢結果，讀取時在訪問的表上不設置任何鎖，因此，在事務 T1 讀取的同一時刻，事務 T2 可以自由的修改事務 T1 所讀取的數據。這種讀操作被稱為一致性非鎖定讀。這里的讀操作就是普通 SELECT。

隔離級別為 RU 和 Serializable 時不需要 MVCC，因此，只有 RC 和 RR 時，才存在 MVCC，才存在一致性非鎖定讀。

一致性非鎖定讀在兩種隔離級別 RC 和 RR 時，是否有什么不同呢? 是的，兩種隔離級別下，拍得快照的時間點不同

RC 時，同一個事務內的每一個一致性讀總是設置和讀取它自己的 *** 快照。也就是說，每次讀取時，都再重新拍得一個 *** 的快照(所以，RC 時總是可以讀取到 *** 提交的數據)。

RR 時，同一個事務內的所有的一致性讀總是讀取同一個快照，此快照是執行該事務的 *** 個一致性讀時所拍得的。

2、鎖定讀(locking read)

如果你先查詢數據，然后，在同一個事務內插入 / 更新相關數據，普通的 SELECT 語句是不能給你足夠的保護的。其他事務可以更新 / 刪除你剛剛查出的數據行。InnoDB 提供兩種鎖定讀，即：SELECT … FOR SHARE 和 SELECT … FOR UPDATE。它倆都能提供額外的安全性。

這兩種鎖定讀在搜索時所遇到的 (注意：不是最終結果集中的) 每一條索引記錄 (index record) 上設置排它鎖或共享鎖。此外，如果當前隔離級別是 RR，它還會在每個索引記錄前面的間隙上設置排它的或共享的 gap lock(排它的和共享的 gap lock 沒有任何區別，二者等價)。

看完背景介紹，我們再來看一下 InnoDB 提供的各種鎖。

三、InnoDB 提供的 8 種不同類型的鎖

InnoDB 一共有 8 種鎖類型，其中，意向鎖 (Intention Locks) 和自增鎖 (AUTO-INC Locks) 是表級鎖，剩余全部都是行級鎖。此外，共享鎖或排它鎖 (Shared and Exclusive Locks) 盡管也作為 8 種鎖類型之一，它卻并不是具體的鎖，它是鎖的模式，用來“修飾”其他各種類型的鎖。

MySQL5.7 及之前，可以通過 information_schema.innodb_locks 查看事務的鎖情況，但，只能看到阻塞事務的鎖; 如果事務并未被阻塞，則在該表中看不到該事務的鎖情況。

MySQL8.0 刪除了 information_schema.innodb_locks，添加了 performance_schema.data_locks，可以通過 performance_schema.data_locks 查看事務的鎖情況，和 MySQL5.7 及之前不同，performance_schema.data_locks 不但可以看到阻塞該事務的鎖，還可以看到該事務所持有的鎖，也就是說即使事務并未被阻塞，依然可以看到事務所持有的鎖(不過，正如文中 *** 一段所說，performance_schema.data_locks 并不總是能看到全部的鎖)。表名的變化其實還反映了 8.0 的 performance_schema.data_locks 更為通用了，即使你使用 InnoDB 之外的存儲引擎，你依然可以從 performance_schema.data_locks 看到事務的鎖情況。

performance_schema.data_locks 的列 LOCK_MODE 表明了鎖的類型，下面在介紹各種鎖時，我們同時指出鎖的 LOCK_MODE。

1、共享鎖或排它鎖(Shared and Exclusive Locks)

它并不是一種鎖的類型，而是其他各種鎖的模式，每種鎖都有 shard 或 exclusive 兩種模式。

當我們說到共享鎖 (S 鎖) 或排它鎖 (X 鎖) 時，一般是指行上的共享鎖或者行上的排它鎖。需要注意的是，表鎖也存在共享鎖和排它鎖，即表上的 S 鎖和表上的 X 鎖，表上的鎖除了這兩種之外，還包括下面將會提到的意向共享鎖 (Shard Intention Locks) 即 IS 鎖、意向排它鎖 (Exclusive Intention Locks) 即 IX 鎖。表上的鎖，除了這四種之外，還有其他類型的鎖，這些鎖都是在訪問表的元信息時會用到的(create table/alter table/drop table 等)，本文不討論這些鎖，詳細可見：常用 SQL 語句的 MDL 加鎖源碼分析。

數據行 r 上共享鎖 (S 鎖) 和排它鎖 (X 鎖) 的兼容性如下：

假設 T1 持有數據行 r 上的 S 鎖，則當 T2 請求 r 上的鎖時：

T2 請求 r 上的 S 鎖，則，T2 立即獲得 S 鎖。T1 和 T2 同時都持有 r 上的 S 鎖。

T2 請求 r 上的 X 鎖，則，T2 無法獲得 X 鎖。T2 必須要等待直到 T1 釋放 r 上的 S 鎖。

假設 T1 持有 r 上的 X 鎖，則當 T2 請求 r 上的鎖時：

T2 請求 r 上的任何類型的鎖時，T2 都無法獲得鎖，此時，T2 必須要等待直到 T1 釋放 r 上的 X 鎖

2、意向鎖(Intention Locks)

表鎖。含義是已經持有了表鎖，稍候將獲取該表上某個 / 些行的行鎖。有 shard 或 exclusive 兩種模式。

LOCK_MODE 分別是：IS 或 IX。

意向鎖用來鎖定層級數據結構，獲取子層級的鎖之前，必須先獲取到父層級的鎖。可以這么看 InnoB 的層級結構：InnoDB 所有數據是 schema 的集合，schema 是表的集合，表是行的集合。意向鎖就是獲取子層級 (數據行) 的鎖之前，需要首先獲取到父層級 (表) 的鎖。

意向鎖的目的是告知其他事務，某事務已經鎖定了或即將鎖定某個 / 些數據行。事務在獲取行鎖之前，首先要獲取到意向鎖，即：

事務在獲取行上的 S 鎖之前，事務必須首先獲取表上的 IS 鎖或表上的更強的鎖。

事務在獲取行上的 X 鎖之前，事務必須首先獲取表上的 IX 鎖。

事務請求鎖時，如果所請求的鎖與已存在的鎖兼容，則該事務可以成功獲得所請求的鎖; 如果所請求的鎖與已存在的鎖沖突，則該事務無法獲得所請求的鎖。

表級鎖 (table-level lock) 的兼容性矩陣如下：

對于上面的兼容性矩陣，一定注意兩點：

在上面的兼容性矩陣中，S 是表的 (不是行的) 共享鎖，X 是表的 (不是行的) 排它鎖。

意向鎖 IS 和 IX 和任何行鎖都兼容(即：和行的 X 鎖或行的 S 鎖都兼容)。

所以，意向鎖只會阻塞全表請求(例如：LOCK TABLES … WRITE)，不會阻塞其他任何東西。因為 LOCK TABLES … WRITE 需要設置 X 表鎖，這會被意向鎖 IS 或 IX 所阻塞。

InnoDB 允許表鎖和行鎖共存，使用意向鎖來支持多粒度鎖(multiple granularity locking)。意向鎖如何支持多粒度鎖呢，我們舉例如下

T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE;

T2: LOCK TABLE t1 WRITE;

T1 執行時，需要獲取 i = 1 的行的 X 鎖，但，T1 獲取行鎖前，T1 必須先要獲取 t1 表的 IX 鎖，不存在沖突，于是 T1 成功獲得了 t1 表的 IX 鎖，然后，又成功獲得了 i = 1 的行的 X 鎖;T2 執行時，需要獲取 t1 表的 X 鎖，但，T2 發現，t1 表上已經被設置了 IX 鎖，因此，T2 被阻塞(因為表的 X 鎖和表的 IX 鎖不兼容)。

假設不存在意向鎖，則：

T1 執行時，需要獲取 i = 1 的行的 X 鎖(不需要獲取 t1 表的意向鎖了);T2 執行時，需要獲取 t1 表的 X 鎖，T2 能否獲取到 T1 表的 X 鎖呢?T2 無法立即知道，T2 不得不遍歷表 t1 的每一個數據行以檢查，是否某個行上已存在的鎖和自己即將設置的 t1 表的 X 鎖沖突，這種的判斷方法效率實在不高，因為需要遍歷整個表。

所以，使用意向鎖，實現了“表鎖是否沖突”的快速判斷。意向鎖就是協調行鎖和表鎖之間的關系的，或者也可以說，意向鎖是協調表上面的讀寫鎖和行上面的讀寫鎖 (也就是不同粒度的鎖) 之間的關系的。

3、索引記錄鎖(Record Locks)

也就是所謂的行鎖，鎖定的是索引記錄。行鎖就是索引記錄鎖，所謂的“鎖定某個行”或“在某個行上設置鎖”，其實就是在某個索引的特定索引記錄 (或稱索引條目、索引項、索引入口) 上設置鎖。有 shard 或 exclusive 兩種模式。

LOCK_MODE 分別是：S,REC_NOT_GAP 或 X,REC_NOT_GAP。

行鎖就是索引記錄鎖，索引記錄鎖總是鎖定索引記錄，即使表上并未定義索引。表未定義索引時，InnoDB 自動創建隱藏的聚集索引(索引名字是 GEN_CLUST_INDEX)，使用該索引執行 record lock。

4、間隙鎖(Gap Locks)

索引記錄之間的間隙上的鎖，鎖定尚未存在的記錄，即索引記錄之間的間隙。有 shard 或 exclusive 兩種模式，但，兩種模式沒有任何區別，二者等價。

LOCK_MODE 分別是：S,GAP 或 X,GAP。

gap lock 可以共存(co-exist)。事務 T1 持有某個間隙上的 gap lock 并不能阻止事務 T2 同時持有同一個間隙上的 gap lock。shared gap lock 和 exclusive gap lock 并沒有任何的不同，它倆并不沖突，它倆執行同樣的功能。

gap lock 鎖住的間隙可以是 *** 個索引記錄前面的間隙，或相鄰兩條索引記錄之間的間隙，或 *** 一個索引記錄后面的間隙。

索引是 B + 樹組織的，因此索引是從小到大按序排列的，在索引記錄上查找給定記錄時，InnoDB 會在 *** 個不滿足查詢條件的記錄上加 gap lock，防止新的滿足條件的記錄插入。

上圖演示了：InnoDB 在索引上掃描時，找到了 c2=11 的記錄，然后，InnoDB 接著掃描，它發現下一條記錄是 c2=18，不滿足條件，InnoDB 遇到了 *** 個不滿足查詢條件的記錄 18，于是 InnoDB 在 18 上設置 gap lock，此 gap lock 鎖定了區間(11, 18)。

為什么需要 gap lock 呢?gap lock 存在的唯一目的就是阻止其他事務向 gap 中插入數據行，它用于在隔離級別為 RR 時，阻止幻影行 (phantom row) 的產生; 隔離級別為 RC 時，搜索和索引掃描時，gap lock 是被禁用的，只在外鍵約束檢查和重復 key 檢查時 gap lock 才有效，正是因為此，RC 時會有幻影行問題。

gap lock 是如何阻止其他事務向 gap 中插入數據行的呢? 看下圖

索引是 B + 樹組織的，因此索引是從小到大按序排列的，如果要插入 10，那么能插入的位置只能是上圖中標紅的區間。在 10 和 10 之間插入時，我們就認為是插入在 *** 面的 10 的后面。如果封鎖了標紅的區間，那么其他事務就無法再插入 10 啦。

問題一：當 T2 要插入 10 時，上圖哪些地方允許插入(注意：索引是有序的哦)?

答：(8, 10)和(10,11)。在 10 和 10 之間插入，我們就認為是插入在 *** 的 10 后面。

只要封鎖住圖中標紅的區間，T2 就無法再插入 10 啦。上面這兩個區間有什么特點嗎? 對，這兩個區間就是：滿足條件的每一條記錄前面的間隙，及，*** 一條不滿足條件的記錄前面的間隙。InnoDB 使用下一個鍵鎖 (Next-Key Locks) 或間隙鎖 (Gap Locks) 來封鎖這種區間。

問題二：gap lock 是用來阻塞插入新數據行的，那么，T2, insert into g values(z , 9) 會被阻塞嗎? 插入 (z , 8)，(z , 10)，(z , 11) 呢?

答：上圖中，T1 的 update 設置的 gap lock 是 (8, 10)和(10,11)，而，insert intention lock 的范圍是(插入值, 向下的一個索引值)。insert intention lock 的詳細介紹請見下面的 6. 插入意向鎖(Insert Intention Locks)。

于是，對于上面這些插入值，得到的 insert intention lock 如下：

插入 (z , 8)時，insert intention lock 是 (8, 10) — 沖突，與 gap lock (8, 10)重疊了

插入 (z , 9)時，insert intention lock 是 (9, 10) — 沖突，與 gap lock (8, 10)重疊了

插入 (z , 10)時，insert intention lock 是 (10, 11) — 沖突，與 gap lock (10, 11)重疊了

插入 (z , 11)時，insert intention lock 是 (11, 15) — 不沖突

事實是不是這樣呢，看下圖

是的，和我們分析的一致，為了看的更清楚，我們把結果列成圖表如下

問題三：“gap 是解決 phantom row 問題的”，插入會導致 phantom row，但更新也一樣也會產生 phantom row 啊。

例如，上圖的 T1 和 T2，T1 把所有 i = 8 的行更新為 108，T2 把 i =15 的行更新為 8，如果 T2 不被阻塞，T1 的 WHERE 條件豈不是多出了一行，即：T1 出現了 phantom row?

答：nice question。我們自己來分析下 T1 和 T2 分別加了哪些鎖

T1 加的鎖：idx_i 上的 next-key lock (5, 8]，PRIMARY 上的 b，以及 idx_i 上的 gap lock (8,10)

T2 加的鎖：idx_i 上的 next-key lock (11, 15]，PRIMARY 上的 f，以及 idx_i 上的 gap lock (15,108)，*** 這個 gap lock 是因為 T1 在 idx_i 上加了新值 108

根據上面的分析，T1 和 T2 的鎖并沒有重疊，即我們分析的結果是：T2 不會被阻塞。

但，上圖清楚的表明 T2 確實被阻塞了，原因竟然是：T2 insert intention lock 和 T1 gap lock(8, 10)沖突了。很奇怪，T2 是更新語句，為什么會有 insert intention lock 呢?

我不知道確切的原因，因為我沒找到文檔說這事。根據我的推斷，update … set 成功找到結果集然后執行更新時，在即將被更新進入行的新值上設置了 insert intention lock(如果找不到結果集，則就不存在 insert intention lock 啦)，因此，T2 在 idx_i 上的新值 8 上設置了 insert intention lock(8, 10)。最終，T2 insert intention lock(8, 10) 與 T1 gap lock(8, 10)沖突啦，T2 被阻塞。

因此，update … set 成功找到結果集時，會在即將被更新進入行的新值上設置 index record lock 以及 insert intention lock。如前所述，insert intention lock 的范圍是 (插入值，下一個值)，如果 T2 是 update g set i=9 where i=15; 那么 update … set 所設置的新值是 9，則 T2 insert intention lock 就是(9, 10) 啦，它依然會和 T1 gap lock(8, 10)沖突，是這樣嗎? 確實是的，感興趣的同學可以試試。

5、下一個鍵鎖(Next-Key Locks)

next-key lock 是 (索引記錄上的索引記錄鎖) + (該索引記錄前面的間隙上的鎖) 二者的合體，它鎖定索引記錄以及該索引記錄前面的間隙。有 shard 或 exclusive 兩種模式。

LOCK_MODE 分別是：S 或 X。

當 InnoDB 搜索或掃描索引時，InnoDB 在它遇到的索引記錄上所設置的鎖就是 next-key lock，它會鎖定索引記錄本身以及該索引記錄前面的 gap(gap immediately before that index record)。即：如果事務 T1 在索引記錄 r 上有一個 next-key lock，則 T2 無法在緊靠著 r 前面的那個間隙中插入新的索引記錄(gap immediately before r in the index order)。

next-key lock 還會加在“supremum pseudo-record”上，什么是 supremum pseudo-record 呢? 它是索引中的偽記錄(pseudo-record)，代表此索引中可能存在的 *** 值，設置在 supremum pseudo-record 上的 next-key lock 鎖定了“此索引中可能存在的 *** 值”，以及這個值前面的間隙，“此索引中可能存在的 *** 值”在索引中是不存在的，因此，該 next-key lock 實際上鎖定了“此索引中可能存在的 *** 值”前面的間隙，也就是此索引中當前實際存在的 *** 值后面的間隙。例如，下圖中，supremum pseudo-record 上的 next-key lock 鎖定了區間(18, 正無窮)，正是此 next-key lock 阻止其他事務插入例如 19, 100 等更大的值。

supremum pseudo-record 上的 next-key lock 鎖定了“比索引中當前實際存在的 *** 值還要大”的那個間隙，“比大還大”，“bigger than bigger”

6、插入意向鎖(Insert Intention Locks)

一種特殊的 gap lock。INSERT 操作插入成功后，會在新插入的行上設置 index record lock，但，在插入行之前，INSERT 操作會首先在索引記錄之間的間隙上設置 insert intention lock，該鎖的范圍是(插入值, 向下的一個索引值)。有 shard 或 exclusive 兩種模式，但，兩種模式沒有任何區別，二者等價。

LOCK_MODE 分別是：S,GAP,INSERT_INTENTION 或 X,GAP,INSERT_INTENTION。

insert intention lock 發出按此方式進行插入的意圖：多個事務向同一個 index gap 并發進行插入時，多個事務無需相互等待。

假設已存在值為 4 和 7 的索引記錄，事務 T1 和 T2 各自嘗試插入索引值 5 和 6，在得到被插入行上的 index record lock 前，倆事務都首先設置 insert intention lock，于是，T1 insert intention lock (5, 7)，T2 insert intention lock (6, 7)，盡管這兩個 insert intention lock 重疊了，T1 和 T2 并不互相阻塞。

如果 gap lock 或 next-key lock 與 insert intention lock 的范圍重疊了，則 gap lock 或 next-key lock 會阻塞 insert intention lock。隔離級別為 RR 時正是利用此特性來解決 phantom row 問題; 盡管 insert intention lock 也是一種特殊的 gap lock，但它和普通的 gap lock 不同，insert intention lock 相互不會阻塞，這極大的提供了插入時的并發性。總結如下：

gap lock 會阻塞 insert intention lock。事實上，gap lock 的存在只是為了阻塞 insert intention lock

gap lock 相互不會阻塞

insert intention lock 相互不會阻塞

insert intention lock 也不會阻塞 gap lock

INSERT 插入行之前，首先在索引記錄之間的間隙上設置 insert intention lock，操作插入成功后，會在新插入的行上設置 index record lock。

我們用下面三圖來說明 insert intention lock 的范圍和特性

上圖演示了：T1 設置了 gap lock(13, 18)，T2 設置了 insert intention lock(16, 18)，兩個鎖的范圍重疊了，于是 T1 gap lock(13, 18)阻塞了 T2 insert intention lock(16, 18)。

上圖演示了：T1 設置了 insert intention lock(13, 18)、index record lock 13;T2 設置了 gap lock(17, 18)。盡管 T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重疊了，但，T2 并未被阻塞。因為 insert intention lock 并不阻塞 gap lock。

上圖演示了：T1 設置了 insert intention lock(11, 18)、index record lock 11;T2 設置了 next-key lock(5, 11]、PRIMARY 上的 index record lock b、gap lock(11, 18)。此時：T1 index record lock 11 和 T2 next-key lock(5, 11]沖突了，因此，T2 被阻塞。

7、自增鎖(AUTO-INC Locks)

表鎖。向帶有 AUTO_INCREMENT 列的表時插入數據行時，事務需要首先獲取到該表的 AUTO-INC 表級鎖，以便可以生成連續的自增值。插入語句開始時請求該鎖，插入語句結束后釋放該鎖(注意：是語句結束后，而不是事務結束后)。

你可能會想，日常開發中，我們所有表都使用 AUTO_INCREMENT 作主鍵，所以會非常頻繁的使用到該鎖。不過，事情可能并不像你想的那樣。在介紹 AUTO-INC 表級鎖之前，我們先來看下和它密切相關的 SQL 語句以及系統變量 innodb_autoinc_lock_mode

INSERT-like 語句

insert

insert … select

replace

replace … select

load data

外加，simple-inserts, bulk-inserts, mixed-mode-inserts

simple-inserts

待插入記錄的條數，提前就可以確定 (語句初始被處理時就可以提前確定) 因此所需要的自增值的個數也就可以提前被確定。

包括：不帶嵌入子查詢的單行或多行的 insert, replace。不過，insert … on duplicate key update 不是

bulk-inserts

待插入記錄的條數，不能提前確定，因此所需要的自增值的個數也就無法提前確定

包括：insert … select, replace … select, load data

在這種情況下，InnoDB 只能每次一行的分配自增值。每當一個數據行被處理時，InnoDB 為該行 AUTO_INCREMENT 列分配一個自增值

mixed-mode-inserts

也是 simple-inserts 語句，但是指定了某些 (非全部) 自增列的值。也就是說，待插入記錄的條數提前能知道，但，指定了部分的自增列的值。

INSERT INTO t1 (c1,c2) VALUES (1, a), (NULL, b), (5, c), (NULL, d

INSERT … ON DUPLICATE KEY UPDATE 也是 mixed-mode，最壞情況下，它就是 INSERT 緊跟著一個 UPDATE，此時，為 AUTO_INCREMENT 列所分配的值在 UPDATE 階段可能用到，也可能用不到。

再看一下系統變量 innodb_autoinc_lock_mode，它有三個候選值 0，1，和 2

8.0.3 之前，默認值是 1，即“連續性的鎖定模式(consecutive lock mode)”;8.0.3 及之后默認值是 2，即“交織性鎖定模式(interleaved lock mode)”

a. 當 innodb_autoinc_lock_mode= 0 時，INSERT-like 語句都需要獲取到 AUTO-INC 表級鎖;

b. 當 innodb_autoinc_lock_mode= 1 時，如果插入行的條數可以提前確定，則無需獲得 AUTO-INC 表級鎖; 如果插入行的條數無法提前確定，則就需要獲取 AUTO-INC 表級鎖。因此，simple-inserts 和 mixed-mode inserts 都無需 AUTO-INC 表級鎖，此時，使用輕量級的 mutex 來互斥獲得自增值;bulk-inserts 需要獲取到 AUTO-INC 表級鎖;

c. 當 innodb_autoinc_lock_mode= 2 時，完全不再使用 AUTO-INC 表級鎖;

我們生產數據庫版本是 5.6.23-72.1，innodb_autoinc_lock_mode=1，而且，我們日常開發中用到大都是 simple-inserts，此時根本就不使用 AUTO-INC 表級鎖，所以，AUTO-INC 表級鎖用到的并不多哦。

LOCK_MODE：AUTO-INC 表級鎖用到的并不多，且，AUTO-INC 鎖是在語句結束后被釋放，較難在 performance_schema.data_locks 中查看到，因此，沒有進行捕獲。感興趣的同學可以使用 INSERT … SELECT 捕獲試試。

8、空間索引(Predicate Locks for Spatial Indexes)

我們平時很少用到 MySQL 的空間索引。所以，本文忽略此類型的鎖

到此為止，MySQL InnoDB 8 種類型的鎖我們就介紹完了。我們以一個例子結束 8 種類型的介紹。

T1 先執行，事務 ID 是 8428;T2 后執行，事務 ID 是 8429

上圖演示了：

任何事務，在鎖定行之前，都需要先加表級鎖 intention lock，即：第三行的 IX 和 *** 行的 IX。

idx_c 是輔助索引，InnoDB 掃描 idx_c 時遇到了 c =222，于是，在 idx_c 上加了 next-key lock，即：第四行的 X。next-key lock 就是 index record lock+gap lock，于是此 next-key lock 鎖定了 idx_c 上值為 222 的索引記錄，以及 222 前面的間隙，也就是間隙(22, 222)。

idx_c 是輔助索引，在主鍵索引之外的任何索引上加 index record lock 時，都需要在該行的主鍵索引上再加 index record lock，于是，又在 PRIMARY 上添加了 index record lock，即：第五行的 X,REC_NOT_GAP。

InnoDB 掃描完 c =222 后，又掃描到了 c =2222，這是 idx_c 上，*** 個不滿足索引掃描條件的索引記錄，于是 InnoDB 在 c =2222 上加 gap lock，c=2222 上的 gap lock 鎖定的范圍是“idx_c 上 2222 前面的間隙”，這本應該是(222, 2222)，但，T1 即將在 idx_c 上插入 c =224，于是，c=2222 上的 gap lock 鎖定的范圍是(224, 2222)。即：第六行的 X,GAP。

InnoDB 即將在 idx_c 上插入 c =224，224 也是不滿足 c =222 的，于是 InnoDB 在 c =224 上加 gap lock，該 gap lock 鎖定了 224 前面的間隙，也就是(222, 224)，即，第七行的 X,GAP。

T2 執行 INSERT 成功后，會在新插入行的加 index record lock，但，T2 在插入之前，首先要作的是得到表級鎖 intention lock 以及設置表的每個索引的 insert intention lock，該鎖的范圍是 (插入值, 向下的一個索引值)，于是，在設置 idx_c 上的 insert intention lock 范圍就是(226, 2222)，這個范圍和事務 T1 第六行 gap lock 范圍(224, 2222) 重疊。于是，事務 T2 被阻塞了，T2 必須等待，直到 T1 釋放第六行的 gap lock。

performance_schema.data_locks 表中并不能看到 T2 的全部鎖，比如，T2 也得在 iux_b 上設置 insert intention lock，但，performance_schema.data_locks 中并沒有這個鎖。關于 performance_schema.data_locks 中顯示了哪些鎖，請見本文 *** 一段。

把這些鎖及其范圍列出來如下圖所示

四、不同的 SQL 加了什么樣的鎖?

OK，我們已經了解了 InnoDB 各種不同類型的鎖，那么，不同 SQL 語句各加了什么樣的鎖呢

我們用最樸素的想法來思考一下，用鎖作什么呢? 鎖要作的就是達到事務隔離的目的，即：兩個并發執行的事務 T1 和 T2，如果 T1 正在修改某些行，那么，T2 要并發讀取 / 修改 / 插入滿足 T1 查詢條件的行時，T2 就必須被阻塞，這是鎖存在的根本原因。index record lock, gap lock, next-key lock 都是實現手段，這些手段使得鎖既能達到目的，還能實現 *** 的并發性。所以，當我們考慮事務 T1 中的 SQL 上加了什么鎖時，就想一下，當 T1 執行時，如果并發的事務 T2 不會觸及到 T1 的行，則 T2 無需被阻塞，如果 T2 的要讀取 / 修改 / 插入滿足 T1 條件的行時，T2 就得被 T1 阻塞。而 T1 阻塞 T2 的具體實現就是：T1 在已存在的行上加 index record lock 使得 T2 無法觸碰已存在的行，以及，T1 在不存在的行上加 gap lock 使得 T2 無法插入新的滿足條件的行。

前面我們說過“加什么樣的鎖”與以下因素相關

當前事務的隔離級別

SQL 是一致性非鎖定讀 (consistent nonlocking read) 還是 DML 或鎖定讀(locking read)

SQL 執行時是否使用了索引，所使用索引的類型(主鍵索引，輔助索引、唯一索引)

我們來看一下，不同的隔離級別下，使用不同的索引時，分別加什么鎖。在討論之前，我們先剔除無需討論的情況

首先，普通 SELECT 使用一致性非鎖定讀，因此根本不存在鎖。無需討論;

再者，作為開發者，我們幾乎從來不會使用到隔離級別 RU 和 Serializable。這兩個隔離級別無需討論。

于是，剩下的就是給定鎖定讀 SELECT 或 DML(INSERT/UPDATE/DELETE)語句，在不同隔離級別下，使用不同類型的索引時，分別會加什么樣的鎖? 直接給出答案，其加鎖原則如下

（一）、RR 時，如果使用非唯一索引進行搜索或掃描，則在所掃描的每一個索引記錄上都設置 next-key lock。

這里“所掃描的每一個索引記錄”是指當掃描執行計劃中所使用的索引時，搜索遇到的每一條記錄。WHERE 條件是否排除掉某個數據行并沒有關系，InnoDB 并不記得確切的 WHERE 條件，InnoDB 倔強的只認其掃描的索引范圍(index range)。

你可能覺得 InnoDB 在設置鎖時蠻不講理，竟然不管 WHERE 條件排除掉的某些行，這不是大大增加了鎖的范圍了嘛。不過，等我們了解了 MySQL 執行 SQL 時的流程，這就好理解了。MySQL 的執行計劃只會選擇一個索引，使用一個索引來進行掃描，MySQL 執行 SQL 語句的流程是，先由 InnoDB 引擎執行索引掃描，然后，把結果返回給 MySQL 服務器，MySQL 服務器會再對該索引條件之外的其他查詢條件進行求值，從而得到最終結果集，而加鎖時只考慮 InnoDB 掃描的索引，由 MySQL 服務器求值的其他 WHERE 條件并不考慮。當然，MySQL 使用 index_merge 優化時會同時使用多個索引的，不過，這個時候設置鎖時也并不特殊，同樣，對于所用到的每一個索引，InnoDB 在所掃描的每一個索引記錄上都設置 next-key lock。

加的鎖一般是 next-key lock，這種鎖住了索引記錄本身，還鎖住了每一條索引記錄前面的間隙，從而阻止其他事務向索引記錄前面緊接著的間隙中插入記錄。

如果在搜索中使用了輔助索引(secondary index)，并且在輔助索引上設置了行鎖，則，InnoDB 還會在相應的聚集索引上設置鎖; 表未定義聚集索引時，InnoDB 自動創建隱藏的聚集索引(索引名字是 GEN_CLUST_INDEX)，當需要在聚集索引上設置鎖時，就設置到此自動創建的索引上。

（二）、RR 時，如果使用了唯一索引的唯一搜索條件，InnoDB 只在滿足條件的索引記錄上設置 index record lock，不鎖定索引記錄前面的間隙; 如果用唯一索引作范圍搜索，依然會鎖定每一條被掃描的索引記錄前面的間隙，并且再在聚集索引上設置鎖。

（三）、RR 時，在 *** 個不滿足搜索條件的索引記錄上設置 gap lock 或 next-key lock。

一般，等值條件時設置 gap lock，范圍條件時設置 next-key lock。此 gap lock 或 next-key lock 鎖住 *** 個不滿足搜索條件的記錄前面的間隙。

（四）、RR 時，INSERT 在插入新行之前，必須首先為表上的每個索引設置 insert intention lock。

每個 insert intention lock 的范圍都是(待插入行的某索引列的值, 此索引上從待插入行給定的值向下的 *** 個索引值)。只有當 insert intention lock 與某個 gap lock 或 next-key lock 沖突時，才能在 performance_schema.data_locks 看到 insert intention lock。

（五）、RC 時，InnoDB 只在完全滿足 WHERE 條件的行上設置 index record lock。

（六）、RC 時，禁用了 gap lock。

正因為此，RC 時不存在 gap lock 或 next-key lock。這是為什么呢? 我們想一想啊，gap lock 是用來解決 phantom row 問題的，gap lock 封鎖的區間內不能插入新的行，因為插入時的 insert intention lock 會和 gap lock 沖突，從而阻止了新行的插入。但，隔離級別 RC 是允許 phantom row 的，因此 RC 時 gap lock 是被禁用的。

（七）、RR 或 RC 時，對于主鍵或唯一索引，當有重復鍵錯誤 (duplicate-key error) 時，會在重復的索引記錄上設置 shared next-key lock 或 shared index record lock。這可能會導致死鎖。

假設 T1, T2, T3 三個事務，T1 已經持有了 X 鎖，T2 和 T3 發生了重復鍵錯誤，因此 T2 和 T3 都在等待獲取 S 鎖，這個時候，當 T1 回滾或提交釋放掉了 X 鎖，則 T2 和 T3 就都獲取到了 S 鎖，并且，T2 和 T3 都請求 X 鎖，“T2 和 T3 同時持有 S 鎖，且都在請求 X 鎖”，于是死鎖就產生了。

好了，規則都列出來了，是時候實踐一把了。下面在展示鎖時，我們同時指出了當前所使用的隔離級別，表上的索引以及事務的 SQL 語句。

實踐一：搜索時無法使用索引，即全表掃描時，InnoDB 在表的全部行上都加鎖

上圖演示了：搜索條件無法使用索引時，InnoDB 不得不在表的全部行上都加鎖。所以，索引實在太重要了，查詢時，它能加快查詢速度; 更新時，除了快速找到指定行，它還能減少被鎖定行的范圍，提高插入時的并發性。

實踐二：唯一索引和非唯一索引、等值查詢和范圍查詢加鎖的不同

搜索時使用唯一索引作等值查詢時，InnoDB 只需要加 index record lock; 搜索時使用唯一索引作范圍查詢時或使用非唯一索引作任何查詢時，InnoDB 需要加 next-key lock 或 gap lock。

示例 1 演示了：使用非唯一索引 idx_c 搜索或掃描時，InnoDB 要鎖住索引本身，還要鎖住索引記錄前面的間隙，即 next-key lock: X 和 gap lock: X,GAP。next-key lock 既鎖住索引記錄本身，還鎖住該索引記錄前面的間隙，gap lock 只鎖住索引記錄前面的間隙。等值條件時，在 *** 一個不滿足條件的索引記錄上設置 gap lock。

示例 2 演示了：使用唯一索引 iux_b 的唯一搜索條件，即，使用唯一索引執行等值查找時，InnoDB 只需鎖住索引本身，即 index record lock: X, REC_NOT_GAP，并不鎖索引前面的間隙。

示例 3 演示了：使用唯一索引 iux_b 進行范圍掃描時，依然需要鎖定掃描過的每一個索引記錄，并且鎖住每一條索引記錄前面的間隙，即 next-key lock: X。范圍條件時，在 *** 一個不滿足條件的索引記錄上設置 next-key lock。

實踐三：不同隔離級別加鎖的不同

無論何種隔離級別，SQL 語句執行時，都是先由 InnoDB 執行索引掃描，然后，返回結果集給 MySQL 服務器，MySQL 服務器再對該索引條件之外的其他查詢條件進行求值，從而得到最終結果集。

上圖中，在不同的隔離級別下，執行了相同的 SQL。無論何種隔離級別，PRIMARY 上的 index record lock 總是會加的，我們不討論它。在 idx_b 上，隔離級別為 RC 時，InnoDB 加了 index record lock，即：X,REC_NOT_GAP，隔離級別為 RR 時，InnoDB 加了 next-key lock，即 X。注意：RC 時沒有 gap lock 或 next-key lock 哦。

上圖演示了：事務的隔離級別也會影響到設置哪種鎖。如我們前面所說，gap lock 是用來阻止 phantom row 的，而 RC 時是允許 phantom row，所以，RC 時禁用了 gap lock。因此，上圖中，RC 時沒有在索引上設置 gap lock 或 next-key lock。

實踐四：操作不存在的索引記錄時，也需要加鎖

上圖中，idx_b 上并不存在 b =266 的索引記錄，那么，當更新 b =266 的記錄時，是否需要加鎖呢? 是的，也需要加鎖

無論 b =266 是否存在，RR 時，InnoDB 在 *** 個不滿足搜索條件的索引記錄上設置 gap lock 或 next-key lock。一般，等值條件時設置 gap lock，范圍條件時設置 next-key lock。上圖中是等值條件，于是 InnoDB 設置 gap lock，即上圖的 X,GAP，其范圍是(226, 2222)，正是此 gap lock 使得并發的事務無法插入 b 列大于等于 266 的值，RC 時，由于 gap lock 是被禁止的，因此，并不會加 gap lock，并發的事務可以插入 b 列大于等于 266 的值。

上圖演示了：操作不存在的索引記錄時，也需要加鎖。

實踐五：重復鍵錯誤 (duplicate-key error) 時，會加共享鎖。這可能會導致死鎖。

對于主鍵或唯一索引，當有重復鍵錯誤 (duplicate-key error) 時，會在重復的索引記錄上設置 shared next-key lock 或 shared index record lock。這可能會導致死鎖。

上圖演示了：T1 在主鍵 1 上設置 exclusive index record lock。T2 和 T3 插入時，會產生重復鍵錯誤，于是 T2 和 T3 都在主鍵 1 上設置了 shared next-key lock。如上圖所示

如果此時，T1 rollback 釋放掉其所持有的 index record lock，則 T2 和 T3 等待獲取的 shared next-key lock 都成功了，然后，T2 和 T3 爭奪主鍵 1 上的 index record lock，于是 T2 和 T3 就死鎖了，因為它倆都持有 shard next-key lock，雙方誰都不會放棄已經得到的 shared next-key lock，于是，誰都無法得到主鍵 1 的 index record lock。

需要明確的是死鎖的可能性并不受隔離級別的影響，因為隔離級別改變的是讀操作的行為，而死鎖是由于寫操作產生的。死鎖并不可怕，MySQL 會選擇一個犧牲者，然后，在系統變量 innodb_lock_wait_timeout 指定的秒數達到后，自動回滾犧牲者事務; 從 MySQL5.7 開始，新加入了系統變量 innodb_deadlock_detect(默認 ON)，如果開啟此變量，則 MySQL 不會再等待，一旦探測到死鎖，就立即回滾犧牲者事務。

上圖演示了：在上圖的狀態下，當 T1 commit 時，T1 釋放了主鍵 1 上的 index record lock，于是 T2 和 T3 等待獲取的 shared next-key lock 都成功了，然后，T2 和 T3 爭奪主鍵 1 上的 index record lock，于是 T2 和 T3 死鎖了，因為它倆都持有 shard next-key lock，雙方誰都不會放棄已經得到的 shared next-key lock，于是，誰都無法得到主鍵 1 的 index record lock。

五、performance_schema.data_locks 中能看到全部的鎖嗎?

顯而易見，performance_schema.data_locks 并未顯示全部的鎖，那么，它顯示了哪些鎖呢? 很不幸，我并未找到文檔說這事，盡管文檔 (https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html) 說：“事務持有的每一個鎖以及事務被阻塞的每一個鎖請求，都在該表中占據一行”，但，我們很多例子都表明，它并未顯示全部的鎖。根據我的試驗，我猜測 performance_schema.data_locks 顯示的是 WHERE 條件所觸碰到的索引上的鎖，“WHERE 條件所觸碰到的索引”是指 SQL 實際執行時所使用的索引，也就是 SQL 執行計劃的 key 列所顯示的索引，正因為此，INSERT 時看不到任何鎖，update g set a=a+1 where b=22 時只看到 idx_b 上的鎖。需要強調的是，這是我自己試驗并猜測的，我并未在文檔中看到這種說法。

假設 T1 和 T2 兩個事務操作同一個表，先執行 T1，此時盡管 performance_schema.data_locks 中只顯示 T1 的 WHERE 條件所觸碰到的索引上的鎖，但是，事實上在 T1 的 WHERE 條件觸碰不到的索引上，也是會設置鎖的。盡管表的索引 idx 并未被 T1 所觸碰到，即 performance_schema.data_locks 顯示 T1 在索引 idx 并沒有設置任何鎖，但，當 T2 執行鎖定讀 / 插入 / 更新 / 刪除時觸碰到了索引 idx，T2 才恍然發現，原來 T1 已經在索引 idx 上加鎖了。

我們來看下面的三個例子

“performance_schema.data_locks 無法看到全部鎖”示例一

上圖演示了：T1 執行時，只觸碰到了索引 idx_b，T1 執行完后，在 performance_schema.data_locks 中只能看到 idx_b 上的鎖，看起來 T1 并未在 idx_a 上設置任何鎖; 但，當 T2 執行觸碰到了索引 idx_a 時，T2 才恍然發現，原來 T1 已經在 idx_a 上設置了 index record lock 啦。

“performance_schema.data_locks 無法看到全部鎖”示例二

插入新行時，會先設置 insert intention lock，插入成功后再在插入完成的行上設置 index record lock。

上圖演示了：T1 插入了新行，但，在 performance_schema.data_locks 中，我們既看不到 T1 設置的 insert intention lock，也看不到 T1 設置的 index record lock。這是因為 T1 的 WHERE 條件并未觸碰到任何索引(T1 根本不存在 WHERE 條件)，因此我們看不到 T1 的這兩個鎖; 但，當 T2 要刪除 T1 新插入的行時，T2 才恍然發現，原來 T1 已經在索引 c2 上設置了 index record lock 啦。

“performance_schema.data_locks 無法看到全部鎖”示例三