MySQL 並發更新會員到期時間：三種方案深度對比與最佳實踐

業務場景

會員表 vip_member（InnoDB），字段：uid、start_at、end_at、active_status、updated_at。

續費規則：

• end_at < NOW()（已過期）→ start_at = NOW()，end_at = NOW() + 月數
• end_at >= NOW()（未過期）→ end_at = end_at + 月數
• 統一設置 active_status = 1

問題：SELECT + UPDATE 非原子導致「更新丟失」

-- 線程 A、B 同時讀取 end_at = '2024-01-01'
-- A 續 1 個月，B 續 2 個月
-- 可能最終 end_at 只增加 2 個月，而非 3 個月

方案對比

方案 A：單條 SQL + CASE 表達式（推薦：原子、無鎖、性能最優）

UPDATE vip_member
SET 
  start_at = CASE 
    WHEN end_at < NOW() THEN NOW() 
    ELSE start_at 
  END,
  end_at = CASE 
    WHEN end_at < NOW() THEN DATE_ADD(NOW(), INTERVAL ? MONTH)
    ELSE DATE_ADD(end_at, INTERVAL ? MONTH)
  END,
  active_status = 1,
  updated_at = NOW()
WHERE uid = ?
LIMIT 1;

優點： 單語句原子執行，無並發衝突，無額外鎖開銷，SQL 注入安全（參數化）。
缺點： 業務邏輯耦合在 SQL，複雜場景維護困難。

方案 B：悲觀鎖（SELECT ... FOR UPDATE）

START TRANSACTION;
SELECT * FROM vip_member WHERE uid = ? FOR UPDATE;  -- 加排他鎖
-- 應用層判斷邏輯
UPDATE vip_member SET ... WHERE uid = ?;
COMMIT;

關鍵點：

• FOR UPDATE：排他鎖，阻塞其他事務的 SELECT ... FOR UPDATE、UPDATE、DELETE。
• LOCK IN SHARE MODE：共享鎖，允許並發讀，阻塞寫 —— 不適用於此場景（仍會丟失更新）。
• 必須在事務中使用，否則鎖立即釋放。

優點： 邏輯清晰，應用層可控。
缺點： 鎖持有時間 = 事務持續時間，高並發下吞吐率下降，易死鎖。

方案 C：樂觀鎖（CAS 機制，推薦：高並發、低衝突場景最優）

-- 1. 讀取當前版本/關鍵字段
SELECT end_at, version FROM vip_member WHERE uid = ?;

-- 2. 業務計算新 end_at
-- 3. 帶版本條件更新
UPDATE vip_member 
SET end_at = ?, active_status = 1, updated_at = NOW(), version = version + 1
WHERE uid = ? AND end_at = ? AND version = ?;  -- 關鍵：雙重檢查

-- 4. 判斷 affected_rows
--   = 1 成功
--   = 0 衝突 → 重試（通常 1-2 次即成功）

實現變體： 無 version 列時，直接用業務字段 end_at 作為檢查條件（如上例）。

優點： 無鎖、無阻塞、吞吐極高、可選無事務（單語句原子）。
缺點： 衝突率高時重試開銷大，需應用層實現重試邏輯。

方案選型決策樹

最佳實踐清單

1. 優先方案 A：能用單 SQL 解決的業務，堅決不用多語句。
2. 樂觀鎖加版本列：version INT DEFAULT 0，所有更新 version = version + 1，防止 ABA 問題。
3. 重試策略：指數退避 + 最大 3 次，記錄衝突日誌便於監控。
4. 索引覆蓋：WHERE uid = ? 必須走主鍵/唯一索引，避免鎖表/全表掃描。
5. 監控指標：Com_update、Innodb_row_lock_waits、重試次數分佈。