Re: trying to understand transaction control in pl/sql

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
Received on Thu Oct 28 2004 - 04:59:13 CDT