L
Test Case of transaction nesting
(1)
First, create a table:Create tables MSG (MSG varchar2 (100 ));
(2)
Then, create the sub-transaction Stored Procedure local.Create or replace procedure local is CNT number: =-1; -- global variables begin select count (*) into CNT from MSG; dbms_output.put_line ('local: # Of rows is '| CNT); insert into MSG values ('new record ');
Commit;End;
(3)
Finally, use the following PL/SQL anonymous block to call local;Declare CNT number: =-1; 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;
The running result (enable set serveroutput on) is as follows:Local: # of rows is 1-> In the subroutine local, you can see the uncommitted record main in the primary anonymous block: # Of rows is 2-> the primary anonymous block shows '2 records (all of which are caused by local commit). Local: # Of rows is 2-> the subprogram local first displays '2 records, and then commit the third record local: # Of rows is 4-> the subprogram local displays the newly added records (all of them are dropped by the local commit), and then commit the fifth record main: # Of rows is 5-> all records are displayed at the end of the primary anonymous block. the PL/SQL process is successfully completed.
(4)
During the reconstruction process, change commit to rollback, as shown below:Create or replace procedure local is CNT number: =-1; -- global variables begin select count (*) into CNT from MSG; dbms_output.put_line ('local: # Of rows is '| CNT); insert into MSG values ('new record ');
Rollback;End;
Run the PL/SQL anonymous block in step (3,
The running result (enable set serveroutput on) is as follows:Local: # of rows is 1 Main: # of rows is 0-> the subroutine rolls back a record of the primary anonymous block, so the result is 0 local: # Of rows is 0 local: # of rows is 1-> the primary anonymous block successfully commts one of its records. The subroutine can also see main: # Of rows is 1 PL/SQL process completed successfully.
(5)
During the reconstruction process, remove the commit as follows:Create or replace procedure local is CNT number: =-1; -- global variables begin select count (*) into CNT from MSG; dbms_output.put_line ('local: # Of rows is '| CNT); insert into MSG values ('new record'); end;
Run the PL/SQL anonymous block in step (3,
The running result (enable set serveroutput on) is as follows:Local: # of rows is 1-> the subroutine displays a record of the primary anonymous block uncommited. Main: # Of rows is 2-> the primary anonymous block shows the uncommited records inserted by the primary anonymous block and subprograms. Local: # Of rows is 0-> Primary anonymous block: rollbacklocal: # Of rows is 2-> the primary anonymous block successfully processes all records inserted by the primary anonymous block and subroutine commitmain: # of rows is 3 PL/SQL. L
Autonomous transaction (
Autonomous transactions)Autonomous_transaction refers to the autonomous management of transactions in subprograms such as function and procedure, when calling these subprograms in other PL/SQL blocks, these subprograms do not roll back with the failure of the parent PL/SQL block, but manage their own commit. Autonomous transactions are often used to write log or TRAC information for error locating.
Autonomous transactions(Hereinafter referred to as at) is a transaction called but independent of Mt by the main transaction (hereinafter referred to as Mt. In
Autonomous transactionsWhen the call is executed, the MT is suspended.
Autonomous transactionsInternally, a series of DML can be executed and commit or rollback. Autonomous transactions prevent nested commit, so that the transaction commit or rollback in its own transaction zone will not affect other transactions. Because
Autonomous transactionsIts commit and rollback do not affect the execution of Mt. In
Autonomous transactionsAfter the execution is complete, the master transaction obtains control and can continue execution. Implementation
Autonomous transactionsOnly the following PL/SQL statement must be added.
Pragma autonomous_transactionYou can. 1. Top-level anonymous PL/SQL Block 2. functions or procedure3. trigger. For example, declare in an independent procedure
Autonomous transactions: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;
Now we can rewrite procedure local with an autonomous transaction:Create or replace procedure local is CNT number: =-1; -- global variables
Pragma autonomous_transaction;Begin select count (*) into CNT from MSG; dbms_output.put_line ('local: # of rows is '| CNT); insert into MSG values ('new record ');
Commit;End;
Run the PL/SQL anonymous block in step (3,
The running result (enable set serveroutput on) is as follows:Local: # of rows is 0-> the uncommitted record in the primary anonymous block cannot be seen in the local sub-transaction (because it is independent) Main: # Of rows is 2-> the primary anonymous block can view '2 records, but only one is committed. local: # of rows is 1-> in the local sub-transaction, you can see the record of 'its previous commit, but the record in the primary anonymous block has been rolled back in advance. Local: # Of rows is 3-> in the sub-transaction local, you can see that 'three records include the main anonymous block commit record main: # Of rows is 4-> all records are displayed at the end of the primary anonymous block. the PL/SQL process is successfully completed. L
Conclusion:In this example, the position of commit and rollback in the primary anonymous block or in the subroutine will affect the entire transaction. if a subprogram contains a transaction or a commit statement, it cannot be rolled back. Otherwise, it can be rolled back because the transaction only rolls back all the transactions after the previous commit or rollback. When a self-made transaction is used, at is independent and the MT is paused when it is executed. at commit and rollback do not affect Mt execution. when using at, there are some precautions: 1. in anonymous PL/SQL blocks, only top-level anonymous PL/SQL blocks can be set as at2. package cannot be declared as, only functions and procedure owned by the package can be declared as at3. at programs must end with commit or rollback, otherwise Oracle error ORA-06519: Active autonomous transaction detected and rolled back will occur
