Oracle SELECT FOR UPDATE 進階用法：WAIT、NOWAIT、SKIP LOCKED 實戰指南

行級鎖回顧

Oracle 行級鎖（TX 鎖）特性：

• **排他鎖**：防止其他事務修改同一行，**不阻塞讀取**（SELECT 不加 FOR UPDATE）。
• 自動加鎖：INSERT/UPDATE/DELETE、SELECT ... FOR UPDATE。
• 鎖釋放：只在 **COMMIT/ROLLBACK** 時。

SELECT FOR UPDATE 完整語法

SELECT ...
FROM ...
WHERE ...
FOR UPDATE [OF column_list] [WAIT n | NOWAIT | SKIP LOCKED];

四大子句行為對比實測

環境：表 T(A VARCHAR2(20), B VARCHAR2(20))，4 行資料。
Session 1：SELECT * FROM T WHERE A='1' FOR UPDATE;（持有鎖不提交）

SKIP LOCKED 進階模式：佇列消費者範本

場景：多消費者並行處理 TASK_QUEUE 表，每次領取 10 筆待處理任務。

CURSOR c_tasks IS
  SELECT task_id, payload
  FROM task_queue
  WHERE status = 'PENDING'
    AND ROWNUM <= 10
  FOR UPDATE SKIP LOCKED;  -- 關鍵：跳過同伴已鎖定的行

BEGIN
  FOR r IN c_tasks LOOP
    UPDATE task_queue SET status = 'PROCESSING', worker_id = :my_id
    WHERE task_id = r.task_id;
    -- 處理業務邏輯
    UPDATE task_queue SET status = 'DONE', result = :result
    WHERE task_id = r.task_id;
    COMMIT;
  END LOOP;
END;
/

優勢：無需應用層協調、無死結、無飢餓、吞吐隨消費者數線性擴展。

OF column_list 子句：精準縮小鎖範圍

多表 Join 時，預設鎖定**所有涉及表的符合行**。

-- 僅鎖定 ORDERS 表的行，不鎖定 CUSTOMERS 表
SELECT o.order_id, c.cust_name
FROM orders o, customers c
WHERE o.cust_id = c.cust_id
  AND o.status = 'NEW'
FOR UPDATE OF o.status;  -- 僅鎖定 ORDERS.STATUS 欄位所在行

若省略 OF，兩表相關行全鎖，嚴重降低併發度。

死結預防：固定存取順序 + NOWAIT/SKIP LOCKED

1. **所有事務遵循相同表順序**鎖定（如先父表後子表）。
2. 關鍵區段使用 FOR UPDATE NOWAIT 或 WAIT 3，捕獲異常立即重試或回滾。
3. 長事務拆分：查詢階段不加鎖，只在最終更新前 SELECT ... FOR UPDATE NOWAIT 確認版本再更新（樂觀鎖 + 悲觀鎖混合）。

監控與告警

-- 實時鎖等待
SELECT s.SID, s.USERNAME, s.SQL_ID, l.TYPE, l.LMODE, l.REQUEST, l.BLOCK
FROM V$LOCK l
JOIN V$SESSION s ON s.SID = l.SID
WHERE l.REQUEST > 0 OR l.BLOCK = 1;

-- 歷史等待統計
SELECT event, total_waits, time_waited_micro/1e6 total_sec
FROM V$SYSTEM_EVENT
WHERE event LIKE 'enq: TX%'
ORDER BY time_waited_micro DESC;

版本演進補充

• Oracle 8i：引入 NOWAIT。
• Oracle 9i Release 2：引入 WAIT n。
• Oracle 11g Release 1：引入 SKIP LOCKED（佇列處理神器）。

結語

SELECT FOR UPDATE 不是「加鎖」，而是**併發控制的精密儀器**。
- NOWAIT：寧失不等，適合高併發爭搶。
- WAIT n：給一點耐心，適合極短關鍵區。
- SKIP LOCKED：錯峰處理，佇列/批次神器。
- OF column_list：精準施策，不波及無關表。
掌握這四把刀，配合固定存取順序、樂觀鎖版本號，**死結與飢餓將成為歷史**。