oracle資料庫自治事務的學習筆記

oracle自治事務是指的預存程序和函數可以自己處理內部事務不受外部事務的影響，用pragma autonomous_transaction來聲明。要建立一個自治事務，必須在匿名塊的最高層或者預存程序、函數、資料包或觸發器的定義部分中，使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION語句。
以下是使用自治事務的幾種情況：

（1）Declaring Autonomous Function in Package

CREATE OR REPLACE PACKAGE emp_actions AS
  -- package specification
  FUNCTION raise_salary(emp_id    NUMBER,
                        sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
 
CREATE OR REPLACE PACKAGE BODY emp_actions AS
  -- package body
  -- code for function raise_salary
  FUNCTION raise_salary(emp_id    NUMBER,
                        sal_raise NUMBER) RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    new_sal NUMBER(8,2);
  BEGIN
    UPDATE employees
       SET salary = salary + sal_raise
     WHERE employee_id = emp_id;
    COMMIT;
    SELECT salary
      INTO new_sal
      FROM employees
     WHERE employee_id = emp_id;
    RETURN new_sal;
  END raise_salary;
END emp_actions;
/
（2）Declaring Autonomous Standalone Procedure

 

CREATE OR REPLACE PROCEDURE lower_salary(emp_id NUMBER,
                                         amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees
     SET salary = salary - amount
   WHERE employee_id = emp_id;
 
  COMMIT;
END lower_salary;
/
（3）Declaring Autonomous PL/SQL Block

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  emp_id NUMBER(6) := 200;
  amount NUMBER(6, 2) := 200;
BEGIN
  UPDATE employees
     SET salary = salary - amount
   WHERE employee_id = emp_id;
 
  COMMIT;
END;
/
（4）Declaring Autonomous in Trigger

CREATE OR REPLACE TRIGGER log_sal
  BEFORE UPDATE OF salary ON emp
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log
    (log_id, up_date, new_sal, old_sal)
  VALUES
    (:old.employee_id, SYSDATE, :new.salary, :old.salary);
  COMMIT;
END;
自治事務可以嵌套，嵌套深度等只受init.ora參數transactions（同時並發的事務數，預設為sessions的1.1倍）制約。因為自治事務是與主事務(簡稱MT)相分離的，所以它不能檢測到被修改過的行的目前狀態。這就好像在主事務提交之前，它們一直處於單獨的會話裡，對自治事務來說，它們是停用。然而，反過來情況就不同了：主事務能夠檢測到已經執行過的自治事務的結果。
如果AT試圖訪問被MT控制的資源,可能有deadlock發生，Package不能被聲明為AT，只有package所擁有的function和procedure 才能聲明為AT。主事務與自治事務是完全不同的事務，因此無法共用鎖定等。結束一個自治事務必須提交一個commit、rollback或執行ddl，否則會產生Oracle錯誤ORA-06519: active autonomous transaction detected and rolled back 。儲存點無法在自治事務中復原到父事務中的一個儲存點，只能在內部使用儲存點。
可能遇到的錯誤：
ora-06519 – 檢查到活動自治事務，復原——退出自治事務時沒有提交、復原或ddl操作
ora-14450 – 試圖訪問正在使用的事務級暫存資料表
ora-00060 – 等待資源時檢查到死結