Oracle 嵌套事务 VS 自治事务

原创

Hehuyi_In_666 2020-09-25 00:05:17 博主文章分类：Oracle ©著作权

文章标签 Test SQL 嵌套事务 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者Hehuyi_In_666的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、概念

嵌套事务(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://oracle-base.com/articles/misc/autonomous-transactions