Nando Dessena wrote:

> Adriano,
> I understand the requirements for the autonomous transaction feature.
> I am not sure why it should be linked to routines, though. Wouldn't it
> be better to allow for BEGIN TRANSACTION, COMMIT and ROLLBACK
> statements in PSQL? It will give all advantages of your idea, plus:
>
> a) ability to execute only part of a trigger or SP in an autonomous
> transaction;
>
> b) ability to execute an anonymous block in an autonomous transaction
> (admittedly not terribly useful, if not to avoid the need to process
> COMMIT and ROLLBACK on the client for simple scripts);
>
> c) ability to execute more than one SPs in the same autonomous
> transaction, without having to wrap them into a single calling SP.
>
> Maybe, instead of BEGIN TRANSACTION and COMMIT/ROLLBACK (which would
> also require a WHEN..DO block for rollback), a more compact syntax
> could be invented, such as:
>
> BEGIN AUTONOMOUS TRANSACTION
>
> ... code here
>
> END AUTONOMOUS TRANSACTION -- commits.
>
> P.S. I have a fear that this is a feature very easy to misuse, but I guess
> Firebird can't do much about that except produce good documentation
> that lists the cases in which to use autonomous transactions, and the
> cases in which it's better to avoid them.
>
>
>

What is the relationship between an autonomous transaction and its
parent? If they are independent, the parent won't be able to see the
results of the autonomous transaction, which is theoretically acceptable
but likely to cause some serious complaint. With a modest amount of the
work, the autonomous transaction could be made visible to the parent but
would still surive rollback of the parent.

I agree that such a facility would be useful. But I also agree with
Nando that implementing them programmatically with explicit BEGIN / END
statements is a more general, flexible, and powerful mechanism than
declaring a procedure to execute under an autonomous transaction
allowing, for example, autonomous transactions in loops.


--
James Starkey, Senior Software Architect
MySQL Inc., Manchester, MA, USA, www.mysql.com
Office: 978 526-1376