Re: Autonomous Transaction is back

On Thu, Jul 23, 2015 at 11:01 AM, Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com>
wrote:

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

For an autonomous transaction not to conflict with the master, either it
shouldn't take conflicting resources or when it does so, they should be
shared by the master transaction. The first needs to be implemented in an
application, the later will need careful design of how to share such
resources (hierarchial resource sharing?) and how to release the resources
when the ATX ends. Given that the transactions are autonomous as per point
1, they should be allowed to conflict and the deadlock detection would take
care of such deadlocks.

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

This might just be an implementation artifact in other RDBMSes, and may not
necessarily apply to the implementation done here. So, we should not list
it as "necessary" behaviour of ATX. Just as an example (no way suggesting
that the design should be this way), if we implement autonomous
transactions using background worker processes or additional backends like
prepared transactions, we will have a limit on the number of simultaneously
open ATX. This may translate to nesting limit if we implement 4, but not if
we implement parallel autonomous transactions.

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

To me only 1 and 5 seem to be the real behaviour we should define, may be
4, but that restricts parallelism. Although parallel autonomous
transactions will have its own problems to solve like sharing code. But the
design shouldn't restrict it from being a future possibility, I guess.

>
>
> *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 *
>
>
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company