Oracle自治事务详解

2022-09-19 15:06:44 浏览数 (1)

相关 《Oracle11g自治事务手册》

1 Oracle自治事务是什么？

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

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

代码语言：javascript复制
func1()
    insert 1
  
    func2()
        insert 2
        [commit]
    
     rollback

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

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

代码语言：javascript复制
func1()
    insert 1
  
    func2()
        PRAGMA AUTONOMOUS_TRANSACTION; -- 自治事务定义，表示当前块为自治事务
        insert 2
        [commit]
    
     rollback

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

2 Oracle自治事务实例

1 非自治事务

代码语言：javascript复制
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;

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

代码语言：javascript复制
SYS@orcl11g>select * from t;

MSG
-------------------------
Roll Back Block
NonAutonomous Insert

2 简单自治事务

代码语言：javascript复制
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;

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

代码语言：javascript复制
SYS@orcl11g>select * from t;

MSG
---------------------------
Commit Block

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

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

代码语言：javascript复制
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;

执行结果：抛出异常

代码语言：javascript复制
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

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

代码语言：javascript复制
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;

执行结果

代码语言：javascript复制
SYS@orcl11g>
MSG
------------------------------
Commit Outter Block
Autonomous Insert Commit

5 自治事务内支持Savepoint

代码语言：javascript复制
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;

执行结果：

代码语言：javascript复制
SYS@orcl11g>select * from t;

MSG
------------------------------
SAVEPOINT S_A
Autonomous Insert Commit

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

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

代码语言：javascript复制
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;

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

代码语言：javascript复制
SYS@orcl11g>select * from t;

MSG
-------------------------
Autonomous Insert

7 自治事务触发器

代码语言：javascript复制
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;

执行结果：

代码语言：javascript复制
    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.
oracle sql

0 人点赞