Java 端協調 Oracle 預存程序序列化執行：DBMS_LOCK 實戰

資源加鎖可序列化存取。Oracle 提供 DBMS_LOCK 套件管理 User Lock，讓多 Session 序列執行特定預存程序、排他存取外部裝置/服務、偵測事務提交/回滾（鎖釋放時機）。

為何鎖必須在資料庫端？

Java 端 synchronized 無法保證：應用伺服器掛掉時預存程序可能仍在執行，Oracle Session (RAC) 未立即釋放。重啟應用伺服器後，舊預存程序可能仍在跑，再次呼叫無法保證序列化。故同步鎖必須在 Oracle DB 端。

DBMS_LOCK 核心 API

ALLOCATE_UNIQUE - 分配唯一鎖代碼

PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE(
    lockname IN VARCHAR2,
    lockhandle OUT VARCHAR2,
    expiration_secs IN INTEGER DEFAULT 864000);

同名 lockname 取得相同 lockhandle。名稱最大 128 字元、大小寫敏感、避免 ORA$ 開頭（保留）。執行後隱性 commit，不可在 Trigger 呼叫。映射存於 SYS.DBMS_LOCK_ALLOCATED。

REQUEST - 申請鎖

FUNCTION DBMS_LOCK.REQUEST(
    lockhandle IN VARCHAR2,
    lockmode IN INTEGER DEFAULT X_MODE,
    timeout IN INTEGER DEFAULT MAXWAIT,
    release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

回傳值：0 成功、1 逾時、2 死結、3 參數錯誤、4 已擁有、5 非法 handle。共享伺服器模式與分散式事務建議 release_on_commit=true。推薦用 lockhandle 方式。用戶自訂鎖 ID 範圍 0~1073741823，2000000000~2147483647 保留。

RELEASE - 釋放鎖

FUNCTION DBMS_LOCK.RELEASE(lockhandle IN VARCHAR2) RETURN INTEGER;

回傳：0 成功、3 參數錯、4 未擁有、5 非法 handle。不用時立即釋放，避免死結。

Wrapper 模式封裝鎖邏輯（避免侵入既有預存程序）

CREATE OR REPLACE PACKAGE BODY frm_test_task_pkg IS
    PROCEDURE app_task_wrapper_proc(i_procname IN VARCHAR2) AS
        PRAGMA AUTONOMOUS_TRANSACTION;
        cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
        fdbk BINARY_INTEGER;
    BEGIN
        DBMS_SQL.PARSE(cur, 'begin ' || i_procname || ';end;', DBMS_SQL.NATIVE);
        fdbk := DBMS_SQL.EXECUTE(cur);
        DBMS_OUTPUT.PUT_LINE('Fetch rows : ' || fdbk);
        DBMS_SQL.CLOSE_CURSOR(cur);
        COMMIT;
    END;

    FUNCTION frm_test_task_func(i_lock_name IN VARCHAR2, i_procname IN VARCHAR2, ...) RETURN NUMBER IS
        v_result NUMBER; v_lockhandle VARCHAR2(200); v_sid PLS_INTEGER;
    BEGIN
        DBMS_LOCK.ALLOCATE_UNIQUE(i_lock_name, v_lockhandle, i_expiration_time);
        v_result := DBMS_LOCK.REQUEST(v_lockhandle, DBMS_LOCK.X_MODE, i_wait_time, TRUE);
        SELECT SYS_CONTEXT('USERENV','SID') INTO v_sid FROM DUAL;
        IF v_result != 0 THEN ... ELSE app_task_wrapper_proc(i_procname); END IF;
        COMMIT; RETURN v_result;
    END;
END;

Java 端呼叫 frm_test_task_func，傳入需序列化的預存程序名與邏輯鎖名。

替代方案：Java 端管理鎖週期（無侵入性）

Oracle 保證 Session 斷開釋放鎖。應用伺服器掛掉後，Session 逾時釋放鎖。Java 端顯式呼叫申請/釋放：

PROCEDURE frm_test_lock_acquire(i_lock_name IN VARCHAR2, ..., o_result OUT NUMBER, o_lockhandle OUT VARCHAR2) AS
BEGIN
    SYS.DBMS_LOCK.ALLOCATE_UNIQUE(i_lock_name, v_lockhandle, i_expiration_time);
    v_result := SYS.DBMS_LOCK.REQUEST(v_lockhandle, DBMS_LOCK.X_MODE, i_wait_time, FALSE);
    o_result := v_result; o_lockhandle := v_lockhandle;
END;

FUNCTION frm_test_lock_release(i_lockhandle IN VARCHAR2) RETURN NUMBER AS
BEGIN
    RETURN SYS.DBMS_LOCK.RELEASE(i_lockhandle);
END;

Java 端流程：acquire → 執行業務預存程序 → release。配合 Template Method 模式封裝，注意 Connection 不可在子類提前關閉（需留供釋放鎖用），可用 Wrapper/Decorator 模式包裝 Connection。

總結

1. 鎖必須在 DB 端，Java 端鎖不可靠。
2. Wrapper 預存程序避免侵入既有代碼。
3. 動態執行預存程序傳參複雜，改由 Java 端管理鎖週期。
4. Template Method + Connection Wrapper 確保鎖正確釋放。