如何使用PRAGMAAUTONOMOUS_TRANSACTION進行事務控制 - PolarDB

當需要在主業務事務中執行一個獨立操作，並確保該操作的成功或失敗不影響主事務時，可使用自治事務PRAGMA AUTONOMOUS_TRANSACTION。自治事務會啟動一個完全獨立的事務，擁有自己的提交和復原邏輯。該特性適合記錄審計日誌或錯誤資訊等情境：即使主商務程序因錯誤而需要整體復原，通過自治事務記錄的日誌依然能夠被成功儲存，確保操作的可追溯性。

工作原理

自治事務的核心在於“隔離”。調用包含 PRAGMA AUTONOMOUS_TRANSACTION 的程式時，會暫停當前的父事務，並啟動一個全新的、獨立的子事務。

隔離性：自治事務看不到父事務中未認可的變更，同樣，父事務也看不到自治事務中未認可的變更。
獨立提交/復原：自治事務內的 COMMIT 或 ROLLBACK 隻影響其自身，完全獨立於父事務的最終 COMMIT 或 ROLLBACK。
串連消耗：每次調用自治事務會在後台建立一個獨立的資料庫連接。當自治事務結束時，該串連被釋放。

適用範圍

PRAGMA AUTONOMOUS_TRANSACTION 指令可以用於以下SPL程式單元的聲明部分：

獨立的預存程序和函數。
匿名塊。
包中聲明的預存程序和函數。
觸發器。
物件類型方法。

重要

在Oracle文法相容 2.0版本中，出於系統穩定性與事務一致性的考慮，目前不支援在觸發器中使用自治事務。

PRAGMA AUTONOMOUS_TRANSACTION指令必須放在聲明地區的起始位置。

-- 預存程序中使用自治事務的正確文法
CREATE OR REPLACE PROCEDURE procedure_name IS    
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN    
  -- 商務邏輯    
  COMMIT;
END;

-- 觸發器中使用自治事務的正確文法，只在相容 Oracle 1.0使用
CREATE OR REPLACE TRIGGER trigger_name
    AFTER INSERT OR UPDATE OR DELETE ON table_name
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- 商務邏輯
    COMMIT;
END;

應用情境

自治事務匿名塊記錄審計日誌

此情境確保無論主業務操作最終是提交還是復原，相關的審計日誌都能被可靠地記錄下來。

建立員工表emp和審計日誌表empauditlog。

CREATE TABLE emp (
    emp_id   INT,
    emp_name VARCHAR(50),
    job      VARCHAR(50)
);

CREATE TABLE empauditlog (
    audit_date      DATE,
    audit_user      VARCHAR2(20),
    audit_desc      VARCHAR2(100)
);

開啟一個事務，插入資料，然後復原該事務。

BEGIN;
INSERT INTO emp (emp_id, emp_name, job) VALUES (101, '張三', '工程師');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO empauditlog VALUES (SYSDATE, USER, 'Added employee(s)');
    COMMIT;
END;
ROLLBACK;

由於主事務執行了ROLLBACK，emp表中的資料插入操作已被撤銷，資料未持久化。而empauditlog 表通過自治事務獨立提交，審計日誌成功寫入，不受主交易回復影響。

-- 查詢 emp 表，應為空白，因為主事務已復原
SELECT * FROM emp WHERE emp_id = 101;
-- 查詢 empauditlog 表，應包含一條審計記錄，因為是自治事務並已提交
SELECT * FROM empauditlog;

自治事務預存程序記錄失敗日誌

本情境用於實現：當庫存充足時，正常建立訂單；當庫存不足時，復原訂單操作，但仍記錄使用者的“下單嘗試”日誌。

建立 orders（訂單）、inventory（庫存）和 operation_log（動作記錄）三張表。初始化庫存為 5，當下單數量為 10 時，觸發庫存不足異常，並記錄失敗日誌。

-- 1. 訂單表（主業務資料）
CREATE TABLE orders (
    order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id      NUMBER,
    product_id   NUMBER,
    qty          NUMBER,
    order_time   TIMESTAMP DEFAULT SYSDATE
);

-- 2. 庫存表
CREATE TABLE inventory (
    product_id   NUMBER PRIMARY KEY,
    stock        NUMBER
);

-- 3. 動作記錄表（用於自治事務寫入）
CREATE TABLE operation_log (
    log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id      NUMBER,
    product_id   NUMBER,
    action       VARCHAR2(50),  -- 如 'ORDER_ATTEMPT'
    status       VARCHAR2(20),  -- 'SUCCESS' / 'FAILED'
    reason       VARCHAR2(200), -- 失敗原因
    log_time     TIMESTAMP DEFAULT SYSDATE
);

-- 4. 初始化資料
INSERT INTO inventory (product_id, stock) VALUES (1001, 5); -- 商品1001 只有5件

建立支援自治事務的預存程序，確保在主交易回復時仍能持久化動作記錄。

CREATE OR REPLACE PROCEDURE log_order_attempt(
    p_user_id    IN NUMBER,
    p_product_id IN NUMBER,
    p_reason     IN VARCHAR2
) IS 
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 過程內部聲明區
BEGIN
    INSERT INTO operation_log (
        user_id, product_id, action, status, reason, log_time
    ) VALUES (
        p_user_id,p_product_id,'ORDER_ATTEMPT','FAILED',p_reason,SYSDATE
    );
    COMMIT; -- 獨立提交，不受主事務影響
END;

使用者嘗試下單，因庫存不足失敗，自治事務確保日誌獨立提交。

DECLARE
    v_user_id     NUMBER := 123;
    v_product_id  NUMBER := 1001;
    v_order_qty   NUMBER := 10;
    v_stock       NUMBER;
BEGIN
    SELECT stock INTO v_stock FROM inventory 
    WHERE product_id = v_product_id FOR UPDATE;

    IF v_stock < v_order_qty THEN
        log_order_attempt(
            p_user_id    => v_user_id,
            p_product_id => v_product_id,
            p_reason     => 'Insufficient stock: need ' || v_order_qty || ', available ' || v_stock
        );

        RAISE_APPLICATION_ERROR(-20001, '庫存不足，訂單建立失敗');
    END IF;

    INSERT INTO orders (user_id, product_id, qty)
    VALUES (v_user_id, v_product_id, v_order_qty);

    UPDATE inventory SET stock = stock - v_order_qty 
    WHERE product_id = v_product_id;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

驗證結果：訂單表為空白，庫存未變，因主事務已復原；而動作記錄中已有記錄，得益於自治事務的成功提交。
```
SELECT * FROM orders;
SELECT * FROM inventory;
SELECT * FROM operation_log;
```

操作建議

串連消耗風險

每個自治事務在執行期間會消耗一個獨立的資料庫連接。在高並發情境下，濫用自治事務可能迅速耗盡資料庫的 max_connections，導致新的串連請求失敗，從而引發服務中斷。

風險情境：在迴圈中或高頻調用的函數裡使用自治事務。
嵌套風險：自治事務可以嵌套，每嵌套一層就會多佔用一個串連。例如，一個嵌套了3層的自治事務會話會臨時佔用4個串連（1個主會話 + 3個自治事務）。
容量規劃：使用前，請評估自治事務對資料庫連接數的額外需求，並相應調整 max_connections 參數。建議進行壓力測試。

相容性說明

PolarDB PostgreSQL版（相容Oracle）的自治事務實現與 Oracle 資料庫存在一個關鍵行為差異，這可能在資料庫遷移時引入潛在風險。

特性

Oracle 行為

PolarDB PostgreSQL版（相容Oracle）行為

風險說明

未顯式提交/復原

拋出 ORA-06519 錯誤，強制開發人員處理未結束的事務。

隱式提交。如果代碼塊正常結束且沒有 COMMIT 或 ROLLBACK，事務會自動認可。

高風險。這種行為可能導致資料在非預期的情況下被提交，破壞事務的原子性。為保證代碼在不同環境下的行為一致性，強烈建議始終顯式地使用 COMMIT 或 ROLLBACK。

常見問題

如何排查 “sorry, too many clients already” 錯誤？

此錯誤表明資料庫的活動串連數已達到 max_connections 上限。如果懷疑是自治事務引起的，可按以下步驟排查：

檢查當前串連數和最大串連數配置。

-- 查看總串連數
SELECT count(*) FROM pg_stat_activity;

-- 查看當前配置的最大串連數
SHOW max_connections;

識別長時間啟動並執行自治事務。使用以下查詢找出執行時間超過5分鐘的自治事務相關查詢。

SELECT pid, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'  
AND query ILIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'  
AND now() - query_start > interval '5 minutes';

解決方案：
- 最佳化代碼：檢查是否存在迴圈調用或長時間啟動並執行自治事務，並進行最佳化。
- 增加串連數：如果使用合理但串連仍然不足，評估後可適當增加 max_connections 的值。
- 設定逾時：為會話設定 statement_timeout，以自動終止長時間啟動並執行查詢，防止串連被永久佔用。

PolarDB：PRAGMA AUTONOMOUS_TRANSACTION