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
