簡單來說，當 UPDATE 語句的 WHERE 條件使用了組合索引，并且需要鎖定不存在的“間隙”來防止幻讀時，就會產生間隙鎖。間隙鎖的范圍取決于 WHERE 條件如何利用組合索引，以及數據庫的隔離級別。

我們用圖書館的例子。比如：

• 分類（Category）：計算機、歷史、文學
• 出版年份（PublishYear）：2020、2021、2022
• 書名（Title）：具體書名

現在，圖書館對 (Category, PublishYear, Title) 建立了一個組合索引。這意味著書架上的書是先按分類排好，然后在每個分類里再按出版年份排好，最后在每個年份里按書名排好。

假設你現在是圖書館管理員，要進行一些更新操作。

核心問題： 當你更新某些書時，為了防止別人在你更新期間偷偷“插入”或“刪除”一些書，導致你更新完后發現書的數量不對（幻讀），你需要鎖定一些“空位”（間隙）。這些空位鎖定的范圍有多大？

深入理解：

間隙鎖是 InnoDB 存儲引擎在 可重復讀（Repeatable Read） 隔離級別下，為了解決 幻讀（Phantom Read） 問題而引入的一種鎖。它鎖定的是索引記錄之間的“間隙”，或者索引記錄之前/之后的空間。

當 UPDATE 語句使用組合索引時，間隙鎖的范圍會變得比較復雜，因為它涉及到索引的“最左前綴原則”以及 WHERE 條件的匹配程度。

我們分幾種情況來討論：

情況一：WHERE 條件完全匹配組合索引的“最左前綴”或全部列，且是等值查詢。

場景： 你想更新“計算機”分類下，“2021年”出版的，名為“數據庫原理”這本書的價格。
UPDATE books SET price = 100 WHERE category = '計算機' AND publish_year = 2021 AND title = '數據庫原理';

索引： (Category, PublishYear, Title)

1. 數據庫會通過組合索引精確找到這本書。
2. 由于是精確匹配，并且找到了具體的記錄，數據庫會給這條記錄加上行級排他鎖（X Lock）。
3. 間隙鎖： 在這種情況下，如果 WHERE 條件能夠精確匹配到一條或幾條存在的記錄，并且這些記錄是連續的，那么間隙鎖的范圍通常會非常小，甚至可能沒有明顯的間隙鎖（因為行鎖已經足夠）。
   • 為什么？ 因為你已經找到了具體的書，你只需要鎖住這本書本身。如果其他人在這個位置插入一本完全相同的書（這通常被唯一索引阻止），或者插入一本在排序上緊鄰的書，并不會影響你當前對“數據庫原理”這本書的更新。
   • 例外： 如果 WHERE 條件匹配到了一個不存在的記錄，但這個記錄可能存在于某個間隙中（例如，你嘗試更新一本不存在的書），那么為了防止幻讀，可能會在這個不存在的記錄應該插入的位置前后加上間隙鎖。但對于精確匹配存在的記錄，間隙鎖通常不是主要考慮。

情況二：WHERE 條件只使用了組合索引的部分列（最左前綴），且是等值查詢。

場景： 你想更新“計算機”分類下，所有“2021年”出版的書的價格。
UPDATE books SET price = 100 WHERE category = '計算機' AND publish_year = 2021;

索引： (Category, PublishYear, Title)

1. 數據庫會通過組合索引找到所有 category = '計算機' 且 publish_year = 2021 的書。
2. 它會給這些找到的所有行都加上行級排他鎖（X Lock）。
3. 間隙鎖： 這是間隙鎖發揮作用的關鍵場景。為了防止其他事務在“計算機”分類下，“2021年”出版的書籍之間或前后插入新的書籍（例如，插入一本 title = '操作系統' 的書），數據庫會在這些行之間以及這些行所在的“范圍”前后加上間隙鎖。
   • 范圍： 間隙鎖會鎖定 (Category='計算機', PublishYear=2021) 這個“范圍”內的所有可能的 Title 值。
   • 具體表現： 假設在索引中，('計算機', 2021, '算法') 后面是 ('計算機', 2021, '數據結構')。那么，('計算機', 2021, '算法') 和 ('計算機', 2021, '數據結構') 之間的間隙會被鎖定。同時，('計算機', 2021, '數據結構') 之后，直到下一個 ('計算機', 2022, ...) 或 ('歷史', ...) 之前的間隙也會被鎖定。
   • 目的： 確保在你更新期間，沒有人能插入新的 ('計算機', 2021, ...) 的書，從而避免幻讀。

情況三：WHERE 條件使用了范圍查詢（BETWEEN, >, <, LIKE 等）。

場景： 你想更新“計算機”分類下，所有“2020年到2022年”出版的書的價格。
UPDATE books SET price = 100 WHERE category = '計算機' AND publish_year BETWEEN 2020 AND 2022;

索引： (Category, PublishYear, Title)

1. 數據庫會通過組合索引找到所有 category = '計算機' 且 publish_year 在 2020 到 2022 之間的書。
2. 它會給這些找到的所有行都加上行級排他鎖（X Lock）。
3. 間隙鎖： 間隙鎖的范圍會覆蓋整個查詢的范圍。
   • 起始點： 間隙鎖會從 ('計算機', 2020, MIN_VALUE) 之前的間隙開始。
   • 結束點： 間隙鎖會延伸到 ('計算機', 2022, MAX_VALUE) 之后的間隙。
   • 中間： 在 ('計算機', 2020, ...) 到 ('計算機', 2022, ...) 之間的所有行和間隙都會被鎖定。這意味著，即使 ('計算機', 2021, '操作系統') 這本書不存在，它應該存在的位置也會被間隙鎖覆蓋，防止其他事務插入。
   • 目的： 確保在你更新這個范圍內的書時，沒有人能插入或刪除任何符合這個范圍的新書，從而避免幻讀。

情況四：WHERE 條件沒有使用組合索引的最左前綴，或者沒有使用索引。

場景： 你想更新所有“2021年”出版的書的價格（不考慮分類）。
UPDATE books SET price = 100 WHERE publish_year = 2021;

索引： (Category, PublishYear, Title)

1. 由于 WHERE 條件沒有使用組合索引的最左前綴（Category），數據庫可能無法有效利用這個組合索引。
2. 全表掃描： 數據庫可能會進行全表掃描來找到所有 publish_year = 2021 的行。
3. 鎖的升級： 在這種情況下，為了保證事務的隔離性，數據庫可能會采取更粗粒度的鎖策略：
   • 表級鎖： 最壞的情況是，數據庫為了避免幻讀，直接對整個 books 表加表級排他鎖（Table-Level X Lock）。這意味著在你的 UPDATE 語句執行期間，其他任何對 books 表的讀寫操作都會被阻塞。
   • 行鎖 + 間隙鎖（但范圍可能很大）： 即使是行鎖，由于是全表掃描，它會掃描所有行，并對符合條件的行加行鎖。同時，為了防止幻讀，它可能需要在整個掃描過程中，對所有掃描到的間隙都加上間隙鎖。這實際上等同于鎖定了整個表，因為間隙鎖會覆蓋所有可能的插入點。
   • 目的： 確保在全表掃描并更新的過程中，沒有新的行被插入或刪除，從而避免幻讀。

情況五：WHERE 條件使用了組合索引，但條件不精確，導致索引掃描范圍很大。

場景： 你想更新所有 category 以“C”開頭的書的價格。
UPDATE books SET price = 100 WHERE category LIKE 'C%';

索引： (Category, PublishYear, Title)

1. 數據庫會利用 Category 上的索引（最左前綴）來查找。
2. 它會掃描所有 Category 以 ‘C’ 開頭的索引項。
3. 間隙鎖： 間隙鎖的范圍會覆蓋所有 Category 以 ‘C’ 開頭的索引項以及它們之間的所有間隙。
   • 具體表現： 假設你的索引中有 ('計算機', ...)、('財務', ...) 等。那么從第一個以 ‘C’ 開頭的 Category 之前，到最后一個以 ‘C’ 開頭的 Category 之后，所有相關的間隙都會被鎖定。
   • 目的： 防止其他事務插入新的 Category 以 ‘C’ 開頭的書籍，從而避免幻讀。

總結間隙鎖的范圍：

間隙鎖的范圍是動態的，它取決于：

1. 隔離級別： 間隙鎖主要在 可重復讀（Repeatable Read） 隔離級別下生效。在讀已提交（Read Committed）隔離級別下，通常不會有間隙鎖（除非是外鍵約束檢查等特殊情況）。
2. WHERE 條件如何利用索引：
   • 精確匹配（等值查詢）到存在記錄： 間隙鎖范圍最小，通常只鎖定行本身。
   • 范圍查詢（BETWEEN, >, <, LIKE 等）或部分索引列匹配： 間隙鎖會鎖定整個查詢范圍內的所有行和間隙，以防止幻讀。范圍越大，間隙鎖的范圍也越大。
   • 無法使用索引或索引效率低下： 可能導致全表掃描，進而可能升級為表級鎖，或者間隙鎖覆蓋整個表，導致并發性急劇下降。
3. 索引的類型：
   • 唯一索引： 如果 WHERE 條件通過唯一索引精確匹配到一條記錄，通常只需要行鎖，間隙鎖的影響很小。
   • 非唯一索引： 非唯一索引更容易產生間隙鎖，因為存在多個相同值的可能性，以及在這些相同值之間插入新值的可能性。

核心思想： 間隙鎖是為了保護一個“范圍”內的“不存在”的數據，防止其他事務在這個范圍內插入新的數據，從而破壞當前事務的“可重復讀”的幻象。UPDATE 操作在定位到要修改的行后，為了確保這些行在事務提交前不會被其他事務的插入操作所“包圍”或“改變其相對位置”，就會對相關的間隙加鎖。