Re: Autonomous Transaction is back

On 23 July 2015 21:04, Robert Haas Wrote:
On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com> wrote:
>> 2. It should be allowed to deadlock with master transaction. We
>> need to work-out a solution to avoid deadlock.

>This sentence seems to contradict itself. I thought the consensus was that the transaction should NOT conflict with the master transaction.

Since we are saying transaction is autonomous to parent transaction, we cannot guarantee that it does not take any conflicting lock unless otherwise designed so by the application.
But yes, we should have mechanism to deal with the possible deadlock.

>> 3. It can support multiple level of nesting based on the
>> configuration (may be max as 70).

>Why have any fixed maximum?

Since we are planning to have nested autonomous transaction, so it is required to have limit on this so that resources can be controlled.

>> 2. The above commands can be issued either inside the procedure to
>> make few statements of procedure inside autonomous transaction or even
>> in stand-alone query execution.

>I think inside a procedure the autonomous transaction will need to be lexically scoped. You won't be able to do this, for example:

>BEGIN AUTONOMOUS TRANSACTION;
>FOR x IN SELECT ... LOOP
> COMMIT;
> BEGIN AUTONOMOUS TRANSACTION;
>END LOOP;

I am not sure, how we will be able to control this. IMHO user should be able to control this, especially since it does not have any meaning from user perspective.
Please let me know if I am missing something here.

>Rather you'd have to do something like this:

>FOR x IN SELECT .. LOOP
> BEGIN WITH AUTONOMOUS TRANSACTION
> do stuff
> END;
>END LOOP;

Thanks and Regards,
Kumar Rajeev Rastogi