Re: Nested Transactions, Abort All

Simon Riggs wrote:
> On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote:
> > Simon Riggs wrote:
> > >
> > > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
> >
> > I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used
> > in SQL99 for savepoints:
> >
> > ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
> > [ <savepoint clause> ]
> >
> >
> > RELEASE only discards the savepoint name, I thought.
> >
>
> ERR-OHH Yes, dead right. Forgive my confusion, I knew there was
> something different about Oracle's support of RELEASE. Check out:
>
> https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm
>
> last thing on page...

Interesting Oracle doesn't support RELEASE or savepoint levels:

T271, Savepoints Oracle supports this feature, except:

* Oracle does not support RELEASE SAVEPOINT.
* Oracle does not support savepoint levels.

> > > Oracle's support of autonomous transactions looks to be identical to
> > > nested transactions (Alvaro's advice required there...). They don't
> > > allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> > > host program that calls a stored procedure, which also contains
> > > BEGIN/COMMIT, effectively giving nested txns.
> >
> > Oracle has nested transactions too? Can you supply an example?
>
> It's hard to quote a short example.... so try this link instead
>
> http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm
>
> Wasn't there some description of autonomous transactions on the stuff I
> sent previously?

This is not a nested transaction. It is "autonomous" meaning it can
commit independent of the outer transaction:

The grand new benefit of autonomous transactions for database triggers
is that inside those triggers you can now issue COMMITs and ROLLBACKs,
statements that are otherwise not allowed in database triggers. The
changes you commit and roll back will not, however, affect the main
transaction that caused the database trigger to fire. They will only
apply to DML activity taking place inside the trigger itself (or through
stored program units called within the trigger).

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073