MySQL 實現高併發隊列：從簡單 SELECT/UPDATE 到原子化 LAST_INSERT_ID 方案

問題背景

將資料表視為隊列：每次取出符合條件的第一筆，標記為已處理。單執行緒下 SELECT ... LIMIT 1 + UPDATE 即可；併發下會出現同一行被多次取出 (Race Condition)。

演進歷程

階段 1：表鎖 (效能差)

LOCK TABLES targets WRITE;
SELECT * FROM targets WHERE status='C' LIMIT 1;
UPDATE targets SET status='D' WHERE id=...;
UNLOCK TABLES;

序列化所有併發，吞吐量極低。

階段 2：存儲過程 + 行鎖 (複雜)

MySQL 5.0 支援 Stored Procedure，可封裝邏輯，但調試困難、版本遷移麻煩，不推薦。

階段 3：單條 UPDATE 原子性 + LAST_INSERT_ID 技巧 (推薦)

-- 步驟 1：原子更新並寫入 LAST_INSERT_ID
UPDATE targets 
SET status='D', id=LAST_INSERT_ID(id) 
WHERE status='C' 
LIMIT 1;

-- 步驟 2：取回剛更新的行
SELECT * FROM targets 
WHERE ROW_COUNT() > 0 AND id = LAST_INSERT_ID();

• UPDATE ... LIMIT 1 保證只鎖定/更新一行，原子性由 InnoDB 行鎖保障。
• id=LAST_INSERT_ID(id) 將該行 ID 寫入連線級變數 LAST_INSERT_ID()。
• ROW_COUNT() 判斷是否真有行被更新 (隊列為空時為 0)。
• 整個過程無顯式鎖、無事務開銷，極輕量。

階段 4：優先級隊列的 ORDER BY 陷阱

-- 慢：UPDATE ... ORDER BY schedule ASC LIMIT 1  (Bug #12915，不走索引)
-- 快：分離查詢與更新
SELECT id FROM targets WHERE status='C' ORDER BY schedule ASC LIMIT 1;
UPDATE targets SET status='D' WHERE id=...;

但分離又回到 Race Condition。折衷方案：子查詢 + 連接更新

UPDATE targets t
JOIN (
  SELECT id FROM targets 
  WHERE status='C' AND schedule < CURRENT_TIMESTAMP 
  ORDER BY schedule ASC LIMIT 1
) tmp ON t.id = tmp.id
SET t.status='D', t.id=LAST_INSERT_ID(t.id);

SELECT * FROM targets WHERE ROW_COUNT()>0 AND id=LAST_INSERT_ID();

• 子查詢走索引快速定位目標 ID。
• 外層 UPDATE ... JOIN 單條語句原子更新並寫入 LAST_INSERT_ID。
• 適用於「帶優先級/排程時間」的併發隊列。

效能對比 (百萬級表)

結語

MySQL 併發隊列首選 單條 UPDATE + LAST_INSERT_ID()，零鎖開銷、原子性強。需優先級時，採用 子查詢定位 + JOIN 更新 折衷方案，兼顧索引效率與原子語義。避免存儲過程、表鎖、分離式 SELECT/UPDATE。