Oracle Database autonomy transaction details

Oracle DatabaseOfAutonomous transactionsThis is what we will introduce in this article. Next let's take a look at its mysteries.

A database transaction is a unit operation. Either all operations are successful or all operations fail. In Oracle, a transaction starts from the execution of the first data management language DML) statement until a COMMIT statement is executed, the transaction is committed and saved, or a ROLLBACK statement is executed, stop this operation.

It is difficult to record the error information to the database table because the transaction fails to be re-run, the INSERT statement used to write log entries has not been completed yet.

To address this dilemma, Oracle provides a convenient method, that is, autonomous transactions. An autonomous transaction starts from the current transaction and runs in its own context. They can be submitted or re-run independently without affecting running transactions. As a result, they form an ideal form of writing error log tables. When an error is detected in a transaction, you can insert a row in the error log table and submit it, and then roll back the primary transaction without losing this insert.

Because the autonomous transaction is separated from the primary transaction, it cannot detect the current status of the modified row. It seems that they are always in separate sessions before the primary transaction is committed, and they are unavailable for autonomous transactions. However, in turn, the situation is different: the main transaction can detect the results of self-governing transactions that have been executed.

To create an autonomous transaction, you must use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of the anonymous block or in the stored procedure, function, data packet, or trigger definition section. The SQL statements executed in such a module or process are autonomous.

The trigger cannot contain the COMMIT statement, unless the PRAGMA AUTONOMOUS_TRANSACTION flag exists. However, only the statements in the trigger can be committed, but not the primary transaction.

Autonomous transactions:

 
 

  
  
create or replace procedure AutoNomouse_Insert is PRAGMA AUTONOMOUS_TRANSACTION;   
  
  
begin insert into Msg values('AutoNomouse Insert');   
  
  
commit;   
  
  
end; 
 
 

Non-autonomous transactions:

 
 

  
  
CREATE OR REPLACE Procedure NonAutoNomouse_Insert as   
  
  
begin insert into Msg Values('NonAutonomouse Insert');   
  
  
commit;  
  
  
end;  
 
 

 
 

  
  
SQL> begin  
  
  
insert into Msg Values('This Main Info');  
  
  
NonAutoNomouse_Insert;  
  
  
rollback;  
  
  
end  
  
  
;  
  
  
/ PL/SQL procedure successfully completed SQL> select * from msg; MSG This Main Info  
  
  
NonAutonomouse Insert 
 
 

Because there is a COMMIT in the process, RULLBACK in the anonymous block does not work. Therefore, the ROLLBACK will affect the entire transaction in a non-autonomous transaction.

Let's look at another situation:

 
 

  
  
SQL> delete msg; 2 rows deleted SQL> there is no COMMIT here;
  
  
SQL> begin
  
  
Insert into Msg Values ('this Main info ');
  
  
Rollback; -- ROLLBACK is added here;
  
  
NonAutoNomouse_Insert;
  
  
Rollback;
  
  
End
  
  
;
  
  
/PL/SQL procedure successfully completed SQL> select * from msg; MSG This Main Info
  
  
NonAutonomouse Insert
  
  
NonAutonomouse Insert
 
 

Why is there no ROLLBACK (DELETE * from msg? Because the process is a new SESSION, the previous SESSION is normally exited and automatically submitted;

 
 

  
  
SQL> commit; Commit complete   
  
  
SQL> select * from msg; MSG This Main Info  
  
  
NonAutonomouse Insert  
  
  
NonAutonomouse Insert SQL> commit; Commit complete SQL> select * from msg; MSG This Main Info  
  
  
NonAutonomouse Insert  
  
  
NonAutonomouse Insert  
 
 

A new SESSION is a meaningless transaction control statement.

 
 

  
  
SQL> delete msg;   
  
  
SQL>3 rows deleted   
  
  
SQL> commit;  
  
  
SQL>Commit complete   
  
  
SQL> select * from msg;   
  
  
MSG 
 
 

Let's take a look at autonomous transactions:

 
 

  
  
SQL> begin  
  
  
insert into Msg Values('This Main Info');  
  
  
AutoNomouse_Insert;  
  
  
rollback;  
  
  
end  
  
  
;  
  
  
/ PL/SQL procedure successfully completed  
  
  
 
  
  
SQL> select * from msg; MSG AutoNomouse Insert 
 
 

We can see that it is a row of data. Obviously, the first SQL INSERT statement is ROLLBACK, which proves that an autonomous transaction is a transaction independent of the main program and will not affect the control of the main transaction. In addition, in the distributed environment, we often encounter ORA-02064 ERROR, that is, because the main transaction has its own transaction control statement, but the called remote process also has its own transaction control statement, of course, an error will be reported. If we declare the called process as an autonomous transaction, then OK.

Here is an introduction to Oracle Database autonomous transactions. I hope this introduction will be helpful to you!