Re: Autonomous procedure falsely committing?

dloomis_at_gmail.com (Dan Loomis) wrote in message news:<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

Dan, the execution of an anonymous transaction as part of a distributed transaction in 8i is unsupported. This is stated in the 8i manuals and normally results in an error being raised. Version 9+ does support performing an anonymous transaction as part of a distributed transaction, but I would expect this to work only when connecting to another version 9+ database.

It would appear each database is following the rules for its version.

IMHO -- Mark D Powell -- Received on Sat Jul 10 2004 - 01:56:29 CEST