MySQL SELECT FOR UPDATE 行鎖與表鎖深度解析

InnoDB SELECT ... FOR UPDATE 鎖機制詳解

在 REPEATABLE READ 隔離級下，SELECT ... FOR UPDATE 與 LOCK IN SHARE MODE 皆會等待其他事務提交。關鍵差異：LOCK IN SHARE MODE 易於 UPDATE 時產生死鎖，若後續需更新同表，**務必使用 FOR UPDATE**。

經典錯誤場景：先查後更新的競態條件

-- 不安全：兩條語句間有間隙
SELECT quantity FROM products WHERE id=3;
UPDATE products SET quantity = 1 WHERE id=3;

間隙期若被其他事務扣減庫存至 0，本事務仍會錯誤執行 UPDATE。

正確做法：事務 + FOR UPDATE

SET AUTOCOMMIT=0;
BEGIN;
SELECT quantity FROM products WHERE id=3 FOR UPDATE;  -- 鎖定該行
-- 業務邏輯判斷 quantity > 0
UPDATE products SET quantity = 1 WHERE id=3;
COMMIT;

• 鎖定粒度：InnoDB 預設行級鎖
• 其他事務嘗試 SELECT ... FOR UPDATE 同一行會阻塞，直到 COMMIT
• 普通 SELECT 不受影響（MVCC 快照讀）

行鎖 vs 表鎖：關鍵在於 WHERE 條件

重要限制

• 僅適用 InnoDB
• 必須在事務區塊（BEGIN/COMMIT）中生效
• 避免在 InnoDB 上使用 LOCK TABLES，易死鎖

測試建議

開兩個 MySQL Command 視窗交互觀察鎖等待狀態。