Re: Autonomous Transaction is back

After few failed attempt to propose Autonomous transaction earlier. I along with Simon Riggs would like to propose again but completely different in approach.

 

We also had discussion about this feature in last PGCon2015 Unconference Day, those who missed this discussion, please refer

                                https://wiki.postgresql.org/wiki/AutonomousTransactionsUnconference2015

 

 

Before jumping into the design and code proposal for this feature, me along with Simon Riggs wanted to propose its behavior and usage to keep everyone in the same boat.

So we have summarized the behavior and usage of the Autonomous Transaction based on the discussion with community members in last PGCon2015 Unconference Day:

 

Behavior of Autonomous Transaction:

1.            The autonomous transaction treated as a completely different transaction from the master transaction.

2.            It should be allowed to deadlock with master transaction. We need to work-out a solution to avoid deadlock.

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

4.            Outer (i.e. main or upper autonomous) transaction to be suspended while the inner autonomous transaction is running.

5.            Outer transaction should not see data of inner till inner is committed (serializable upper transaction should not see even after inner transaction commit).

 

How to Use Autonomous Transaction:

1. We can issue explicit command to start an Autonomous transaction as below:

                BEGIN AUTONOMOUS TRANSACTION  (Don’t worry about keywords at this point.)

                Do you work.

                COMMIT/ROLLBACK       (Will commit/rollback the autonomous transaction and will return to main transaction or upper autonomous transaction).             

 

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.

3. We can make whole procedure itself as autonomous, which will be similar to start autonomous transaction in the beginning of the procedure and commit/rollback at the end of the procedure.  

 

There was another discussion in Unconference Day to decide whether to implement COMMIT/ROLLBACK inside the procedure or autonomous transaction. So our opinion about this is that

                COMMIT/ROLLBACK inside procedure will be somewhat different from Autonomous Transaction as incase of first, once we commit inside the procedure,

it commits everything done before call of procedure. This is the behavior of Oracle.

So in this case user required to be very careful to not do any operation before call of procedure, which is not yet intended to be committed inside procedure.

               

So we can prefer to implement Autonomous Transaction, which will not only be compatible with Oracle but also gives really strong required features.

 

I have not put the use-cases here as already we agree about its strong use-cases.

 

Requesting for everyone's opinion regarding this based on which we can proceed to enhance/tune/re-write our design.               

 

Thanks and Regards,

Kumar Rajeev Rastogi