| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is it possible to nest transactions?
On Thu, 04 Mar 1999 22:03:45 -0600, "John P. Higgins" <jh33378_at_deere.com> wrote:
>You start an Oracle transaction by issueing an INSERT, UPDATE, DELETE or
>SELECT FOR UPDATE statement. The transaction ends with a COMMIT or ROLLBACK
>statement or with any DDL statement. All the statements are comitted or
>rolled back as an atomic unit. All locks are released at COMMIT or ROLLBACK
>or DDL.
>
>Within the Oracle transaction, you can set SAVEPOINT's. Later, you can
>rollback to an identified savepoint. A rollback to a savepoint does not end
>the transaction. Nor does a rollback to a savepoint release any locks. You
>can execute more statements before the final transaction-ending COMMIT or
>ROLLBACK.
>
>The engine will not autocommit. Your front-end tool might. The SQL*Plus
>front-end tool can be set to autocommit or not. If your connection is lost,
>Oracle auto rolls back whatever transaction was in-flight.
>
In 8i you can have sub transactions.
eg.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> select * from a;
no rows selected
SQL> create or replace
2 procedure rb as
3 pragma autonomous_transaction;
4 begin
5 insert into a values ( 3 );
6 insert into a values ( 4 );
7 rollback;
8 end;
9 /
Procedure created.
SQL> begin
2 insert into a values ( 1 );
3 insert into a values ( 2 );
4 rb;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from a;
N
1
2
chris.
>Csaba Toth wrote:
>
>> Hi all,
>>
>> Coming from a Sybase (SQLA) background, I would like to know, whether it
>> is possible to nest transactions. Also, is there a command you can
>> issue to explicitly tell Oracle you want to start a transaction? (Sort
>> of like in SQLA "BEGIN TRANSACTION")
>> If nested transactions are possible, does Oracle keep a variable of some
>> sort (one that you can hopefully query) on the transaction nesting
>> depth?
>>
>> I am searching the doco, but no mention of nested transactions.
>>
>> While I am at it, is there an engine option you can set to tell Oracle
>> to AUTOCOMMIT after DML statements?
>>
>> Any help is much appreciated.
>>
>> Regards
>>
>> Csaba Toth
>>
>> --
>> Csaba Toth
>> Programmer [Development]
>> csaba_at_addease.com.au
>>
>> Addease Pty. Ltd.
>> P.O. Box 1484
>> COLLINGWOOD VIC 3066
>> Australia
>>
>> Ph : +61 3 9416 2944
>> Fax: +61 3 9416 2580
>> web: http://www.addease.com.au
>
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |