Oracle 存儲過程實戰：正確使用行鎖解決併發入庫重複問題

業務場景與併發隱患

入庫單表含 status 欄位記錄入庫狀態。業務邏輯：入庫前先查狀態，若為「未入庫」則執行入庫並更新狀態。併發風險：兩用戶同時查詢均獲「未入庫」，導致重複入庫，產生髒資料。

核心解決方案：SELECT ... FOR UPDATE 行鎖

在存儲過程開啟事務時，以 FOR UPDATE 子句鎖定目標行，阻塞其他事務對同一行的修改操作，保證「查-改」原子性。

標準語法

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

• OF column_list：僅鎖定指定欄位所在行，減少鎖衝突（多表連接時尤為重要）。
• WAIT n：等待 n 秒獲取鎖，超時拋出 ORA-30006，避免無限期阻塞。
• NOWAIT：立即返回資源忙異常，適合互動式應用。
• SKIP LOCKED（11g+）：跳過已鎖定行，直接處理可用行，適用於批次處理佇列場景。

實戰存儲過程範例

CREATE OR REPLACE PROCEDURE p_instock (p_dm IN VARCHAR2) IS
  v_flag CHAR(1);
BEGIN
  -- 1. 查詢並加行鎖，最多等待 5 秒
  SELECT status INTO v_flag
  FROM   stock_table
  WHERE  dm = p_dm
  FOR UPDATE WAIT 5;

  -- 2. 業務判斷
  IF v_flag = 'N' THEN
    -- 執行入庫邏輯
    UPDATE stock_table SET status = 'Y', instock_time = SYSDATE WHERE dm = p_dm;
    COMMIT;
  ELSE
    -- 已入庫，拋出自訂異常或記錄日誌
    RAISE_APPLICATION_ERROR(-20001, '重複入庫：單據 ' || p_dm || ' 已處理');
    ROLLBACK;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

行為驗證實驗（PL/SQL Developer 雙窗口）

進階應用：多表連接時的 OF 子句

SELECT o.order_id, o.amount
FROM   orders o, order_items i
WHERE  o.order_id = i.order_id
AND    o.status = 'NEW'
FOR UPDATE OF o.status;  -- 僅鎖定 orders 表的狀態欄，order_items 不加鎖

若省略 OF，兩表符合條件的所有行均會被鎖定，降低併發吞吐。

注意事項與最佳實踐

1. 鎖定行期間不可對該行執行 DML，否則自死鎖。
2. 事務務必儘短：查詢→業務邏輯→更新→提交/回滾，避免長事務擴大鎖持有時間。
3. 搭配索引：WHERE 條件欄位建立索引，縮小鎖定範圍（行級鎖依賴索引定位）。
4. 11g+ 推薦使用 SKIP LOCKED 實現高併發任務佇列消費模型。