Concurrency Control Patterns

應用層處理資料庫併發衝突的三種主要 pattern。

各主流 RDB 預設 Isolation Level

MySQL 是少數預設 Repeatable Read 的 RDB；多數 RDB 預設 Read Committed，允許更高並發但需應用層自行處理競爭。

Pattern 比較

Pattern 1 — Pessimistic Lock（悲觀鎖）

假設衝突一定發生，先取得 Row Lock 再操作。

BEGIN;

-- 取得 Row Lock，其他交易無法讀取或修改此行直到 COMMIT
SELECT stock FROM product WHERE id = 1 FOR UPDATE;

-- 確認庫存後扣減
UPDATE product SET stock = stock - 1 WHERE id = 1;

COMMIT;

• 使用 SELECT ... FOR UPDATE 取得排他鎖（Exclusive Lock）
• 其他交易在 COMMIT 前無法取得同一行的鎖，會等待或超時
• 適合：寫衝突頻繁、交易時間短、不希望有 retry 邏輯

Pattern 2 — Optimistic Lock（樂觀鎖）

假設衝突少，更新時才檢查是否有人搶先。

Table 需有 version column：

ALTER TABLE product ADD COLUMN version INT NOT NULL DEFAULT 0;

操作流程：

-- 1. 讀取資料（含 version）
SELECT stock, version FROM product WHERE id = 1;
-- 假設回傳 stock=10, version=3

-- 2. 更新時帶入舊 version
UPDATE product
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 3;

-- 3. 應用層判斷 affected_rows
-- affected_rows = 1 → 成功
-- affected_rows = 0 → 衝突，retry

  sequenceDiagram
    participant A as Client A
    participant B as Client B
    participant DB as Database

    A ->> DB: SELECT stock=10, version=3
    B ->> DB: SELECT stock=10, version=3
    B ->> DB: UPDATE ... WHERE version=3 → success (version=4)
    A ->> DB: UPDATE ... WHERE version=3 → affected_rows=0
    note over A,DB: conflict! A needs to retry
    A ->> DB: SELECT stock=9, version=4
    A ->> DB: UPDATE ... WHERE version=4 → success (version=5)

• 適合：讀多寫少、衝突概率低
• 衝突頻繁時 retry 次數多，反而影響效能

Pattern 3 — Atomic SQL WHERE（原子 SQL）

不加鎖，利用 DB 原子性 + WHERE 條件做隱式檢查。

-- 扣庫存：WHERE stock > 0 確保不會扣成負數
UPDATE product
SET stock = stock - 1
WHERE id = 1 AND stock > 0;

-- affected_rows = 1 → 成功
-- affected_rows = 0 → 庫存不足或被搶先

帶 version 的變體（不用 SELECT FOR UPDATE，也不需 BEGIN/COMMIT）：

UPDATE product
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND stock > 0 AND version = 3;

• 無需 BEGIN/COMMIT，單條 SQL 即保證原子性
• 無 Row Lock，高並發下效能最好
• 適合：簡單數值扣減（庫存、點數、餘額），不需要複雜 retry 邏輯
• 不適合：更新前需要複雜業務邏輯判斷的場景

相關主題

• ACID
• Transaction Issues
• Database Locks
• CAP Theorem