在 MySQL 的 InnoDB 存儲引擎中,非唯一索引觸發間隙鎖的核心原因是為了 防止幻讀(Phantom Read),尤其是在 REPEATABLE READ
(可重復讀)隔離級別下。以下從原理、場景和示例詳細解釋:
1. 間隙鎖的作用
間隙鎖(Gap Lock)是 InnoDB 在索引記錄之間的 間隙(Gap) 上施加的鎖,用于阻止其他事務在這些間隙中插入新數據,從而保證事務執行期間查詢結果集的一致性。
示例場景:
假設事務 A 執行 SELECT * FROM users WHERE age = 25 FOR UPDATE
,若 age
是非唯一索引,InnoDB 會鎖定 age = 25
的所有記錄以及相鄰的間隙,阻止其他事務插入 age = 25
的新記錄。
2. 非唯一索引的結構特點
非唯一索引允許存在重復的鍵值,因此索引樹中可能存在多個相同鍵值的記錄。
當通過非唯一索引查詢時,InnoDB 無法僅通過鎖定單一行來避免幻讀,因為其他事務可能在 相同的鍵值范圍內插入新數據。
示例數據:
假設表 users
的 age
是非唯一索引,數據如下:
+----+-----+
| id | age |
+----+-----+
| 1 | 20 |
| 3 | 25 |
| 5 | 25 |
| 7 | 30 |
+----+-----+
索引 age
的結構(按升序排列):
(20) → (25) → (25) → (30)
3. 非唯一索引觸發間隙鎖的場景
場景 1:等值查詢(age = 25
)
-- 事務 A
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;
鎖定范圍:
- 行鎖:鎖定所有
age = 25
的記錄(id=3 和 id=5)。 - 間隙鎖:鎖定
age = 25
的前后間隙:- 左間隙:
(20, 25)
- 右間隙:
(25, 30)
- 左間隙:
- 行鎖:鎖定所有
其他事務插入
age = 25
的新數據會被阻塞:-- 事務 B(阻塞) INSERT INTO users (id, age) VALUES (6, 25);
場景 2:范圍查詢(age > 20
)
-- 事務 A
BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE;
- 鎖定范圍:
- 行鎖:鎖定
age = 25
和age = 30
的記錄。 - 間隙鎖:鎖定所有符合條件的間隙:
(20, 25)
、(25, 25)
、(25, 30)
、(30, +∞)
- 行鎖:鎖定
4. 對比唯一索引的行為
如果是 唯一索引(如主鍵 id
),等值查詢不會觸發間隙鎖:
-- 事務 A
BEGIN;
SELECT * FROM users WHERE id = 3 FOR UPDATE;
- 鎖定范圍:僅鎖定
id = 3
的行,不涉及間隙鎖。 - 其他事務插入
id = 4
不會被阻塞:-- 事務 B(成功) INSERT INTO users (id, age) VALUES (4, 25);
例外:唯一索引的范圍查詢仍會觸發間隙鎖:
-- 事務 A
BEGIN;
SELECT * FROM users WHERE id > 3 FOR UPDATE;
- 鎖定
id > 3
的所有行和間隙(如(3, 5)
、(5, 7)
、(7, +∞)
)。
5. 為什么非唯一索引必須加間隙鎖?
- 防止幻讀:如果僅鎖定現有行,其他事務可以在間隙中插入相同鍵值的新數據,導致事務 A 的后續查詢出現“幻行”。
- 保證可重復讀:事務 A 在多次查詢同一范圍時,結果集必須一致。
6. 如何避免非唯一索引的間隙鎖?
方案 1:改用 READ COMMITTED
隔離級別
在 READ COMMITTED
級別下,InnoDB 不使用間隙鎖(僅行鎖),但可能產生幻讀:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
方案 2:優化查詢條件
盡量使用 唯一索引 或 精確匹配,減少范圍查詢。
方案 3:縮短事務時間
盡快提交事務,減少鎖持有時間。
總結
非唯一索引觸發間隙鎖的本質是 防止在重復鍵值范圍內插入新數據導致幻讀。理解這一機制有助于:
- 合理設計索引(優先使用唯一索引)。
- 在高并發場景下優化鎖競爭(如選擇
READ COMMITTED
)。 - 避免大范圍查詢對性能的影響。