Autonomous procedure falsely committing?

From: Dan Loomis <dloomis_at_gmail.com>
Date: 9 Jul 2004 07:44:23 -0700
Message-ID: <e2cb6420.0407090644.1bdf6f20_at_posting.google.com>



Hi guys - I have a weird condition here I'm trying to get resolution on, and unfortunately I'm not getting anywhere with my friends at Oracle. I'm hoping it's something simple, or at the very least it's a documented bug that I can simply reference and move on.

The following code and session output (I think) documents a condition where a procedure marked for an automomous commit is commiting an INSERT over a database link that is outside of its transactional boundry. In a nutshell, my INSERT is committing when it shouldn't, *only* in situations where I have an autonomous transaction, *and* I have an error handler that doesn't gracefully rollback the transaction. My test case:

--setup only - the actual test run is further down create table cca_autonomous_commit_test_9i (

	id number,
	creation_date date

)
/

create or replace procedure autonomous_insert is

        PRAGMA AUTONOMOUS_TRANSACTION; begin         

--insert into local database table

	insert into cca_autonomous_commit_test_9i values ( 1 ,sysdate );
	commit;

end;
/

create or replace procedure dloomis_test

is

begin

--reset environment

	delete from cca_autonomous_commit_test_8i_at_xxcts_sjoe_ccais;
	delete from cca_autonomous_commit_test_9i;
	
	commit;
	

--insert over database link.
insert into cca_autonomous_commit_test_8i_at_xxcts_sjoe_ccais values (
1, sysdate );         

--call my autonomous procedure, which should *NOT* commit the record
above

        autonomous_insert;

--raise error, for testing purposes only
        raise NO_DATA_FOUND;         

end;
/

--the test output
Session 1:

--Execute dloomis_test, which should throw no_data_found by design:

SQL> exec dloomis_test
BEGIN dloomis_test; END;

*
ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "APPS.DLOOMIS_TEST", line 20
ORA-06512: at line 1

Session 2 - everything looks ok:

SQL> select count(*) from cca_autonomous_commit_test_9i;

  COUNT(*)


         1

SQL> select count(*) from
cca_autonomous_commit_test_8i_at_xxcts_sjoe_ccais;

  COUNT(*)


         0

Now, if I Control-C out of session 1, the 8i record is commited somehow:

Session 2:

SQL> select count(*) from
cca_autonomous_commit_test_8i_at_xxcts_sjoe_ccais;

  COUNT(*)


         1

This is not always reproducable, but if you run the test several times over you will get the results above. My question is - how is the 8i record being commited, if the only commit being issued is in the autonomous procedure? I realize an explicit ROLLBACK command in the WHEN OTHERS block will fix the issue, but I'd still like an answer on why this happens under the above condition.

Thanks a bunch -

Dan Loomis
IT Engineer, Cisco Systems Received on Fri Jul 09 2004 - 16:44:23 CEST

Original text of this message