Autonomous transactions for Oracle

An autonomous transaction (autonomous transaction) allows you to create a "transaction in a transaction" that can be committed or rolled back independently of its parent transaction. With an autonomous transaction, you can suspend the currently executing transaction, start a new transaction, complete some work, and then commit or roll back, all without affecting the state of the currently executing transaction. Autonomous transactions provide a new way to control transactions with PL/SQL, which can be used to:

• Top-level anonymous block;

• Local (procedure in process), independent or packaged functions and processes;

• The method of the object type;

• Database triggers.

Use examples to demonstrate how autonomous transactions work

--Create a test table for saving information [email protected]>create table t  ( MSG VARCHAR2 ( )); table created.--creating a stored procedure for an autonomous transaction [email protected]>create or replace procedure  autonomous_insert  2  as        pragma  Autonomous_transaction;---indicates an autonomous transaction statement   4  begin  5           insert into t values  (  ' Autonomous insert '  );   6          commit;  7  end;   8  /procedure created.--creating a normal stored procedure [email protected]>create or  Replace procedure nonautonomous_insert  2  as  3  begin   4          insert into t values   (  ' Nonautonomous insert '  );  5          commit;  6   end;  7  /procedure created.

Observe the behavior of Non-Self-governing transactions using PL/SQL code

[Email protected]>begin 2 insert into t values (' Anonymous Block ');  3 Nonautonomous_insert;  4 rollback;  5 end; 6/pl/sql procedure successfully completed. [Email protected]>select * from t; MSG---------------------------------------------------------------------------Anonymous blocknonautonomous Insert

A commit in the process of observing a non-autonomous transaction also commits the parent transaction that called it, and the rollback in the parent transaction does not work.

Observe the behavior of Non-Self-governing transactions using PL/SQL code

[Email protected]>delete from t;2 rows deleted. [Email protected]>commit; Commit complete.        [email protected]>begin insert INTO t values (' Anonymous Block ');        Autonomous_insert;  Rollback;end; 6/pl/sql procedure successfully completed. [Email protected]>select * from t; MSG---------------------------------------------------------------------------Autonomous Insert

As you can see, a commit in an autonomous transaction commits only its own transaction, and the statement for the parent transaction does not work, and the rollback in the parent transaction has no effect on the statements in the autonomous transaction.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1888528

Autonomous transactions for Oracle