萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SELECT ... FOR UPDATE 深度解析:行鎖/表鎖判定、死鎖避坑與實戰範例

兩種鎖定讀模式

語句鎖類型兼容性典型場景
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 UPDATERow Lock (id=3)主鍵精確匹配,命中唯一索引
SELECT * FROM t WHERE id=-1 FOR UPDATE無鎖無匹配行,InnoDB 不加鎖
SELECT * FROM t WHERE name='Mouse' FOR UPDATETable Lock非索引列/非唯一索引 → 全表掃描 → 間隙鎖/表鎖
SELECT * FROM t WHERE id<>3 FOR UPDATETable Lock範圍條件 → 間隙鎖擴大至全表
SELECT * FROM t WHERE id LIKE '3' FOR UPDATETable 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 獲得鎖繼續

避坑指南

  1. 僅 InnoDB 支援,MyISAM 只支援表鎖
  2. 必須在事務中(BEGIN/COMMIT),自動提交模式下鎖即釋放
  3. 索引設計:WHERE 條件務必命中唯一/主鍵索引,避免升級表鎖
  4. 禁用 LOCK TABLES:InnoDB 下易死鎖,若必須用請閱讀官方文檔限制條件
  5. 死鎖處理:捕獲 ERROR 1213 (HY000),應用層隨機延遲重試
copyright © 萬盛學電腦網 all rights reserved