MySQL InnoDB 鎖機制深度解析 (三)：不同隔離級別下的加鎖行為與索引影響

MySQL InnoDB 鎖機制深度解析 (三)：不同隔離級別下的加鎖行為與索引影響

核心概念：聚簇索引

• 每張 InnoDB 表有且僅有一個聚簇索引，資料直接儲存在其葉節點。
• 通常為主鍵；無主鍵則用第一個非空唯一索引；否則隱式建立 6 字節 row_id。
• 二級索引葉節點僅儲存索引列 + 聚簇索引鍵值。

關鍵原則：鎖定的是「掃描到的索引記錄」，而非最終返回行

執行鎖讀 (LOCK IN SHARE MODE / FOR UPDATE) 時，InnoDB 對查詢過程中掃描的所有索引記錄加鎖。若掃描的是二級索引，該索引記錄指向的聚簇索引記錄、以及其他指向同一聚簇記錄的二級索引記錄全都會被加鎖。無索引可用時會掃描整個聚簇索引 → 鎖全表。

案例 1：無索引欄位查詢導致全表鎖

CREATE TABLE tb (id1 INT PRIMARY KEY, id2 INT UNIQUE, id3 INT, id4 INT, KEY idx(id3)) ENGINE=InnoDB;
INSERT INTO tb VALUES (1,1,1,1), (5,5,5,5), (9,9,9,9);

1. S1：SELECT * FROM tb WHERE id4=1 LOCK IN SHARE MODE (id4 無索引) → 掃描全表聚簇索引 (id1) 並加 S 鎖，所有二級索引 (id2, id3) 也被加鎖。
2. S2：SELECT * FROM tb WHERE id2=5 FOR UPDATE → 等待鎖超時。

教訓：正確設計索引不僅關乎效能，更決定併發度。

案例 2：二級索引 + REPEATABLE READ (RR) → Next-Key Lock

1. S1：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT * FROM tb WHERE id3=5 FOR UPDATE; (id3 為普通索引)
2. S2 任意操作涉及 id3=5 附近間隙、或 id1=5、id2=5 的記錄 → 全部阻塞。

原因：RR 級別下，InnoDB 對二級索引採用 Next-Key Lock (Gap Lock + Record Lock)，鎖定索引區間防止幻讀；同時向聚簇索引與同鍵值的其他二級索引傳播鎖。

總結規則

• RR 級別：採用 Next-Key Lock；RC 級別：採用 Record Lock (僅鎖定確切存在的記錄)。
• 被掃描索引非聚簇 → 聚簇索引 + 同鍵值二級索引同步加鎖。
• LOCK IN SHARE MODE 加 S 鎖；FOR UPDATE 加 X 鎖。
• 普通 SELECT (非 Serializable) 為非阻塞快照讀，不加鎖。
• UPDATE/DELETE ... WHERE 加 X 鎖；INSERT 加 Record Lock。

掌握此機制，才能寫出高併發、低衝突的 SQL。