MySQL 行鎖替代方案：利用條件更新單條 SQL 解決並發超賣問題

前言

高並發場景 (如圖書借閱、秒殺) 中，若先 SELECT 再 UPDATE，中間間隙會導致髒讀與超賣。常見解法是 SELECT ... FOR UPDATE 加排他鎖，但本文提出更簡潔、效能更佳的單條 SQL 方案：在 UPDATE 直接加入庫存條件，利用資料庫原子性操作完全避免鎖競爭。

問題重現：借書系統並發缺陷

-- Session 1
SELECT restnum FROM book WHERE id = 1;  -- 讀到 1
UPDATE book SET restnum = restnum - 1 WHERE id = 1;

-- Session 2 (插在中間)
SELECT restnum FROM book WHERE id = 1;  -- 仍讀到 1 (髒讀)
UPDATE book SET restnum = restnum - 1 WHERE id = 1;
-- 結果 restnum = -1，邏輯錯誤

傳統行鎖方案

BEGIN;
SELECT restnum FROM book WHERE id = 1 FOR UPDATE;  -- 排他鎖
UPDATE book SET restnum = restnum - 1 WHERE id = 1;
COMMIT;

缺點：鎖持有時間 = 整個交易持續時間，並發吞吐受限；需 InnoDB 且 id 有索引。

優雅替代方案：條件更新單條 SQL

UPDATE book 
SET restnum = restnum - 1 
WHERE id = 1 AND restnum > 0;

原理：UPDATE 為原子操作，資料庫保證「讀取-判斷-寫入」不可分割。若 restnum > 0 不成立，該行不更新，影響列數為 0，應用層據此判斷失敗。

優勢對比

應用層處理邏輯

int affected = cmd.ExecuteNonQuery();
if (affected == 0) {
    // 庫存不足或並發搶失敗
    return "庫存不足";
}
return "借閱成功";

總結

「條件更新」模式 (UPDATE ... WHERE id = ? AND quantity > 0) 是解決並發扣減庫存、防超賣的最佳實踐：零鎖競爭、零死鎖、程式碼最簡、效能最強。凡涉及「檢查後更新」的業務，優先考慮合併為單條帶條件的 UPDATE。