在大批量資料抽取/遷移/ETL 場景中，單執行緒處理往往無法滿足時效要求。Oracle 11g 前 FOR UPDATE SKIP LOCKED 尚未正式支援，且搭配 ORDER BY 存在 Bug。本文介紹一套基於 ANSI 標準 FOR UPDATE NOWAIT、動態 SQL、全域暫存表 與 REF CURSOR 的多線程併發抽取套件 RESB_MT_TABLE_PKG，實現「樂觀鎖定 → 批次鎖定 → 狀態回寫 → 遊標回傳」的完整流程，確保多線程間不重複、不遺漏、高併發。
RESB_MT_TT_ROWIDS：儲存本次批次成功鎖定的 ROWID，ON COMMIT PRESERVE ROWS，供後續 UPDATE/SELECT 使用。RESB_MT_LOCK_ROW：動態 SQL 執行 SELECT 1 FROM table WHERE rowid = :rid FOR UPDATE NOWAIT，成功回傳 1，遇 ORA-00054（資源忙）回傳 0，其他例外拋出。RESB_MT_FIND_ROWS[_VOID]：開啟 REF CURSOR 掃描符合條件的 ROWID（支援動態 WHERE/ORDER BY），逐行嘗試鎖定，成功則寫入暫存表，達到需求筆數 i_rcount 即停止。RESB_MT_UPDATE_COLUMNS：鎖定成功後立即更新狀態欄位（如 status='PROCESSING'），釋放鎖後其他線程可見新狀態，避免重複嘗試。RESB_MT_FIND_ROWS 以 OUT REF CURSOR 回傳已鎖定資料完整欄位；_VOID 版本僅執行更新不回傳。CREATE OR REPLACE PACKAGE BODY RESB_MT_TABLE_PKG AS
-- ============================================================
-- 嘗試以 ROWID 鎖定單列
-- 回傳：1 成功，0 失敗（已被鎖定）
-- ============================================================
FUNCTION RESB_MT_LOCK_ROW (
i_table_source IN VARCHAR2,
i_rid IN ROWID
) RETURN NUMBER IS
o_ret_id NUMBER := 0;
BEGIN
EXECUTE IMMEDIATE
'SELECT 1 FROM ' || i_table_source ||
' WHERE rowid = :x FOR UPDATE NOWAIT'
INTO o_ret_id
USING i_rid;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -54 THEN -- ORA-00054: resource busy
RETURN 0;
ELSE
RAISE;
END IF;
END RESB_MT_LOCK_ROW;
-- ============================================================
-- 更新指定欄位（狀態回寫），針對已鎖定 ROWID 集合
-- ============================================================
PROCEDURE RESB_MT_UPDATE_COLUMNS (
i_table_source IN VARCHAR2,
i_update_expression IN VARCHAR2 -- 例：'status=''PROCESSING'', proc_id=123'
) IS
BEGIN
EXECUTE IMMEDIATE
'UPDATE ' || i_table_source ||
' SET ' || i_update_expression ||
' WHERE rowid IN (SELECT rid FROM RESB_MT_TT_ROWIDS)';
EXCEPTION
WHEN OTHERS THEN RAISE;
END RESB_MT_UPDATE_COLUMNS;
-- ============================================================
-- 尋找並鎖定，僅執行狀態更新，不回傳結果集
-- ============================================================
PROCEDURE RESB_MT_FIND_ROWS_VOID (
i_table_source IN VARCHAR2,
i_search_condition IN VARCHAR2, -- 動態 WHERE，不含 WHERE 關鍵字
i_order_expression IN VARCHAR2, -- 動態 ORDER BY，不含 ORDER BY 關鍵字
i_update_expression IN VARCHAR2, -- 更新運算式
i_rcount IN NUMBER -- 目標鎖定筆數
) IS
TYPE c_type IS REF CURSOR;
v_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000);
BEGIN
v_sql := 'SELECT rowid FROM ' || i_table_source ||
' WHERE ' || i_search_condition ||
' ORDER BY ' || i_order_expression;
OPEN v_cur FOR v_sql;
LOOP
FETCH v_cur INTO v_rowid;
EXIT WHEN v_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE v_cur;
-- 立即回寫狀態，釋放鎖供其他線程見到新狀態
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
EXCEPTION
WHEN OTHERS THEN RAISE;
END RESB_MT_FIND_ROWS_VOID;
-- ============================================================
-- 尋找並鎖定，回傳結果集 REF CURSOR 供呼叫端處理
-- ============================================================
PROCEDURE RESB_MT_FIND_ROWS (
i_table_source IN VARCHAR2,
i_search_condition IN VARCHAR2,
i_order_expression IN VARCHAR2,
i_update_expression IN VARCHAR2,
i_rcount IN NUMBER,
o_resb_mt_cur OUT resb_mt_cursor_type -- 弱型別 REF CURSOR
) IS
TYPE c_type IS REF CURSOR;
v_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000);
v_o_sql VARCHAR2(4000);
BEGIN
-- 1. 掃描候選 ROWID
v_sql := 'SELECT rowid FROM ' || i_table_source ||
' WHERE ' || i_search_condition ||
' ORDER BY ' || i_order_expression;
-- 2. 回傳完整欄位查詢
v_o_sql := 'SELECT * FROM ' || i_table_source ||
' WHERE rowid IN (SELECT rid FROM RESB_MT_TT_ROWIDS)' ||
' ORDER BY ' || i_order_expression;
OPEN v_cur FOR v_sql;
LOOP
FETCH v_cur INTO v_rowid;
EXIT WHEN v_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE v_cur;
-- 3. 狀態回寫
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
-- 4. 開啟結果集遊標回傳
OPEN o_resb_mt_cur FOR v_o_sql;
EXCEPTION
WHEN OTHERS THEN RAISE;
END RESB_MT_FIND_ROWS;
END RESB_MT_TABLE_PKG;
/
-- 全域暫存表：儲存本批次鎖定成功的 ROWID
CREATE GLOBAL TEMPORARY TABLE RESB_MT_TT_ROWIDS (
rid ROWID
) ON COMMIT PRESERVE ROWS;
-- 弱型別 REF CURSOR 型別（套件規格中宣告）
CREATE OR REPLACE PACKAGE RESB_MT_TABLE_PKG AS
TYPE resb_mt_cursor_type IS REF CURSOR; -- 弱型別
FUNCTION RESB_MT_LOCK_ROW (i_table_source IN VARCHAR2, i_rid IN ROWID) RETURN NUMBER;
PROCEDURE RESB_MT_UPDATE_COLUMNS (i_table_source IN VARCHAR2, i_update_expression IN VARCHAR2);
PROCEDURE RESB_MT_FIND_ROWS_VOID (...);
PROCEDURE RESB_MT_FIND_ROWS (...);
END RESB_MT_TABLE_PKG;
/
-- Java/Python/.NET 伪代碼
BEGIN
-- 線程 A
RESB_MT_TABLE_PKG.RESB_MT_FIND_ROWS(
i_table_source => 'orders',
i_search_condition => "status = 'PENDING'",
i_order_expression => 'created_at',
i_update_expression => "status = 'PROCESSING', worker_id = 1",
i_rcount => 100,
o_resb_mt_cur => :cursor_var -- OUT REF CURSOR
);
-- 讀取 cursor_var 逐行處理業務邏輯
-- 完成後 UPDATE orders SET status='DONE' WHERE rowid IN ...
COMMIT;
END;
WHERE status='PENDING' 直接過濾掉已處理列，大幅減少 FOR UPDATE NOWAIT 碰撞重試。i_rcount 限制單次鎖定筆數，防止長事務與 Undo 爆漲。| 維度 | 自建佇列表 + FOR UPDATE NOWAIT | Oracle AQ |
|---|---|---|
| 部署複雜度 | 低（純 PL/SQL） | 中（需建立隊列表/隊列/訂閱者） |
| 訊息持久化 | 自行管理表結構 | 內建持久化、保留/清除策略 |
| 多消費者/發布訂閱 | 需自行實現 | 原生支援 |
| 延遲/排程投遞 | 需自行實現 | 原生支援 ENQUEUE_TIME/DELAY |
| 監控與管理 | 自訂視圖 | DBA_QUEUE% 完整監控體系 |
| 效能（高併發抽取） | 極佳（ROWID 直達、批次控制） | 優（內部最佳化），但有額外開銷 |
| 適用場景 | ETL 批次抽取、資料遷移、簡單工作佇列 | 企業級訊息總線、非同步解耦、事件驅動架構 |
結論：若需求為「高吞吐批次資料搬運/抽取」，自建 FOR UPDATE NOWAIT 方案更輕量、可控；若需求涉及「訊息路由、持久化保證、多消費者、延遲投遞、標準 JMS 介面」，Oracle AQ 是標準選擇。
DBMS_APPLICATION_INFO.SET_MODULE 標識線程，便於 V$SESSION 監控。DBMS_PARALLEL_EXECUTE (11gR2+) 實現分塊並行處理，進一步簡化應用層線程管理。SKIP LOCKED (11g+) 替代 NOWAIT，完全避免等待/重試邏輯。V$LOCK、V$SESSION_WAIT 確認無熱塊爭用。這套模式已在多個 TB 級遷移專案驗證，穩定支撐每秒萬筆級併發抽取吞吐。
