DB2 9.7 自治交易：以 AUTONOMOUS 預存程序實現獨立提交/回滾

什麼是自治交易？

自治交易擁有獨立的 COMMIT/ROLLBACK 範圍，其結果不影響呼叫端的未提交變更；反之，呼叫端的提交/回滾也不影響自治交易已完成的變更。呼叫會話會暫停直到自治程序返回控制權——這不是平行執行機制。

建立自治預存程序

在 CREATE PROCEDURE 中指定 AUTONOMOUS 關鍵字：

CREATE OR REPLACE PROCEDURE your_proc_name()
LANGUAGE SQL
AUTONOMOUS
BEGIN
  -- 自治工作
END;

執行時會在獨立會話中運行：成功隱式提交、失敗回滾，皆不影響呼叫端交易。

實戰案例：銀行敏感資料存取稽核

情境

銀行要求每次查詢客戶敏感資料必須記錄：查詢員工 ID、客戶帳號、時間戳。介面以預存程序實作。

步驟 1：建立資料表

DROP TABLE customerSensitiveInfo;
CREATE TABLE customerSensitiveInfo (customerAccountNumber INT, amountOverdue INT);

DROP TABLE log_table;
CREATE TABLE log_table (
  queryingEmployeeID VARCHAR(100),
  customerAccNumber INT,
  when TIMESTAMP
);
COMMIT;

步驟 2：建立一般記錄程序 (無 AUTONOMOUS)

CREATE OR REPLACE PROCEDURE log_query (
  IN queryingEmployee VARCHAR(100),
  IN accNumber INT,
  IN when TIMESTAMP
)
LANGUAGE SQL
BEGIN
  INSERT INTO log_table VALUES (queryingEmployee, accNumber, when);
END;
COMMIT;

步驟 3：建立查詢介面程序

CREATE OR REPLACE PROCEDURE get_AmountOverdue (
  IN accountNumber INT,
  OUT overdue INT
)
LANGUAGE SQL
BEGIN
  DECLARE due INT;
  DECLARE currentTime TIMESTAMP;
  SET currentTime = CURRENT TIMESTAMP;
  CALL log_query(CURRENT USER, accountNumber, currentTime);
  SELECT amountOverdue INTO due FROM customerSensitiveInfo 
    WHERE customerAccountNumber = accountNumber;
  SET overdue = due;
END;
COMMIT;

步驟 4：測試無自治交易行為

INSERT INTO customerSensitiveInfo VALUES (12345, 10000), (12346, 20000);
COMMIT;

-- 呼叫並立即回滾
CALL get_AmountOverdue(12345, ?);
ROLLBACK;

-- 檢查 log_table
SELECT * FROM log_table;  -- 0 筆，因整個交易被回滾

步驟 5：加上 AUTONOMOUS 關鍵字

CREATE OR REPLACE PROCEDURE log_query (
  IN queryingEmployee VARCHAR(100),
  IN accNumber INT,
  IN when TIMESTAMP
)
LANGUAGE SQL
AUTONOMOUS
BEGIN
  INSERT INTO log_table VALUES (queryingEmployee, accNumber, when);
END;
COMMIT;

步驟 6：再次測試

CALL get_AmountOverdue(12345, ?);
ROLLBACK;

SELECT * FROM log_table;  
-- 現在有 1 筆記錄！即使外層交易回滾，自治記錄已獨立提交

關鍵重點

• AUTONOMOUS 只能用於 LANGUAGE SQL 的預存程序
• 自治程序可包含 DML、DDL、動態 SQL 等
• 巢狀呼叫自治程序時，每層皆獨立提交/回滾
• 適用場景：稽核記錄、錯誤日誌、計數器更新、獨立業務邏輯