自治事务可能导致死锁,测试如下:
--1,测试表
create table test(x int,y int);
alter table test
add constraint pk_test primary key(x);
create or replace procedure auto_proc(p_x int,p_y int) as
pragma autonomous_transaction;
begin
insert into test(x,y) values(p_x,p_y);
commit;
end;
/
--2,测试1,set autocommit off
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bocnet@dev95
SQL> set timing on
SQL> set autocommit off
SQL> select * from test;
no rows selected
Elapsed: 00:00:00.00
SQL> insert into test(x,y) values(1,1);
1 row created.
Elapsed: 00:00:00.01
SQL> exec auto_proc(1,2);
BEGIN auto_proc(1,2); END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "BOCNET.AUTO_PROC", line 4
ORA-06512: at line 1
Elapsed: 00:00:03.10
SQL> select * from test;
X Y
---------- ----------
1 1
Elapsed: 00:00:00.00
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
==>ORA-00060: deadlock,发现死锁后,自治事务被回退
==>alert_bocnet.log:
Wed Apr 15 11:17:29 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/product/10.2.0/db_1/admin/bocnet/udump/bocnet_ora_10394.trc.
--3,测试2,set autocommit on
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bocnet@dev95
SQL> set autocommit on
SQL> set timing on
SQL> select * from test;
no rows selected
Elapsed: 00:00:00.00
SQL> insert into test(x,y) values(1,1);
1 row created.
Commit complete.
Elapsed: 00:00:00.01
SQL> exec auto_proc(1,2);
BEGIN auto_proc(1,2); END;
*
ERROR at line 1:
ORA-00001: unique constraint (BOCNET.PK_TEST) violated
ORA-06512: at "BOCNET.AUTO_PROC", line 4
ORA-06512: at line 1
Elapsed: 00:00:00.01
SQL> select * from test;
X Y
---------- ----------
1 1
Elapsed: 00:00:00.00
==>ORA-00001: unique constraint,自治事务违反唯一约束被回退
--1,测试表
create table test(x int,y int);
alter table test
add constraint pk_test primary key(x);
create or replace procedure auto_proc(p_x int,p_y int) as
pragma autonomous_transaction;
begin
insert into test(x,y) values(p_x,p_y);
commit;
end;
/
--2,测试1,set autocommit off
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bocnet@dev95
SQL> set timing on
SQL> set autocommit off
SQL> select * from test;
no rows selected
Elapsed: 00:00:00.00
SQL> insert into test(x,y) values(1,1);
1 row created.
Elapsed: 00:00:00.01
SQL> exec auto_proc(1,2);
BEGIN auto_proc(1,2); END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "BOCNET.AUTO_PROC", line 4
ORA-06512: at line 1
Elapsed: 00:00:03.10
SQL> select * from test;
X Y
---------- ----------
1 1
Elapsed: 00:00:00.00
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
==>ORA-00060: deadlock,发现死锁后,自治事务被回退
==>alert_bocnet.log:
Wed Apr 15 11:17:29 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/product/10.2.0/db_1/admin/bocnet/udump/bocnet_ora_10394.trc.
--3,测试2,set autocommit on
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bocnet@dev95
SQL> set autocommit on
SQL> set timing on
SQL> select * from test;
no rows selected
Elapsed: 00:00:00.00
SQL> insert into test(x,y) values(1,1);
1 row created.
Commit complete.
Elapsed: 00:00:00.01
SQL> exec auto_proc(1,2);
BEGIN auto_proc(1,2); END;
*
ERROR at line 1:
ORA-00001: unique constraint (BOCNET.PK_TEST) violated
ORA-06512: at "BOCNET.AUTO_PROC", line 4
ORA-06512: at line 1
Elapsed: 00:00:00.01
SQL> select * from test;
X Y
---------- ----------
1 1
Elapsed: 00:00:00.00
==>ORA-00001: unique constraint,自治事务违反唯一约束被回退
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-762383/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-762383/