博客 / 詳情

返回

技術分享 | MySQL間隙鎖原理深度詳解

本文為墨天輪數據庫管理服務團隊第150期技術分享，內容原創，作者為技術顧問陳洋，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

一、間隙鎖概述

間隙鎖（Gap Lock）是InnoDB存儲引擎在REPEATABLE READ（可重複讀）隔離級別下為了解決幻讀（Phantom Read）問題而引入的一種鎖機制。它鎖定的是索引記錄之間的“間隙”，而不是實際存在的記錄。這意味着，即使間隙中沒有數據，間隙鎖也能阻止其他事務在該間隙內插入新的數據，從而保證了在同一事務中多次讀取相同範圍的數據時，結果集保持一致。

二、幻讀問題

幻讀是指在同一個事務中，兩次執行相同的查詢語句，但第二次查詢卻看到了第一次查詢沒

  1. 事務A在某個範圍內執行了查詢。
  2. 事務B在該範圍內插入了新的行並提交。
  3. 事務A再次執行相同的查詢，看到了事務B插入的新行，導致前後兩次查詢結果不一致。

在REPEATABLE READ隔離級別下，MySQL通過兩種方式解決幻讀問題：

  • 快照讀（Snapshot Read）：對於普通的SELECT語句，InnoDB通過MVCC（多版本併發控制）機制，在事務開始時生成一個Read View（一致性視圖），後續的快照讀都基於這個視圖，因此不會看到其他事務提交的新數據。這種方式解決了普通SELECT語句的幻讀。
  • 當前讀（Current Read）：對於SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、INSERT、UPDATE、DELETE等語句，它們需要讀取最新的數據版本，因此稱為當前讀。在當前讀情況下，MVCC無法解決幻讀問題，此時就需要間隙鎖來防止其他事務插入新數據。

三、間隙鎖的實現原理

間隙鎖是基於索引的，它鎖定的是索引記錄之間的空隙。當對某個範圍的數據進行當前讀操作時，InnoDB不僅會鎖定符合條件的記錄本身（記錄鎖），還會鎖定這些記錄前後的間隙，以及第一個記錄之前的間隙和最後一個記錄之後的間隙。這種記錄鎖和間隙鎖的組合被稱為Next-Key Lock。

3.1 Next-Key Lock

Next-Key Lock是InnoDB默認的行鎖類型，它結合了記錄鎖（Record Lock）和間隙鎖（Gap Lock）。一個Next-Key Lock會鎖定一個索引記錄以及該記錄之前的間隙。其鎖定範圍是(前一個索引記錄, 當前索引記錄]。

例如，在一個索引包含值10、20、30的表中，Next-Key Lock可能鎖定的區間包括：

  • (-∞, 10]
  • (10, 20]
  • (20, 30]
  • (30, +∞)

3.2 間隙鎖的特性

  • 只在REPEATABLE READ隔離級別下生效：在READ COMMITTED（讀已提交）隔離級別下，沒有間隙鎖，因此可能會出現幻讀。
  • 不區分共享鎖和排他鎖：間隙鎖的唯一目的是防止其他事務插入數據，因此它不區分共享（S）鎖和排他（X）鎖。任何事務持有間隙鎖，都會阻止其他事務在該間隙內插入數據。
  • 間隙鎖之間不衝突：不同事務可以同時持有同一個間隙的間隙鎖。因為間隙鎖的目的是阻止插入，而不是阻止讀取或修改已存在的數據。
  • 與索引相關：間隙鎖是加在索引上的，而不是數據行本身。如果查詢沒有使用索引，或者使用的索引不能有效地限制掃描範圍，間隙鎖可能會鎖定整個表，導致併發性能下降。

四、間隙鎖的加鎖規則

間隙鎖的加鎖規則相對複雜，主要取決於查詢條件、索引類型以及是否是唯一索引：

  1. 等值查詢與唯一索引：
  • 如果查詢條件是唯一索引的等值查詢，並且找到了對應的記錄，那麼Next-Key Lock會退化為記錄鎖，只鎖定該行，不會產生間隙鎖。因為唯一索引保證了該值是唯一的，不會有新的數據插入到該位置。
  • 如果查詢條件是唯一索引的等值查詢，但沒有找到對應的記錄，那麼會在不存在的記錄位置形成一個間隙鎖，鎖定該間隙，防止其他事務插入該值。
  1. 等值查詢與非唯一索引：
  • 如果查詢條件是非唯一索引的等值查詢，無論是否找到記錄，都會在掃描到的符合條件的記錄以及其前後的間隙上加Next-Key Lock。這是因為非唯一索引可能存在多個相同的值，需要鎖定一個範圍來防止幻讀。
  1. 範圍查詢：
  • 對於範圍查詢（如WHERE id > 10或WHERE id BETWEEN 10 AND 20），無論是否是唯一索引，都會在掃描到的所有符合條件的記錄及其前後的間隙上加Next-Key Lock。掃描會持續到第一個不滿足條件的記錄，並鎖定該記錄之前的間隙。
  1. 無索引或索引失效：
  • 如果查詢沒有使用索引，或者索引失效，那麼InnoDB會進行全表掃描。在這種情況下，為了防止幻讀，InnoDB會給整個表的所有索引記錄都加上Next-Key Lock，這會嚴重影響併發性能。

五、間隙鎖的示例

假設有一個products表，其中包含id（主鍵）、name和price字段，並且id是自增主鍵。

CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `products` (`id`, `name`, `price`) VALUES
(1, 'Laptop', 1200.00),
(5, 'Mouse', 25.00),
(10, 'Keyboard', 75.00);

當前products表中的id值有1, 5, 10。那麼存在的間隙包括：

  • (-∞, 1]
  • (1, 5]
  • (5, 10]
  • (10, +∞)

示例1：等值查詢（唯一索引，找到記錄）

事務A:

BEGIN;
SELECT * FROM products WHERE id = 5 FOR UPDATE;

分析：id是主鍵（唯一索引），查詢條件是等值查詢且找到了記錄。此時，Next-Key Lock會退化為記錄鎖，只鎖定id = 5的行。其他事務可以插入id = 2或id = 7的記錄，但不能修改或刪除id = 5的記錄。

示例2：等值查詢（唯一索引，未找到記錄）

事務A:

BEGIN;
SELECT * FROM products WHERE id = 3 FOR UPDATE;

分析：id是主鍵（唯一索引），查詢條件是等值查詢但未找到記錄。此時，會在(1, 5]這個間隙上加間隙鎖，阻止其他事務插入id為2、3、4的記錄。例如，事務B嘗試插入id = 2的記錄會被阻塞。

示例3：範圍查詢

事務A:

BEGIN;
SELECT * FROM products WHERE id > 5 FOR UPDATE;

分析：查詢條件是範圍查詢。InnoDB會掃描id = 10的記錄，並鎖定(5, 10]和(10, +∞)這兩個間隙。這意味着，其他事務不能插入id為6、7、8、9的記錄，也不能插入id大於10的記錄。同時，id = 10的記錄本身也會被鎖定。

六、間隙鎖的優缺點

優點：

  • 解決幻讀：在REPEATABLE READ隔離級別下，間隙鎖有效地防止了幻讀的發生，保證了數據的一致性。

缺點：

  • 降低併發性：間隙鎖鎖定的不是具體的行，而是索引的範圍，這可能導致不必要的鎖定，從而降低了數據庫的併發性能。即使沒有數據，間隙也會被鎖定。
  • 死鎖風險：間隙鎖增加了死鎖的風險，因為多個事務可能在不同的間隙上持有鎖，並嘗試獲取對方持有的間隙上的鎖，從而形成死鎖。
  • 難以理解和排查：間隙鎖的加鎖規則相對複雜，使得在出現性能問題或死鎖時，排查和定位問題變得更加困難。

七、總結

間隙鎖是MySQL InnoDB存儲引擎在REPEATABLE READ隔離級別下解決幻讀問題的關鍵機制。理解其原理、加鎖規則以及優缺點對於數據庫性能優化和問題排查至關重要。在實際應用中，應根據業務需求和併發量，合理選擇事務隔離級別，並注意避免因間隙鎖導致的性能瓶頸和死鎖問題。

八、間隙鎖的排查與定位

在實際的數據庫運維和開發中，間隙鎖可能導致性能問題甚至死鎖。因此，瞭解如何排查和定位間隙鎖是至關重要的。

8.1 識別間隙鎖導致的性能問題

  • 慢查詢日誌

    ：檢查MySQL的慢查詢日誌，特別是那些執行時間長、涉及範圍查詢且隔離級別為REPEATABLE READ的SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE語句。這些語句很可能觸發了間隙鎖。

  • SHOW PROCESSLIST：通過SHOW PROCESSLIST命令可以查看當前正在執行的SQL語句。如果發現有大量事務長時間處於Locked或Waiting for table metadata lock狀態，並且涉及的SQL語句是範圍查詢，則可能與間隙鎖有關。
  • information_schema數據庫：information_schema數據庫提供了許多關於MySQL服務器狀態的信息。以下幾個表對於排查鎖問題非常有用：
  • information_schema.INNODB_TRX：顯示當前所有正在運行的InnoDB事務的信息，包括事務ID、事務狀態、鎖等待情況等。
  • information_schema.INNODB_LOCKS：顯示當前被鎖定的資源以及持有鎖的事務信息。可以查看鎖的類型（如RECORD、GAP、AUTO_INC等）和鎖定的索引。
  • information_schema.INNODB_LOCK_WAITS：顯示當前存在的鎖等待關係，可以幫助識別死鎖或長時間的鎖等待。

8.2 使用SHOW ENGINE INNODB STATUS排查

SHOW ENGINE INNODB STATUS命令是排查InnoDB存儲引擎問題（包括鎖問題）的強大工具。它會輸出大量關於InnoDB內部狀態的信息，其中LATEST DETECTED DEADLOCK和TRANSACTIONS部分對於分析間隙鎖導致的死鎖和鎖等待尤為重要。

輸出解讀要點：

  • LATEST DETECTED DEADLOCK：如果發生了死鎖，這一部分會詳細記錄最近一次死鎖的信息，包括死鎖涉及的事務、它們嘗試獲取的鎖、持有的鎖以及等待的資源。通過分析這裏的信息，可以明確是哪些事務在哪些間隙上發生了死鎖。
  • RECORD LOCKS：表示記錄鎖。
  • GAP LOCKS：表示間隙鎖。
  • NEXT-KEY LOCKS：表示臨鍵鎖（記錄鎖+間隙鎖）。
  • TRANSACTIONS：這一部分列出了所有活躍的事務，包括它們的事務ID、狀態、執行的SQL語句、持有的鎖以及等待的鎖。通過查看LOCK WAIT狀態的事務，可以找到正在等待鎖的事務，並進一步分析其等待的原因。
  • 查找LOCK WAIT狀態的事務。
  • 查看LOCKED TABLES和WAITING FOR THIS LOCK TO BE GRANTED部分，瞭解事務正在等待的鎖類型和資源。
  • 結合SQL語句，判斷是否是間隙鎖導致的等待。

示例：

SHOW ENGINE INNODB STATUS\G

執行上述命令後，會得到一個詳細的報告。你需要仔細閲讀其中的LATEST DETECTED DEADLOCK和TRANSACTIONS部分。

8.3 模擬和復現間隙鎖

為了更好地理解和排查間隙鎖，可以在測試環境中模擬和復現間隙鎖的場景。這通常涉及：

  1. 設置數據庫隔離級別為REPEATABLE READ。
  2. 創建包含索引的測試表。
  3. 開啓多個事務，在不同的事務中執行會觸發間隙鎖的SQL語句（如範圍查詢的FOR UPDATE語句），並嘗試在間隙中插入數據，觀察事務的阻塞和死鎖情況。

通過模擬，可以加深對間隙鎖行為的理解，並驗證排查方法是否有效。

8.4 避免間隙鎖導致的性能問題

  • 降低隔離級別：如果業務允許，可以將事務隔離級別從REPEATABLE READ降至READ COMMITTED。在READ COMMITTED隔離級別下，InnoDB不會使用間隙鎖，從而避免了幻讀和間隙鎖帶來的性能問題。但需要注意的是，這可能會引入其他併發問題，需要根據業務場景權衡。
  • 優化SQL語句
  • 儘量使用等值查詢，避免不必要的範圍查詢。
  • 確保查詢條件能夠命中索引，避免全表掃描。全表掃描會導致整個表被間隙鎖鎖定，嚴重影響併發。
  • 對於範圍查詢，儘量縮小查詢範圍，減少間隙鎖鎖定的範圍。
  • 避免不必要的FOR UPDATE或LOCK IN SHARE MODE：只有在確實需要對查詢結果進行更新或需要保證數據一致性時，才使用這些語句。
  • 拆分大事務：將長時間運行的大事務拆分為多個小事務，減少事務持有鎖的時間，從而降低間隙鎖衝突的概率。
  • 使用樂觀鎖：對於某些業務場景，可以考慮使用樂觀鎖（通過版本號或時間戳）來替代悲觀鎖，減少數據庫層面的鎖競爭。
  • 調整索引：合理設計索引，確保查詢能夠高效地利用索引，減少不必要的全表掃描或索引掃描。

通過上述方法，可以有效地排查、定位和避免MySQL間隙鎖帶來的性能問題和死鎖風險。

九、間隙鎖死鎖案例分析與解決方案

間隙鎖雖然解決了幻讀問題，但它引入了死鎖的風險。當兩個或多個事務在獲取間隙鎖時形成循環等待，就會發生死鎖。以下是一個典型的間隙鎖死鎖案例及其解決方案。

9.1 案例場景：併發插入導致的間隙鎖死鎖

假設我們有一個orders表，其中包含id（主鍵）、order_no（唯一索引）和amount字段。為了簡化，我們只關注id和order_no。

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(255) UNIQUE,
  `amount` decimal(10,2),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `orders` (`id`, `order_no`, `amount`) VALUES
(1, 'A001', 100.00),
(5, 'A005', 200.00),
(10, 'A010', 300.00);

當前orders表中的order_no值有’A001’, ‘A005’, ‘A010’。假設現在有兩個事務（事務A和事務B）幾乎同時嘗試插入order_no在’A001’和’A005’之間的記錄，例如’A003’和’A004’。

事務A:

-- 事務A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 步驟1: 事務A嘗試插入 'A003'
INSERT INTO orders (order_no, amount) VALUES ('A003', 150.00);

事務B:

-- 事務B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 步驟1: 事務B嘗試插入 'A004'
INSERT INTO orders (order_no, amount) VALUES ('A004', 180.00);

死鎖發生過程：

  1. 事務A執行INSERT ('A003', ...)：
  • 為了插入’A003’，InnoDB需要檢查order_no唯一索引中(A001, A005)這個間隙。事務A會在這個間隙上加一個意向插入鎖（Insert Intention Lock），這是一種特殊的間隙鎖，表示事務A打算在這個間隙中插入一條記錄。同時，為了保證唯一性，它可能還需要對A001和A005這兩個記錄加S鎖或X鎖（具體取決於索引類型和操作）。
  1. 事務B執行INSERT ('A004', ...)：
  • 幾乎同時，事務B也嘗試插入’A004’。它也需要檢查order_no唯一索引中(A001, A005)這個間隙。事務B也會在這個間隙上加一個意向插入鎖。
  1. 衝突與死鎖：
  • 雖然意向插入鎖之間通常不會直接衝突，但當兩個事務都試圖在同一個間隙內插入數據時，它們可能會嘗試獲取間隙內的其他鎖（例如，為了檢查唯一性而對相鄰記錄加的鎖），或者在內部對間隙進行更細粒度的鎖定。在這種情況下，如果事務A持有了間隙(A001, A005)的一部分鎖，並等待事務B持有的另一部分鎖；同時事務B持有了間隙(A001, A005)的另一部分鎖，並等待事務A持有的鎖，就會形成循環等待，導致死鎖。
  • MySQL的死鎖檢測機制會發現這個循環，並選擇其中一個事務作為“犧牲品”（通常是修改行數較少的事務），回滾該事務，從而解除死鎖。被回滾的事務會收到ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction錯誤。

9.2 解決方案

針對這種因間隙鎖導致的死鎖，可以採取以下幾種策略：

  1. 降低事務隔離級別：將事務隔離級別從REPEATABLE READ降至READ COMMITTED。在READ COMMITTED級別下，INSERT操作通常只在插入的行上加行鎖，而不會加間隙鎖，從而避免了這類死鎖。但需要注意的是，READ COMMITTED隔離級別下可能出現幻讀（對於快照讀），需要根據業務場景權衡。
  2. 優化SQL語句和索引：
  • 避免在非唯一索引上進行範圍查詢的FOR UPDATE或LOCK IN SHARE MODE：如果業務允許，儘量避免在非唯一索引上使用FOR UPDATE或LOCK IN SHARE MODE進行範圍查詢，因為這會更容易觸發間隙鎖。
  • 合理設計唯一索引：如果order_no是唯一的，並且業務邏輯允許，可以考慮在插入前先進行一次SELECT ... FOR UPDATE來預先鎖定範圍，但這會降低併發性。
  1. 應用程序層面處理死鎖：在應用程序代碼中捕獲死鎖異常（錯誤碼1213），並實現事務重試機制。當發生死鎖時，回滾當前事務，並等待一小段時間後重新嘗試執行事務。這是處理死鎖的常見且有效的方法。
  2. 使用自增主鍵作為插入依據：如果order_no不是嚴格遞增的，或者其生成邏輯複雜，可以考慮讓id（自增主鍵）作為主要的插入依據，而order_no作為普通唯一索引。在某些情況下，這可以減少間隙鎖的衝突。
  3. 批量插入：如果需要插入大量數據，可以考慮使用批量插入（INSERT INTO ... VALUES (...), (...);）而不是單條插入。批量插入可以減少事務的數量和鎖的競爭。
  4. 調整業務邏輯：重新審視業務邏輯，看是否可以調整操作順序或數據模型，以減少併發事務對相同間隙的競爭。

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service

MySQL , 數據庫 , SQL
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.