Oracle autonomous transaction Pragma autonomous_transaction

Operate the table that triggers the trigger in the trigger, and use the Pragma autonomous_transaction option.

15.1 why cannot I roll back an audit using autonomous transactions?

Generally, when a trigger is used to prohibit operations such as table update, if a log is logged, the trigger will roll back the log when an exception is thrown. Autonomous transactions can prevent this point.

Avoid mutating table

That is, to operate the table that triggers the trigger in the trigger

Use DDL to write data to a database in a trigger

Stored Procedures or functions that have write operations (insert, update, delete, create, alter, and commit) on databases cannot be simply called using SQL, at this point you can set it to autonomous transactions, avoiding ORA-14552 (cannot execute DDL, commit, rollback in one query or DML), ORA-14551 (cannot perform DML operations in one query) errors. Note that the function must have a return value, but only the in parameter (the out or in/out parameter is not allowed ).

Develop more modular code

In large-scale development, autonomous transactions can make the code more modular. If the Code fails or succeeds, it will not affect other operations of the caller. The cost is that the caller has lost control of this module, in addition, the module cannot reference data not submitted by the caller.

15.2 how to control work transactions

The entire declare block belongs to the parent transaction, and the autonomous transaction starts from the first in after Pragma. As long as the begin block is still in scope, it belongs to the autonomous transaction.For example, if the declare module declares a database-writing function, the function is a parent transaction although it is executed in the stored procedure of the autonomous firm; all functions and stored procedures called in autonomous transactions and any triggers triggered are part of this autonomous transaction.

Autonomous transactions can be nested, And the nested depth is restricted only by the init. ora parameter transactions (number of concurrent transactions, which is 1.1 times of sessions by default.

Scope

1. variables in the package

The autonomous transaction can see and modify the variables of the parent transaction, and the parent transaction will detect this change, and there is no rollback problem.

2. Session settings/parameters

The autonomous transaction shares the same session environment with the parent transaction. modifications made by alter session are valid for the entire session. However, SET transaction is transaction-level and only valid for the transaction that initiates the modification.

3. Database Modification

The changes committed by the parent transaction are visible to the autonomous transaction, but those not committed are invisible to the autonomous transaction. Whether the changes made by the autonomous transaction are visible to the parent transaction depends on the isolation level (isolation level ).

For a cursor, it depends on the position where it is opened. If it is opened in the parent transaction, the uncommitted modifications of the parent transaction are valid for it, and these modifications are also visible in the autonomous transaction; opening in an autonomous transaction makes uncommitted modifications to the parent transaction invisible.

If the default read committed isolation level is used, modifications to the autonomous transaction are visible to the parent transaction. If serializable is used, modifications to the autonomous transaction are invisible.

4. Lock

Parent and autonomous transactions are completely different transactions, so they cannot share locks.

To end an autonomous transaction, you must submit a commit, rollback, or execute DDL.

The storage point cannot be rolled back to a storage point in the parent transaction in the autonomous transaction. It can only be used internally.

15.3 The last note does not support distributed transactions. As of 8.1.7, distributed transactions are not supported in autonomous transactions.

Only PL/SQL can be used to roll back all transactions. If an autonomous transaction fails, all transactions are rolled back, even if the parent transaction has an exception processing module.

Transaction-level temporary tables only one transaction in each session can access the transaction-level temporary table (transactions in multiple sessions can be operated concurrently ).

15.4 possible errors
ORA-06519-check for active autonomous transactions, rollback-No commit, rollback, or DDL operations to exit autonomous transactions

ORA-14450-attempting to access a transaction-level temporary table in use

ORA-00060-deadlock detected while waiting for resources

-- A simple example

Create or replace procedure ddl_proc (str_ SQL in varchar2)
Is
Pragma autonomous_transaction;
Begin
Execute immediate str_ SQL;
End;

In the trigger, you can use ddl_proc to execute DDL, DML