During some projects based on low-release Oracle, sometimes some headaches are encountered. For example, if you want to perform the current transaction (transaction) composed of multiple DML, record some information for each step DML to track In the table, due to the atomicity of the transaction, the submission of these tracking information will determine the common commit or rollback. This one of the difficulty of writing the program is increased, and the programmer has to record these tracking information to the structure of similar arrays. In, then put them in the trace table after the end of the main transaction. Hey, it is really trouble!
Is there a simple way to solve similar problems?
Oracle8i's AutoMous Transaction (Autonomous Affairs, the following AT) is a good answer.
AT is called by the main transaction (hereinafter, the following is independent of its transaction. When the AT is called executed, the MT is suspended, in the inside of the AT, a series of DMLs can be executed and commit or rollback.
Note that due to the independence of AT, its CommT and Rollback do not affect the execution of MT. After the At the end of the AT, the main transaction gets control and can continue.
see picture 1:
figure 1:
How to implement the definition of AT? Let's take a look at its grammar. In fact, it is very simple.
Simply add PRAGMA Autonomous_Transaction in the following PL / SQL declaration.
1. Top-level anonymous PL / SQL block
2. Functions or procedure (independent statement or statement can be in package)
3. SQL Object Type method
4. Trigger.
such as:
Declaring AT in a separate procedure
Create or Replace Procedure
LOG_ERROR (Error_Msg in varcha2 (100))
IS
Pragma autonomous_transaction;
Begin
INSERT INTO ERROR_LOG VALUES (Sysdate, Error_MSG);
COMMIT;
END;
Let's take 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 ordinary transactions:
Declare
CNT Number: = -1; -} Global Variables
Procedure Local IS
Begin
SELECT Count (*) INTO CNT from MSG;
DBMS_OUTPUT.PUT_LINE ('local: # of rows is' || cnt);
INSERT INTO MSG VALUES ('New Record');
COMMIT;
END;
Begin
Delete from msg;
COMMIT;
INSERT INTO MSG VALUES ('ROW 1');
Local;
SELECT Count (*) INTO 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 CNT from MSG;
DBMS_OUTPUT.PUT_LINE ('main: # of rows is' || cnt);
END;
Operation results (pay attention to open serveroutput)
Local: # of rows IS 1 -> Subplit Local can "see the uncommitted record in the 'main anonym block
Main: # of rows is 2 -> The main appendix block can 'see' 2 records (they are all dropped by Local Commit)
Local: # of rows is 2 -> Subprogram Local first 'See' 2 records, then commit, then Article 3 Record
Local: # of rows is 4 -> Subprint local and 'See' new increased records (they are all dropped by local commit), then COMMIT, the fifth record
Main: # of rows is 5 -> The main apartment block last 'See' all records.
From this example, we see that the position of Commit and Rollback will affect the entire current transaction in the location of the main apartment or in a subroutine.
Now use AT to change the Procedure Local in anonymous block:
...
Procedure Local IS
Pragma autonomous_transaction;
Begin
...
Run (pay attention to open serveroutput)
Local: # of rows is 0 -> Subplit Local can't 'see the uncommitted record in the' main anonym block (because it is independent)
Main: # of rows IS 2 -> The main apartment block can 'see' 2 records, but only one is compted.
Local: # of rows is 1 -> Subprogram Local can 'see' its previous commit record, but records in the main appended block have been taken in advance ROLLBACK
Local: # of rows IS 3 -> Subplit Local can 'see' 3 records include records of the main anonymous block COMMIT
Main: # of rows is 4 -> The main apartment block last 'See' all records.
Obviously, AT is independent, when it is executed, MT is suspended. At the commism, Rollback does not affect the execution of MT.
When using AT, there are some precautions that are simply listed as follows:
1. In anonymous PL / SQL block, only top-level anonymous PL / SQL blocks can be set to AT
2. If the AT tries to access the resources controlled by MT, there may be a DEADLOCK.
3. Package can't be declared as AT, only Function and Procedure owned by Package can be declared as AT
4. The AT program must end with the end of commit or rollback, otherwise Oracle error ORA-06519: Active Autonomous Transaction Detected and Rolled Back
At the time of program development, if you make full use of the Features of Autonomous Transaction, you can get the effect of multiplication.
Reference:
Metalink.Oracle.com
Oracle8i Manual
