兩種鎖定讀模式
| 語句 | 鎖類型 | 兼容性 | 典型場景 |
SELECT ... LOCK IN SHARE MODE | 共享鎖 (S) | 兼容 S,阻塞 X | 讀取後需確認無並發修改 |
SELECT ... FOR UPDATE | 排他鎖 (X) | 阻塞 S/X | 讀取後即將 UPDATE/DELETE 同一行 |
InnoDB 預設 REPEATABLE READ 隔離級別,兩者在事務中均會等待其他事務提交才執行。FOR UPDATE 避免 SHARE MODE 下雙方升級 X 鎖導致的死鎖。
經典庫存扣減場景
❌ 不安全寫法(競態條件)
SELECT quantity FROM products WHERE id=3; -- 讀到 2
-- 此處可能已被其他事務扣為 0
UPDATE products SET quantity = 1 WHERE id=3; -- 錯誤覆蓋
✅ 安全寫法(FOR UPDATE + 事務)
SET AUTOCOMMIT=0;
BEGIN;
SELECT quantity FROM products WHERE id=3 FOR UPDATE; -- 鎖定該行
-- 業務邏輯判斷 quantity > 0
UPDATE products SET quantity = quantity - 1 WHERE id=3;
COMMIT;
行鎖 vs 表鎖:關鍵在於「明確主鍵」
| SQL | 鎖定範圍 | 原因 |
SELECT * FROM t WHERE id=3 FOR UPDATE | Row Lock (id=3) | 主鍵精確匹配,命中唯一索引 |
SELECT * FROM t WHERE id=-1 FOR UPDATE | 無鎖 | 無匹配行,InnoDB 不加鎖 |
SELECT * FROM t WHERE name='Mouse' FOR UPDATE | Table Lock | 非索引列/非唯一索引 → 全表掃描 → 間隙鎖/表鎖 |
SELECT * FROM t WHERE id<>3 FOR UPDATE | Table Lock | 範圍條件 → 間隙鎖擴大至全表 |
SELECT * FROM t WHERE id LIKE '3' FOR UPDATE | Table Lock | 隱式轉換導致索引失效 |
實戰測試模板
CREATE TABLE testupdate (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
val BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO testupdate VALUES (1,0);
-- 終端1
BEGIN;
SELECT val FROM testupdate WHERE id=1 FOR UPDATE;
-- 終端2 嘗試同語句 → 阻塞
UPDATE testupdate SET val=val+1 WHERE id=1;
COMMIT;
-- 終端2 獲得鎖繼續
避坑指南
- 僅 InnoDB 支援,MyISAM 只支援表鎖
- 必須在事務中(BEGIN/COMMIT),自動提交模式下鎖即釋放
- 索引設計:WHERE 條件務必命中唯一/主鍵索引,避免升級表鎖
- 禁用 LOCK TABLES:InnoDB 下易死鎖,若必須用請閱讀官方文檔限制條件
- 死鎖處理:捕獲
ERROR 1213 (HY000),應用層隨機延遲重試