MyISAM 僅支援表鎖,但業務需行級並發控制。
UPDATE tbl_name SET row_flag = 1 WHERE id = ID AND row_flag <> 1;
row_flag != 1,影響行數 = 1 → 獲取「鎖」成功row_flag = 1 或不存在,影響行數 = 0 → 獲取失敗-- 業務邏輯
BEGIN;
SELECT * FROM tbl WHERE id = ? FOR UPDATE; -- InnoDB 真行鎖
-- MyISAM 替代方案
UPDATE tbl SET row_flag = 1, ... WHERE id = ? AND row_flag = 0;
IF ROW_COUNT() = 1 THEN
COMMIT; -- 成功
ELSE
ROLLBACK; -- 衝突重試
END IF;