Re: trying to understand transaction control in pl/sql

kquirici_at_yahoo.com (ken quirici) wrote in message news:<eeca902a.0410280159.6c499074_at_posting.google.com>...
> Hi Xho,
>
> I think you've helped clarify it via: 'effectively a nested
> transaction' and 'ongoing' transaction - it didn't occur to me
> that there could be [effectively] nested transactions, and that
> when the unnamed block errored, it's transaction could continue,
> be 'ongoing'. Presumably, the unnamed block is a 'sub-transaction'
> within some larger transaction in which it is embedded (unless
> it's immediately preceded by a commit). You can rollback and keep
> the outer transaction going, but a commit terminates all
> transactions?
>
> I THINK its getting clearer.
>
> Ken
>
>
>
> ctcgag_at_hotmail.com wrote in message news:<20041027161808.319$C7_at_newsreader.com>...
> >
> > Beginning a block automatically causes a savepoint. Leaving a block
> > through an unhandled exception automatically rollsback to that savepoint
> > (unless, of course, a commit or rollback happened in the block before the
> > exception was thrown, thus obliterating that initial savepoint.)
> >
> >
> > But now do a rollback. Those two rows disappear.
> >
> > The whole block is effectively a nested transaction. The nested
> > transaction with the unhandled error is rolled back to its own beginning,
> > but not to the beginning of the outer transaction. (try doing a manual
> > insert before the first block is executed. The inserts done in the block
> > are rolledback, but the manual insert done before the block is neither
> > rolled back nor committed, it is still "active".)
> >
> > No. When the block leaves, the root-level transaction that created those
> > two rows is still ongoing, neither rolled back nor committed. The reason
> > you can see the rows is because you are still inside that transaction. If
> > you open a different connection, you will not be able to the see the rows
> > (until the first connection commits).
> >
> > Xho

And watch out for leaving your SQL*PLUS session. By default EXIT does a COMMIT !!!

Ed Received on Thu Oct 28 2004 - 14:29:47 CDT