您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 行鎖深度實戰：從基礎 FOR UPDATE 到優化器陷阱與無鎖並發替代方案

編輯：MySQL 綜合教學

並發場景下數據一致性是資料庫應用核心挑戰。本文以圖書借閱系統為例，層層遞進剖析 MySQL InnoDB 行鎖機制、優化器導致的鎖升級陷阱，並提供無顯式鎖的嚴格模式替代方案。

經典並發問題：讀取-修改非原子性

兩用戶同時借閱庫存為 1 的圖書 (id=1)。若先 SELECT restnum 判斷 >0 再 UPDATE restnum-1，中間窗口期第二用戶讀到舊值，導致庫存變 -1。解決之道：事務 + SELECT ... FOR UPDATE 加排他鎖，第二會話阻塞至提交。

陷阱一：優化器放棄索引導致表鎖

實驗構造 num 字段有索引但基數極低 (僅 11/22 兩值，各 5 行)。SELECT ... WHERE num=11 FOR UPDATE 正常行鎖，但後續 UPDATE ... WHERE num=11 卻阻塞——優化器判定全表掃描成本更低，放棄索引升級為表鎖。即使 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 為行鎖。子查詢外層未使用索引，再次觸發鎖升級。

替代方案：嚴格模式 + 無符號欄位實現無鎖並發控制

針對主鍵唯一場景 (如 id)，可避免顯式鎖：

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;

將 restnum 定義為 UNSIGNED。並發極端情況下第二事務 UPDATE 會嘗試寫 -1，觸發嚴格模式溢出錯誤自動回滾，保證數據正確性。此法利用約束檢查替代鎖序列化，適用於高併發低衝突熱點行。