16.2. Visibility
Visibility rules for autonomous transactions are the same as for independent transactions executed via dblink
. Autonomous transactions do not see the effects of their parent transaction because the latter has not been committed yet. At the same time, if other autonomous transactions precede the active autonomous transaction and are already committed, then the active transaction will see the effects of these transactions. The parent transaction might see the effects of its autonomous transactions, depending on its own isolation level. For example, if the isolation level is Read Committed, the parent transaction will see all changes made by autonomous transactions. However, if the parent transaction is using the Repeatable Read isolation level, the changes made by autonomous transactions will be invisible.
Consider the following example, showing a set of both consequent and nested transactions. Each transaction in the set is labeled with its status. In addition, the transactions that have been already committed are highlighted.
- Ordinary transaction T0 [PAUSED] - Autonomous transaction A0 [COMMITTED] - Autonomous transaction A1 [COMMITTED] - Autonomous transaction A2 [PAUSED] - Autonomous transaction A2.0 [COMMITTED] - Autonomous transaction A2.1 [PAUSED] - Autonomous transaction A2.1.0 [ACTIVE]
In the above example, the active autonomous transaction A2.1.0
does not see the effects of the preceding transactions T0
, A2
, and A2.1
that are paused and not committed yet, regardless of their type. However, the active transaction sees the effects of the preceding autonomous transactions A0
, A1
, and A2.0
that have been already committed.
Autonomous transactions can cause single-session deadlocks, since an autonomous transaction can conflict with one of the paused transactions in its session. If an autonomous transaction tries to obtain any resource locked by its parent, a deadlock is reported.