Re: trying to understand transaction control in pl/sql

kquirici_at_yahoo.com (ken quirici) wrote:
> Hi,
>
> I'm observing behavior in 9i about transactions in pl/sql unnamed blocks
> that seems undocumented (altho I find the existing doc. hard to
> understand anyway), and I wonder if anyone can explain it in terms of
> some notion of transactional 'boundaries' - when a transaction starts,
> when it ends, within a pl/sql unname block. It's probably obvious but I
> have brain lock at the moment.
>
> I have a test table a(a1 number) initially empty.
>
> I run:
>
> begin
> insert into a values(1);
> insert into a values(2);
> insert into a values('a');
> end;
> /
>
> As expected an error results trying to insert the nonnumeric 'a' into a.
> When I select from a, it's empty.

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.)

>
> Then I run:
>
> begin
> insert into a values(1);
> insert into a values(2);
> insert into a values('a');
> exception
> when others then
> null;
> end;
> /
>
> Now of course I see no error since it's trapped. But when I look at the
> table a, it has two rows, with a1 = 1 and with a1=2.

But now do a rollback. Those two rows disappear.

> In the first case it seems the whole block is a transaction;

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".)

> but just
> adding the error trap (which doesn't do anything) makes each statement in
> the block a transaction (it seems).

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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB