Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Autonomous Transactions

Re: Autonomous Transactions

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 28 Aug 2006 08:31:39 -0400
Message-ID: <4lg619F1ofjhU1@individual.net>


devjnr_at_gmail.com wrote:
> I noticed that a commit inside a procedure, commit also all changes
> outside its begin...end block.
>
> Is it possible? Why?
>
> begin
>
> insert into test values ('test');
> aaa_proc1; -- in this procedure at the end there is commit
> instruction.
>
> end;
>
> This anonymous block cause 'test' value to be inserted.
>
> Is it correct? I think (proc) commit should affect only its begin end
> bloc....

You are confusing save points with transactions. Could it be you have an MS SQL Server background? In SQL Server a nested transaction isn't really committed until the outermost transaction commits. (it's a stack kind of thing) In Oracle (and DB2 FWIW) transactions are always global. If you want to open a new nested scope to be rolled back individually you use a save point.
An autonomous transaction is an entirely different animal. An autonomous transaction is entirely separated from the transaction that started. It's as if the transaction had opened a separate connection to the database.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/
Received on Mon Aug 28 2006 - 07:31:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US