ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Autonomous Transaction(自治事务)

Oracle Autonomous Transaction(自治事务)

原创 Linux操作系统 作者:is.x 时间:2012-04-01 15:46:28 0 删除 编辑

Oracle Autonomous transaction自治事务可在PL/SQL块中定义,适用于以下范围:

l  Stored procedure or function

l  Local procedure or function

l  Package

l  Type method

l  Top-level anonymous block

 

此功能从Oracle 8iOracle 8.1.5 and higher)出现,用pragma AUTONOMOUS_TRANSACTION实现,使用时当前事务会被悬挂(suspend),同时Oracle会另开一个自治事务。自治事务从当前事务开始,在其自身的语境中执行,它能独立地被提交或重新运行,而不影响正在运行的事务,即不影响主事务的ACID特性。所以自治事务常被用于记录程序运行日志或者debug异常定位等方面。

 

以下举例说明:

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table t (id number,name varchar2(10));

 

Table created.

 

SQL> create table t_monitor_log (logtext varchar2(20));

 

Table created.

 

SQL> insert into t values(1,'a');

 

1 row created.

 

SQL> insert into t values(2,'b');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

 

1)自治事务和主事务相分离

SQL> create or replace procedure p_autonomous is

  2     PRAGMA AUTONOMOUS_TRANSACTION;

  3     begin

  4             insert into t_monitor_log values('insert test 1');

  5             commit;

  6     end;

  7  /

 

Procedure created.

 

SQL> create or replace procedure p_test is

  2     begin

  3             insert into t values(3,'c');

  4             p_autonomous;

  5             rollback;

  6     end;

  7  /

 

Procedure created.

 

SQL> exec p_test;

 

PL/SQL procedure successfully completed.

 

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

 

SQL> select * from t_monitor_log;

 

LOGTEXT

----------------------------------------

insert test 1

这里主事务被回滚,但是自治事务正常提交。

 

2)进一步证明

这里,我模拟一个主事务出错的例子,导致主事务失败,但自治事务正常提交。

SQL> create or replace procedure p_autonomous is

  2     PRAGMA AUTONOMOUS_TRANSACTION;

  3     begin

  4             insert into t_monitor_log values('insert test 2');

  5             commit;

  6     end;

  7  /

 

Procedure created.

 

SQL> create or replace procedure p_test is

  2     begin

  3             update t set name='aa' where id=1;     

  4             p_autonomous;

  5             update t set name='bb' where id=2;

  6             commit;

  7     end;

  8  /

 

Procedure created.

 

这里我主要是想模拟死锁,所以我另开了一个会话2,执行update操作:

SQL> update t set name='bbb' where id=2;

 

1 row updated.

 

update后不提交,切换回原来会话,执行procedure

SQL> exec p_test;

 

会话hang住,切换回会话2

SQL> update t set name='aaa' where id=1;

 

1 row updated.

 

这时,原会话出现死锁报错

BEGIN p_test; END;

 

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "TEST.P_TEST", line 5

ORA-06512: at line 1

 

会话2将事务都回滚:

SQL> rollback;

 

Rollback complete.

 

这里可以看到,自治事务确实正常提交。

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

 

SQL> select * from t_monitor_log;

 

LOGTEXT

----------------------------------------

insert test 1

insert test 2

 

3)在PL/SQL匿名块中,自治事务必须完成(提交或回滚),主事务没有要求

SQL> create or replace procedure p_autonomous is

  2     PRAGMA AUTONOMOUS_TRANSACTION;

  3     begin

  4             insert into t_monitor_log values('insert test 3');

  5     end;

  6  /

 

Procedure created.

 

SQL> create or replace procedure p_test is

  2     begin

  3             insert into t values(3,'c');

  4             p_autonomous;

  5     end;

  6  /

 

Procedure created.

 

这里创建2个都没有commitrollbackprocedure,测试运行一下:

 

SQL> exec p_test;

BEGIN p_test; END;

 

*

ERROR at line 1:

ORA-06519: active autonomous transaction detected and rolled back

ORA-06512: at "TEST.P_AUTONOMOUS", line 5

ORA-06512: at "TEST.P_TEST", line 4

ORA-06512: at line 1

 

出现报错,autonomous transaction被回滚。

 

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

 

SQL> select * from t_monitor_log;

 

LOGTEXT

----------------------------------------

insert test 1

insert test 2

 

稍作修改autonomous transaction,加上commit

SQL> create or replace procedure p_autonomous is

  2     PRAGMA AUTONOMOUS_TRANSACTION;

  3     begin

  4             insert into t_monitor_log values('insert test 3');

  5             commit;

  6     end;

  7  /

 

Procedure正常完成

SQL> exec p_test;

 

PL/SQL procedure successfully completed.

 

所以在自治事务编写时,事务必须完成,不过主事务没这个要求。

虽然没这个要求,但并不代表主事务已正常完成。

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

         3 c

 

SQL> select * from t_monitor_log;

 

LOGTEXT

----------------------------------------

insert test 1

insert test 2

insert test 3

 

SQL> rollback;

 

Rollback complete.

 

SQL> select * from t;

 

        ID NAME

---------- --------------------

         1 a

         2 b

 

SQL> select * from t_monitor_log;

 

LOGTEXT

----------------------------------------

insert test 1

insert test 2

insert test 3

当前的事务被回滚了,这也说明了procedure执行完成时默认是不会结束事务的。

 

4)自锁造成ORA-60报错

这是一个很有趣的现象

SQL> create or replace procedure p_autonomous is

  2     PRAGMA AUTONOMOUS_TRANSACTION;

  3     begin

  4       insert into t_monitor_log values('insert test 4');

  5             update t set name='aaa' where id=1;

  6             commit;

  7     end;

  8  /

 

Procedure created.

 

SQL> create or replace procedure p_test is

  2     begin

  3             update t set name='aa' where id=1;

  4             p_autonomous;

  5             commit;

  6     end;

  7  /

 

Procedure created.

 

SQL> exec p_test;

BEGIN p_test; END;

 

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "TEST.P_AUTONOMOUS", line 5

ORA-06512: at "TEST.P_TEST", line 4

ORA-06512: at line 1

 

出现死锁了,不过这次是自己锁自己,alert_log信息:

Sun Apr  1 14:55:13 2012

ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/admin/TESTDB/udump/testdb_ora_909412.trc.

 

看看trace中的Deadlock graph

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00060013-00001d58        13      35     X             13      35           X

session 35: DID 0001-000D-000005EB      session 35: DID 0001-000D-000005EB

 

要是不看实验过程,这个自己锁自己的现象,还真让人摸不着头脑呢。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-720170/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    256806