您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL InnoDB 行鎖深度實測：索引失效導致表鎖、子查詢陷阱與嚴格模式替代方案

編輯：MySQL 資料庫相關知識

InnoDB 行鎖依賴索引。實測發現：索引重複值過高、子查詢、優化器判斷全表掃描更划算時，MySQL 會忽略索引升級為表鎖，導致併發衝突。

經典借書場景與行鎖寫法

BEGIN;
SELECT restnum FROM book WHERE id = 1 FOR UPDATE;  -- 加排他鎖
UPDATE book SET restnum = restnum - 1 WHERE id = 1;
COMMIT;

id 為主鍵，正常行鎖無問題。

實測發現：索引重複值高 → 優化器放棄索引 → 表鎖

測試表：num 欄位有索引，僅 11/22 兩值，各 5 筆（重複率極高）
Session 1：SELECT ... WHERE num=11 FOR UPDATE → 鎖 5 筆
Session 2：SELECT ... WHERE num=22 FOR UPDATE → 鎖另外 5 筆
Session 1 執行 UPDATE ... WHERE num=11 → 等待！ 被 Session 2 阻擋
原因：優化器認為 5/10 筆需掃描，不如全表掃描，放棄索引改用表鎖
即使 FORCE INDEX 也無效，仍為表鎖

子查詢導致表鎖陷阱

-- 表鎖
SELECT id FROM items WHERE id IN (SELECT id FROM items WHERE id < 6) FOR UPDATE;

-- 行鎖
SELECT id FROM items WHERE id IN (1,2,3,4,5) FOR UPDATE;

子查詢外層未用索引 → 表鎖。避免在 FOR UPDATE 使用子查詢。

替代方案：嚴格模式 + UNSIGNED 防負值

SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
BEGIN;
SELECT restnum FROM book WHERE id = 1;  -- 不加 FOR UPDATE
UPDATE book SET restnum = restnum - 1 WHERE id = 1;
IF (update 成功) COMMIT; ELSE ROLLBACK;