MySQL 行鎖深度解析：索引失效導致表鎖、子查詢陷阱與嚴格模式巧解併發

InnoDB 行鎖基於索引實現，但索引失效會導致鎖升級為表鎖，嚴重影響併發性能。本文通過實驗複現索引失效鎖升級、子查詢鎖升級現象，並給出嚴格模式巧解併發競爭方案。

基礎知識：行鎖加持條件

• 存儲引擎：InnoDB
• 隔離級別：READ COMMITTED / REPEATABLE READ
• SQL 條件：WHERE 子句使用索引列，且優化器選擇索引掃描（非全表掃描）。
• 鎖類型：SELECT ... FOR UPDATE（排他鎖 X）、SELECT ... LOCK IN SHARE MODE（共享鎖 S）、UPDATE/DELETE 隱式加 X 鎖。

實驗一：索引列重復率高導致優化器放棄索引 → 表鎖

表結構

CREATE TABLE `book` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `num` INT, KEY `idx_num` (`num`)
) ENGINE=InnoDB;

數據分布

10 行數據，num 只有 11 和 22 兩值，各 5 行（重復率 50%）。

現象復現

1. Session 1: BEGIN; SELECT * FROM book WHERE num=11 FOR UPDATE; → 鎖定 5 行。
2. Session 2: BEGIN; SELECT * FROM book WHERE num=22 FOR UPDATE; → 正常獲取另 5 行鎖（無衝突）。
3. Session 1: UPDATE book SET name='abc' WHERE num=11; → 阻塞等待！

根因

優化器判斷 num=11 需掃描 50% 行，成本高於全表掃描，放棄索引 → 全表掃描 → 表鎖。Session 1 持有表鎖，自身 UPDATE 也需表鎖，因鎖不兼容自我阻塞。

驗證

刪除部分數據使 num=11 僅 2 行（選擇性 20%），優化器重新選擇索引 → 行鎖 → UPDATE 秒執行。

實驗二：子查詢導致外層無法使用索引 → 表鎖

-- 表鎖
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;

子查詢導致外層優化器無法確定值列表，退化為全表掃描 + 表鎖。

併發競爭巧解：嚴格模式 + 無符號數防超賣

場景

圖書借閱：庫存 restnum UNSIGNED，併發借閱導致超賣（-1）。

方案

SET SESSION 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 (ROW_COUNT() = 1) THEN
    COMMIT;
ELSE
    ROLLBACK;  -- restnum 為 0 時 UPDATE 因 UNSIGNED 溢出失敗，ROW_COUNT=0
END IF;

原理

• STRICT_TRANS_TABLES：嚴格模式下，UNSIGNED 列計算結果超出範圍（如 0-1）直接報錯，語句失敗。
• 無需 FOR UPDATE，避免索引失效風險；依賴數據類型約束 + 事務原子性保證正確性。
• 極低概率併發窗口（SELECT 到 UPDATE 間隙）由數據庫約束兜底。

最佳實踐總結

1. 索引設計：高選擇性列建索引；避免低選擇性列（性別、狀態）單列索引。
2. SQL 寫法：避免子查詢、隱式轉換、函數包裹索引列；用 IN (值列表) 替代 IN (子查詢)。
3. 鎖監控：SHOW ENGINE INNODB STATUS、sys.innodb_lock_waits、Performance Schema 定期巡檢。
4. 業務兜底：關鍵庫存/餘額字段用 UNSIGNED + 嚴格模式，或應用層分佈式鎖（Redis Lua/Redisson）雙重保障。

行鎖不是銀彈，索引有效性 才是併發性能的基石。