Introduction to Autonomous TRANSACTION (Autonomous affairs)
In the process of doing some projects based on the lower version of Oracle, sometimes there are some headaches, such as the need to record some information to the trace table for each step of DML when executing the current transaction (transaction) consisting of multiple DML, because of the atomicity of the transaction, The submission of these trace information will be determined by the commit or rollback of the master transaction. This makes it more difficult to write the program, and the programmer has to log the trace information into an array-like structure, and then put them into a tracking table after the main transaction is finished. Oh, what a nuisance!
Is there an easy way to solve a similar problem?
Oracle8i's autonomous TRANSACTION (autonomous business, the following at) is a good answer.
At is a transaction that is called by the main transaction (the following MT) but is independent of it. When the at is invoked to execute, MT is suspended, and within at, a series of DML can be executed and commit or rollback.
Note that because of the independence of at, its commit and rollback do not affect the performance of Mt. At the end of the at execution, the master transaction gains control and can continue execution.
How does the definition of at be implemented? Let's take a look at its syntax. It's actually very simple.
Simply add the pragma autonomous_transaction to the following PL/SQL Declarations section.
1. Top-level anonymous PL/SQL blocks
2. Functions or Procedure (declaration or Declaration of Independence is available in the package)
3. Methods for SQL Object type
4. Trigger.
Like what:
Declare at in a separate procedure
create or replace procedure Log_error (error_msg in varchar2 (100 is PRAGMA Autonomous_transaction; begin insert into error_log (Sysdate,error_ msg); commit ; end ;
Let's look at an example, (Win2000 Advanced Server + oracle8.1.6, connect as Scott)
Create a table:
CREATE Table MSG (msg varchar2 (120));
First, write an anonymous PL/SQL block with a normal transaction:
DeclareCNT Number:= -1;--} Global variables procedureLocal is begin Select Count(*) intoCnt frommsg; Dbms_output.put_line ('Local: # of Rows is' ||CNT); Insert intoMsgValues('New Record'); Commit; End;begin Delete frommsg; Commit; Insert intoMsgValues('Row 1'); Local Select Count(*) intoCnt frommsg; Dbms_output.put_line ('main: # of Rows is' ||CNT); rollback; Local Insert intoMsgValues('Row 2'); Commit; Local Select Count(*) intoCnt frommsg; Dbms_output.put_line ('main: # of Rows is' ||CNT);End;
Run results (note open serveroutput)
Local: # of rows is 1--sub-program local can ' see ' uncommitted records in the main anonymous block
Main: # of rows is 2--the main anonymous block can ' see ' 2 records (they are all dropped by local commit)
Local: # of rows is 2--sub-program local first ' see ' 2 records, then commit the third record
Local: # of rows is 4--sub-program local and ' see ' the newly added records (they are all dropped by local commit), and then commit the fifth record
Main: # of rows is 5, the main anonymous block finally ' see ' All records.
From this example, we see that the location of commit and rollback will affect the entire current transaction, either in the main anonymous block or in the subroutine.
Now use at to rewrite the procedure local in the anonymous block:
... procedure is pragma autonomous_transaction; begin ...
Re-run (note open serveroutput)
Local: # of rows is 0--sub-program in local cannot ' see ' The uncommitted record in the main anonymous block (because it is independent)
Main: # of rows is 2, the main anonymous block can ' see ' 2 records, but only one is commited.
Local: # of rows is 1--sub-program local can ' see ' its previous commit record, but the records in the main anonymous block have been rollback in advance.
Local: # of rows is 3--sub-program local can ' see ' 3 records including primary anonymous block commit
Main: # of rows is 4, the main anonymous block finally ' see ' All records.
Obviously, at is independent, and when it executes, MT is paused. The commit,rollback of at does not affect the execution of Mt.
At the time of use, there are some considerations, briefly listed as follows:
1. In an anonymous PL/SQL block, only the top-level anonymous PL/SQL blocks can be set to at
2. If at attempts to access resources that are controlled by MT, deadlock may occur.
3. Package cannot be declared at, only function and procedure owned by the package can be declared as at
4. The AT program must end in commit or rollback, otherwise it will produce Oracle error Ora-06519:active autonomous transaction detected and rolled back
In the process of development, if the full use of autonomous transaction characteristics, will be able to achieve a more effective effect.
Introduction to Oracle Autonomous TRANSACTION (autonomous transaction)