Oracle 巢狀事務 VS 自治事務

Hehuyi_In發表於2020-09-25
Oracle巢狀

一、 概念

巢狀事務(Nested Transaction)： 指在主事務(MT)中巢狀的一個或多個子事務，並且子事務與主事務相互影響。

自治事務(Autonomous Transaction)：由主事務呼叫但又獨立於主事務，子事務對commit和rollback進行自治管理，不影響主事務執行效果。常用於寫入LOG或TRACE資訊便於查詢錯誤。

 

二、 巢狀事務

1．預備Create Table

create table TEST_POLICY   
(   
    POLICY_CODE VARCHAR2(20),   
    POLICY_TYPE CHAR(1)   
);

2. 建立一個巢狀事務procedure

Procedure P_Insert_Policy(I_Policy_code varchar2(20),I_Policy_type char(1)) as    
      cnt number :=0;   
      begin   
          select count(1) into cnt from Test_Policy;   
          Dbms_Output.put_line('records of the test_policy is '|| cnt);   
             
          Insert into Test_Policy values(I_Policy_code, I_Policy_type);   
          commit;--commit in nested transaction    
      end P_Insert_Policy;
    
      --call procedure used in nested transaction   
      PROCEDURE TEST_PL_SQL_ENTRY(I_POL_ID IN VARCHAR2,O_SUCC_FLG OUT VARCHAR2) AS   
      strSql varchar2(500);   
      cnt number := 0;   
      BEGIN   
         delete from test_policy;   
         commit;   
         insert into test_policy values('2010042101', '1');   
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);   
         --call nested transaction   
         P_Insert_Policy('2010042102', '2');   
         rollback;--rollback data for all transactions   
         commit;--master transaction commit   
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);   
         rollback;               
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);               
    END TEST_PL_SQL_ENTRY;   

執行輸出結果如下：

  • records of the test_policy is 1 –-主事務中的操作已經commit   
  • records of the test_policy is 1 –-主事務的操作對Nested transaction有影響。   
  • records of the test_policy is 2 –-Nested transaction 已經Commit   
  • records of the test_policy is 2 –-Nested transaction對主事務有影響。  

將上面的nested transaction的procedure修改一下，不進行commit：

Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,    
                                I_Policy_type t_contract_master.policy_type%type) as    
      cnt number :=0;   
      begin   
          select count(1) into cnt from Test_Policy;   
          Dbms_Output.put_line('records of the test_policy is '|| cnt);   
          Insert into Test_Policy values(I_Policy_code, I_Policy_type);   
          --commit;   
      end P_Insert_Policy;  
 
      PROCEDURE TEST_PL_SQL_ENTRY(I_POL_ID IN VARCHAR2, O_SUCC_FLG OUT VARCHAR2) AS   
      strSql varchar2(500);   
      cnt number := 0;   
      BEGIN   
         delete from test_policy;   
         commit;   
         insert into test_policy values('2010042101', '1');   
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);   
            
         P_Insert_Policy('2010042102', '2');   
         rollback;   
         commit;   
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);   
         rollback;   
            
         select count(1) into cnt from Test_Policy;   
         Dbms_Output.put_line('records of the test_policy is '|| cnt);   
            
    END TEST_PL_SQL_ENTRY;   

執行輸出結果如下： 

  • records of the test_policy is 1 –-主事務中的操作已經commit   
  • records of the test_policy is 1 –-主事務的操作對Nested transaction有影響。   
  • records of the test_policy is 0 –-Nested transaction 的資料被主事務rollback.   
  • records of the test_policy is 0  

     

三、 自治事務

以下型別的 PL/SQL blocks 可以被定義為自治事務：

  • Stored procedures and functions.
  • Local procedures and functions defined in a PL/SQL declaration block.
  • Packaged procedures and functions.
  • Type methods.
  • Top-level anonymous blocks.

定義方法非常簡單，在 DECLARE 後加上 PRAGMA AUTONOMOUS_TRANSACTION 即可。

來看一個簡單的例子，注意以下在一個會話內執行

CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
-- 不要提交

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

-- 自治事務plsql block
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  cnt number := 0;
BEGIN
  select count(*) into cnt from at_test;
  Dbms_Output.put_line('cnt: '|| cnt);  -- 輸出0，因為自治事務不受主事務影響
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
  select count(*) into cnt from at_test;
  Dbms_Output.put_line('cnt: '|| cnt);  -- 輸出8，插入8行
END;
/

-- 回到主事務查詢，一共十行（提交的自治事務影響主事務）
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

-- 回滾，再查詢，發現主事務插入的2行沒了，自治事務插入的還在
ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

再看一個與前面巢狀事務對應的例子：

create Procedure p_insert_policy_new(i_policy_code Varchar2(20), i_policy_type char(1)) as   
       Pragma Autonomous_Transaction; --定義自治事務
       cnt number := 0;   
       begin   
           select count(1) into cnt from test_policy;   
           Dbms_Output.put_line('records of the test policy table is: '||cnt);       
           Insert into Test_Policy values(I_Policy_code, I_Policy_type);                             
           commit;   
           select count(1) into cnt from test_policy;   
           Dbms_Output.put_line('records of the test policy table is: '||cnt);    
       end p_insert_policy_new;   
     /

     --call auto trans procedure   
create PROCEDURE TEST_PL_SQL_ENTRY(I_POL_ID IN VARCHAR2, O_SUCC_FLG OUT VARCHAR2) AS   
       strSql varchar2(500);   
       cnt number := 0;   
       v_policyCode t_contract_master.policy_code%type;   
       BEGIN   
          delete from test_policy;   
          commit;   
          insert into test_policy values('2010042101', '1');   
          select count(1) into cnt from Test_Policy;   
          Dbms_Output.put_line('records of the test_policy is '|| cnt);   
             
          p_insert_policy_new('2010042102', '2');   
          select count(1) into cnt from Test_Policy;   
          Dbms_Output.put_line('records of the test_policy is '|| cnt);   
          rollback;   
          select policy_code into v_policyCode from test_policy;   
          Dbms_Output.put_line('policy_code: '|| v_policyCode);   
          select count(1) into cnt from Test_Policy;   
          Dbms_Output.put_line('records of the test_policy is '|| cnt);   
             
       END TEST_PL_SQL_ENTRY;   
/

執行結果如下：

  • records of the test_policy is 1 -- 主事務插入資料
  • records of the test policy table is: 0 -- 自治事務不受主事務影響
  • records of the test policy table is: 1 -- 自治事務提交
  • records of the test_policy is 2 -- 提交的自治事務影響主事務
  • policy_code: 2010042102 -- 主事務回滾，自治事務已提交資料不受影響
  • records of the test_policy is 1 -- 同上，行數為1

 

下面是一個用自治事務收集報錯日誌資訊的例子：

定義一個自治事務儲存過程

CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

外層程式碼呼叫儲存過程，報錯時記錄日誌

BEGIN
  INSERT INTO at_test (id, description) VALUES (998, 'Description for 998');
  -- Force invalid insert.
  INSERT INTO at_test (id, description) VALUES (999, NULL);

EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

SELECT * FROM at_test WHERE id >= 998;
no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP                 ERROR_MESSAGE
---------- -----------------------      -----------------------------------------------
         1 28-FEB-2006 11:10:10.107625  ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")

 

參考

https://blog.csdn.net/xujinyang/article/details/7029848

https://oracle-base.com/articles/misc/autonomous-transactions

相關文章