狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

在 MySQL 的 InnoDB 存儲引擎中，非唯一索引觸發(fā)間隙鎖的核心原因是為了 防止幻讀（Phantom Read），尤其是在 REPEATABLE READ（可重復(fù)讀）隔離級別下。以下從原理、場景和示例詳細(xì)解釋：

---

1. 間隙鎖的作用

間隙鎖（Gap Lock）是 InnoDB 在索引記錄之間的 間隙（Gap） 上施加的鎖，用于阻止其他事務(wù)在這些間隙中插入新數(shù)據(jù)，從而保證事務(wù)執(zhí)行期間查詢結(jié)果集的一致性。

示例場景：
假設(shè)事務(wù) A 執(zhí)行 SELECT * FROM users WHERE age = 25 FOR UPDATE，若 age 是非唯一索引，InnoDB 會(huì)鎖定 age = 25 的所有記錄以及相鄰的間隙，阻止其他事務(wù)插入 age = 25 的新記錄。

---

2. 非唯一索引的結(jié)構(gòu)特點(diǎn)

非唯一索引允許存在重復(fù)的鍵值，因此索引樹中可能存在多個(gè)相同鍵值的記錄。
當(dāng)通過非唯一索引查詢時(shí)，InnoDB 無法僅通過鎖定單一行來避免幻讀，因?yàn)槠渌聞?wù)可能在 相同的鍵值范圍內(nèi)插入新數(shù)據(jù)。

示例數(shù)據(jù)：

假設(shè)表 users 的 age 是非唯一索引，數(shù)據(jù)如下：

+----+-----+
| id | age |
+----+-----+
| 1  | 20  |
| 3  | 25  |
| 5  | 25  | 
| 7  | 30  |
+----+-----+

索引 age 的結(jié)構(gòu)（按升序排列）：

(20) → (25) → (25) → (30)

---

3. 非唯一索引觸發(fā)間隙鎖的場景

場景 1：等值查詢（age = 25）

-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;

• 鎖定范圍：

  1. 行鎖：鎖定所有 age = 25 的記錄（id=3 和 id=5）。
  2. 間隙鎖：鎖定 age = 25 的前后間隙：
     • 左間隙：(20, 25)
     • 右間隙：(25, 30)

• 其他事務(wù)插入 age = 25 的新數(shù)據(jù)會(huì)被阻塞：

  -- 事務(wù) B（阻塞）
  INSERT INTO users (id, age) VALUES (6, 25);
  

場景 2：范圍查詢（age > 20）

-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE;

• 鎖定范圍：
  1. 行鎖：鎖定 age = 25 和 age = 30 的記錄。
  2. 間隙鎖：鎖定所有符合條件的間隙：
     • (20, 25)、(25, 25)、(25, 30)、(30, +∞)

---

4. 對比唯一索引的行為

如果是 唯一索引（如主鍵 id），等值查詢不會(huì)觸發(fā)間隙鎖：

-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE id = 3 FOR UPDATE;

• 鎖定范圍：僅鎖定 id = 3 的行，不涉及間隙鎖。
• 其他事務(wù)插入 id = 4 不會(huì)被阻塞：

  -- 事務(wù) B（成功）
  INSERT INTO users (id, age) VALUES (4, 25);
  

例外：唯一索引的范圍查詢?nèi)詴?huì)觸發(fā)間隙鎖：

-- 事務(wù) A
BEGIN;
SELECT * FROM users WHERE id > 3 FOR UPDATE;

• 鎖定 id > 3 的所有行和間隙（如 (3, 5)、(5, 7)、(7, +∞)）。

---

5. 為什么非唯一索引必須加間隙鎖？

• 防止幻讀：如果僅鎖定現(xiàn)有行，其他事務(wù)可以在間隙中插入相同鍵值的新數(shù)據(jù)，導(dǎo)致事務(wù) A 的后續(xù)查詢出現(xiàn)“幻行”。
• 保證可重復(fù)讀：事務(wù) A 在多次查詢同一范圍時(shí)，結(jié)果集必須一致。

---

6. 如何避免非唯一索引的間隙鎖？

方案 1：改用 READ COMMITTED 隔離級別

在 READ COMMITTED 級別下，InnoDB 不使用間隙鎖（僅行鎖），但可能產(chǎn)生幻讀：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

方案 2：優(yōu)化查詢條件

盡量使用 唯一索引 或 精確匹配，減少范圍查詢。

方案 3：縮短事務(wù)時(shí)間

盡快提交事務(wù)，減少鎖持有時(shí)間。

---

總結(jié)

非唯一索引觸發(fā)間隙鎖的本質(zhì)是 防止在重復(fù)鍵值范圍內(nèi)插入新數(shù)據(jù)導(dǎo)致幻讀。理解這一機(jī)制有助于：

1. 合理設(shè)計(jì)索引（優(yōu)先使用唯一索引）。
2. 在高并發(fā)場景下優(yōu)化鎖競爭（如選擇 READ COMMITTED）。
3. 避免大范圍查詢對性能的影響。