相关

《Oracle11g自治事务手册》

1 Oracle自治事务是什么？

在PLSQL中，例如下面func1调用func2的场景，如果正常无自治事务的场景，func1的insert1会被func2的commit提交掉， 即使func1最后有rollback，insert 1也提交了。

根因就是func2内部的事务控制语句，影响了外部调用者。

func1()
    insert 1
  
    func2()
        insert 2
        [commit]
    
     rollback
1
2
3
4
5
6
7
8

再看一下Oracle提供的自治事务功能，增加了PRAGMA AUTONOMOUS_TRANSACTION;语法后，func2的事务控制语句完全独立出来，和func1不在有任何关系。

即insert 1不会被func2的commit影响，最后会被func1的rollback回滚掉。

func1()
    insert 1
  
    func2()
        PRAGMA AUTONOMOUS_TRANSACTION; -- 自治事务定义，表示当前块为自治事务
        insert 2
        [commit]
    
     rollback
1
2
3
4
5
6
7
8
9

自治事务常用于一些日志记录，不希望被外层事务影响：外层交易函数可能成功会失败，但都希望记录日志，那么就可以把日志写入的事务包装在自治事务内，实现这样的需求。

2 Oracle自治事务实例

1 非自治事务

drop table t;
create table t ( msg varchar2(25) );
	
create or replace procedure NonAutonomous_Insert
as
begin
    insert into t values ( 'NonAutonomous Insert' );
    commit;
end;
/

begin
    insert into t values ( 'Roll Back Block' );
    NonAutonomous_Insert;
    rollback;
end;
/
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

执行结果：insert into t values ( 'Roll Back Block' );被内层函数提交了，结果中可以看到Roll Back Block。

SYS@orcl11g>select * from t;

MSG
-------------------------
Roll Back Block
NonAutonomous Insert
1
2
3
4
5
6

2 简单自治事务

drop table t;
create table t ( msg varchar2(27) );

create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;
begin
    insert into t values ( 'Autonomous Insert Rollback' );
    rollback;
end;
/
begin
    insert into t values ( 'Commit Block' );
    Autonomous_Insert;
    commit;
end;
/
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

执行结果：外层事务未收到内层事务回滚的影响，外层事务的insert正常提交了。

SYS@orcl11g>select * from t;

MSG
---------------------------
Commit Block
1
2
3
4
5

3 不显式提交/回滚的自治事务

Oracle自治事务必须！显式提交或回滚，否则会抛出异常

drop table t;
create table t ( msg varchar2(25) );
create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;
begin
    insert into t values ( 'Autonomous Insert' );
end;
/
begin
    insert into t values ( 'Roll Back Block' );
    Autonomous_Insert;
    rollback;
end;
/
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

执行结果：抛出异常

ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SYS.AUTONOMOUS_INSERT", line 6
ORA-06512: at line 3
1
2
3
4

4 自治事务内可多次提交回滚，且不影响外层事务

drop table t;
create table t ( msg varchar2(30) );
create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;
begin
    insert into t values ( 'Autonomous Insert Rollback1' );
    rollback;
    insert into t values ( 'Autonomous Insert Commit' );
    commit;
    insert into t values ( 'Autonomous Insert Rollback2' );
    rollback;
end;
/
begin
    insert into t values ( 'Commit Outter Block' );
    Autonomous_Insert;
    commit;
end;
/
select * from t;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

执行结果

SYS@orcl11g>
MSG
------------------------------
Commit Outter Block
Autonomous Insert Commit
1
2
3
4
5

5 自治事务内支持Savepoint

drop table t;
create table t ( msg varchar2(30) );
create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;
begin
    insert into t values ( 'SAVEPOINT S_A' );
    SAVEPOINT s_a;
    insert into t values ( 'Rollback2' );
    ROLLBACK TO SAVEPOINT s_a;
    insert into t values ( 'Autonomous Insert Commit' );
    COMMIT;
end;
/
begin
    insert into t values ( 'Commit Outter Block' );
    Autonomous_Insert;
    rollback;
end;
/
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

执行结果：

SYS@orcl11g>select * from t;

MSG
------------------------------
SAVEPOINT S_A
Autonomous Insert Commit
1
2
3
4
5
6

6 外层事务无法通过Savepoint回滚调自治事务

自治事务完全脱离外层事务，外层事务回滚、savepoint回滚都无法影响自治事务。

drop table t;
create table t ( msg varchar2(25) );
create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;
begin
    insert into t values ( 'Autonomous Insert' );
    commit;
end;
/
begin
    savepoint s_a;
    Autonomous_Insert;
    rollback to savepoint s_a;
end;
/
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

执行结果：外层事务rollback to savepoint业务无法回滚自治事务。

SYS@orcl11g>select * from t;

MSG
-------------------------
Autonomous Insert
1
2
3
4
5

7 自治事务触发器

DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
 
-- Log table:
 
DROP TABLE log;
CREATE TABLE log (
  log_id   NUMBER(6),
  up_date  DATE,
  new_sal  NUMBER(8,2),
  old_sal  NUMBER(8,2)
);
 
-- Autonomous trigger on emp table:
 
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;
/
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 115;
 
COMMIT;
 
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 116;
 
ROLLBACK;
 
-- Show that both committed and rolled-back updates
-- add rows to log table
 
SELECT * FROM log
WHERE log_id = 115 OR log_id = 116;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

执行结果：

    LOG_ID UP_DATE      NEW_SAL    OLD_SAL
---------- --------- ---------- ----------
       115 28-APR-10    3417.75       3255
       116 28-APR-10    3197.25       3045
 
2 rows selected.
1
2
3
4
5
6