Introduction to Autonomous TRANSACTION (Autonomous affairs)

 





 





sometimes have some headaches when doing projects based on a lower version of Oracle. For example, to perform a current transaction (transaction) consisting of multiple DML, record some information for each step of DML into the trace table, because of the atomicity of the transaction, The submission of these trace information will be determined by the commit or rollback of the main transaction. This makes writing programs more difficult, and programmers have to record them in similar array structures and then store them in the tracking table after the main transaction ends. Oh, what a nuisance!





is there a simple way to solve a similar problem?





Oracle8i's autonomous TRANSACTION (autonomous transaction, below) is a good answer.





at is a transaction that is invoked by the master transaction (the following MT) but is independent of it. When the at is invoked to execute, MT is suspended, and a series of DML can be executed and commit or rollback within the at.





Note that because of the independence of at, its commit and rollback do not affect the execution effect of Mt. At the end of the at execution, the master transaction is granted control and can continue to execute.





See Figure 1:





 





Figure 1:





 





How to implement the definition of at? Let's take a look at its syntax. actually very simple.





only need the following Pl/sql declaration section plus pragma autonomous_transaction is ok.





1. Top-level anonymous pl/sql block





2. Functions or Procedure (independent declarations or declarations are available in package)





3. Method of SQL Object type





4. Trigger.





 





    





 





 





For example:





 





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 values (sysdate,error_msg);





COMMIT;





end;





 





let's look at an example, (Win2000 Advanced Server + oracle8.1.6, connect as Scott)





set up a table:





CREATE TABLE MSG (msg varchar2 (120));





First, write an anonymous pl/sql block with ordinary transactions:





 





Declare





CNT Number: =-1; --} Global variables





procedure The Local is





begin





Select COUNT (*) into the CNT from MSG;





dbms_output.put_line (' Local: # of rows are ' | | CNT);





       





INSERT into MSG values (the ' New Record ');





commit;





end;





 





 





 





 





 





begin





Delete from Msg;





commit;





INSERT into MSG values (' Row 1 ');





Local;





Select COUNT (*) into the CNT from MSG;





Dbms_output.put_line (' main: # of rows is ' | | CNT);





rollback;





 





Local;





INSERT into MSG values (' Row 2 ');





commit;





 





Local;





Select COUNT (*) into the CNT from MSG;





Dbms_output.put_line (' main: # of rows is ' | | CNT);





end;





 





Run result (note open serveroutput)





 





Local: # of the rows is 1-> The subroutine local can ' see ' the uncommitted record in the primary anonymous block





main: # of rows is 2-> The primary anonymous block can ' see ' 2 records (they are all local commits)





Local: # of the rows is 2-> sub program local first ' see ' 2 records, then commit a third record





Local: # of rows are 4-> local and ' see ' the newly added records (all of which are local commits), and then commit the fifth record





main: # of rows is 5-> The main anonymous block finally ' see ' all the records.





 





 





from this example, we see that the location of the commit and rollback, whether in the primary anonymous block or in a subroutine, affects the entire current transaction.





 





 











 





 





now rewrites the procedure local in the anonymous block with an at:





...





procedure The Local is





pragma autonomous_transaction;





begin





...





 





Rerun (note open serveroutput)





Local: # of the rows is 0-> the sub program Local cannot ' see ' The uncommitted record in the primary anonymous block (because it is independent)





main: # of rows is 2-> The primary anonymous block can ' see ' 2 records, but only one is commited.





Local: # of the rows is 1-> The subroutine local can ' see ' the record of its previous commit, but the records in the master anonymous block have been rollback in advance





Local: # of the rows is 3-> The Sub program Local can ' see ' 3 records including primary anonymous block commit records





main: # of rows is 4-> The main anonymous block finally ' see ' all the records.











Obviously, at is independent, and when it executes, MT is paused. The at Commit,rollback does not affect the execution of Mt.





 





use at, there are some considerations, simply listed as follows:





1. In an anonymous pl/sql block, only the top-level anonymous pl/sql block can be set to at





2. If at attempts to access a resource that is controlled by MT, deadlock may occur.





3. Package cannot be declared at, only the function and procedure owned by Package can be declared at





4. The AT program must end with a commit or rollback, or it will produce an 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 ineffective results.





 





 





Reference:





metalink.oracle.com





oracle8i Manual