MySQL InnoDB 鎖機制實驗：主鍵、索引如何決定行鎖與表鎖

MySQL InnoDB 鎖機制實驗：主鍵、索引如何決定行鎖與表鎖

在 MySQL 5.5.20 上通過雙 Session 並發實驗，驗證 InnoDB 在不同表結構（無主鍵、有主鍵、無索引、有索引）下的鎖行為，結論：命中索引（含主鍵）才是行鎖，否則退化為表鎖。

實驗環境

CREATE TABLE test (a INT(5), b VARCHAR(10), c VARCHAR(10));
INSERT INTO test VALUES (1,'111','111'),(2,'222','222'),(3,'333','333'),
                        (4,'444','444'),(5,'555','555'),(6,'666','666');
SET AUTOCOMMIT=0;

場景與結果總表

核心結論

1. 主鍵 = 聚簇索引，Where 條件命中主鍵 → 行鎖。
2. 二級索引命中 → 行鎖（先鎖二級索引記錄，再鎖聚簇索引記錄）。
3. 全表掃描（無索引可用） → 表鎖（實為鎖所有記錄 + 間隙鎖），併發度極低。
4. 與 Oracle「預設行鎖、無索引也行鎖」不同，InnoDB 強依賴索引 實現行級鎖。

實戰建議

• 所有表必須有主鍵（建議自增 BIGINT）。
• WHERE/JOIN/ORDER BY 涉及的欄位建立適當索引。
• 避免大範圍更新/刪除未命中索引的 SQL，必要時強制索引或重寫。
• 使用 EXPLAIN 確認 type=ref/eq_ref 而非 ALL。