Re: Is it possible to nest transactions?

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.

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
Received on Thu Mar 04 1999 - 22:03:45 CST