Thoughts on Oracle nested transactions and autonomous transactions

Source from: http://hwhuang.javaeye.com/blog/650903

Keywords
Concepts of nested transactions and autonomous transactions
Use of nested transactions
Use of autonomous transactions
1. Concepts
1. nested transaction ):
It refers to one or more sub transactions nested in a parent transaction, and the primary transaction and its Mutual influence. Such transactions are called nested transactions. Use commit as the end of the transaction.
2. autonomous transaction ):
It 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. Use commit as the end of the transaction. Autonomous transactions are often used to write log or TRAC information for error locating.
Ii. Use of nested transactions (nested transaction)
1. Prepare create table:

SQL code

1. Create Table test_policy
2. (
3. Policy_code varchar2 (20 ),
4. Policy_type char (1)
5. )

create table TEST_POLICY(  POLICY_CODE VARCHAR2(20),  POLICY_TYPE CHAR(1))

2. Create procedure for a nested transaction:

1)

SQL code

1. Procedure p_insert_policy (I _policy_code varchar2 (20 ),
2. I _policy_type char (1)
3. CNT number: = 0;
4. Begin
5. Select count (1) into CNT from test_policy;
6. Dbms_output.put_line ('records of the test_policy is '| CNT );
8. Insert into test_policy values (I _policy_code, I _policy_type );
9. Commit; -- commit in nested transaction
10. End p_insert_policy;
11. -- Call procedure used in nested transaction
12. Procedure test_pl_ SQL _entry (
13. I _pol_id in varchar2,
14. O_succ_flg out varchar2)
15. Strsql varchar2 (500 );
16. CNT number: = 0;
17. Begin
18. Delete from test_policy;
19. Commit;
20. Insert into test_policy values ('20140901', '1 ');
21. Select count (1) into CNT from test_policy;
22. Dbms_output.put_line ('records of the test_policy is '| CNT );
23. -- Call nested transaction
24. P_insert_policy ('20140901', '2 ');
25. Rollback; -- rollback data for all transactions
26. Commit; -- master transaction commit
27. Select count (1) into CNT from test_policy;
28. Dbms_output.put_line ('records of the test_policy is '| CNT );
29. Rollback;
31. Select count (1) into CNT from test_policy;
32. Dbms_output.put_line ('records of the test_policy is '| CNT );
34. End test_pl_ SQL _entry;
36. => Run pl/SQL:
37. Records of the test_policy is 1 -- the operation in the master transaction has been commit
38. Records of the test_policy is 1 -- the operation of the primary transaction has an impact on the nested transaction.
39. Records of the test_policy is 2 -- the nested transaction has been commit
40. Records of the test_policy is 2 -- nested transaction has an impact on the main task.

Procedure p_insert_policy (I _policy_code varchar2 (20), I _policy_type char (1) as CNT number: = 0; begin select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is '| CNT); insert into test_policy values (I _policy_code, I _policy_type); Commit; -- commit in nested transaction end p_insert_policy; -- call procedure used in nested transaction procedure test_pl_ SQL _entry (I _ Pol_id in varchar2, o_succ_flg out varchar2) as strsql varchar2 (500); CNT number: = 0; begin Delete from test_policy; commit; insert into test_policy values ('123 ', '1'); select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is '| CNT); -- call nested transaction p_insert_policy ('200 ', '2'); rollback; -- rollback data for all transactions commit ;-- Master transaction commit select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is '| CNT); rollback; select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is '| CNT); End test_pl_ SQL _entry; => run pl/SQL: records of the test_policy is 1 -- the operation in the primary transaction has been committed to the test_policy is 1 -- the operation of the primary transaction has an impact on the nested transaction. Records of the test_policy is 2 -- the nested transaction has commitrecords of the test_policy is 2 -- the nested transaction has an impact on the main task.

Modify the procedure of the nested transaction above without the need for commit:

SQL code

1. Procedure p_insert_policy (I _policy_code t_contract_master.policy_code % type,
2. I _policy_type t_contract_master.policy_type % Type)
3. CNT number: = 0;
4. Begin
5. Select count (1) into CNT from test_policy;
6. Dbms_output.put_line ('records of the test_policy is '| CNT );
8. Insert into test_policy values (I _policy_code, I _policy_type );
9. -- Commit;
10. End p_insert_policy;
11. Procedure test_pl_ SQL _entry (
12. I _pol_id in varchar2,
13. O_succ_flg out varchar2)
14. Strsql varchar2 (500 );
15. CNT number: = 0;
16. Begin
17. Delete from test_policy;
18. Commit;
19. Insert into test_policy values ('20140901', '1 ');
20. Select count (1) into CNT from test_policy;
21. Dbms_output.put_line ('records of the test_policy is '| CNT );
23. P_insert_policy ('20140901', '2 ');
24. Rollback;
25. Commit;
26. Select count (1) into CNT from test_policy;
27. Dbms_output.put_line ('records of the test_policy is '| CNT );
28. Rollback;
30. Select count (1) into CNT from test_policy;
31. Dbms_output.put_line ('records of the test_policy is '| CNT );
33. End test_pl_ SQL _entry;
34. Run PL/SQL =>
35. The result is:
36. Records of the test_policy is 1 -- the operation in the master transaction has been commit
37. Records of the test_policy is 1 -- the operation of the primary transaction has an impact on the nested transaction.
38. Records of the test_policy is 0 -- the data of the nested transaction is rollback by the main transaction.
39. Records of the test_policy is 0

Procedure p_insert_policy (I _policy_code t_contract_master.policy_code % type, I _policy_type t_contract_master.policy_type % Type) as CNT number: = 0; begin select count (1) into CNT from test_policy; values ('records of the test_policy is '| CNT); insert into test_policy values (I _policy_code, I _policy_type); -- commit; end p_insert_policy; Procedure values (I _pol_id in varchar2, O _ Succ_flg out varchar2) as strsql varchar2 (500); CNT number: = 0; begin Delete from test_policy; commit; insert into test_policy values ('20140901', '1 '); select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is '| CNT); p_insert_policy ('20140901', '2'); rollback; commit; select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test _ Policy is '| CNT); rollback; select count (1) into CNT from test_policy; dbms_output.put_line ('records of the test_policy is' | CNT); End test_pl_ SQL _entry; run PL/SQL => result: Records of the test_policy is 1 -- the operation in the main transaction has commitrecords OF THE test_policy is 1 -- the operation in the main transaction has an impact on the nested transaction. Records of the test_policy is 0 -- the data of the nested transaction is rollback. Records of the test_policy is 0

Iii. autonomous transaction)

1. The following is a description of the autonomous transaction on Oracle:

Autonomous transactions does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.

Autonomous transactions 'committed changes are visible to other transactions immediately. (A nested transaction's committed changes are not visible to other transactions until the main transaction commits .)

An autonomous transaction (hereinafter referred to as at) is a transaction called but independent of Mt by the main transaction (hereinafter referred to as Mt. When an autonomous transaction is called and executed, MT is suspended. Within the autonomous transaction, a series of DML can be executed and commit or rollback. autonomous transactions prevent nested commit, so that transactions are committed or rolled back within their own transaction zone without affecting other transactions. Because of the independence of the autonomous transaction, its commit and rollback do not affect the execution of Mt. After the execution of the autonomous transaction is completed, the master transaction obtains control and can continue to be executed.

To implement the definition of autonomous transactions, you only need to add Pragma autonomous_transaction to the following pl/SQL statement.

1). Top-level anonymous PL/SQL Blocks

2). functions or procedure.

2. Define an autonomous transaction:

SQL code

1. Procedure p_insert_policy_new (I _policy_code varchar2 (20 ),
2. I _policy_type char (1)
3. Pragma autonomous_transaction; -- Define auto Trans
4. CNT number: = 0;
5. Begin
6. Select count (1) into CNT from test_policy;
7. Dbms_output.put_line ('records of the test policy table is: '| CNT );
9. Insert into test_policy values (I _policy_code, I _policy_type );
10. Commit;
11. Select count (1) into CNT from test_policy;
12. Dbms_output.put_line ('records of the test policy table is: '| CNT );
13. End p_insert_policy_new;
14. -- Call auto trans procedure
15. Procedure test_pl_ SQL _entry (
16. I _pol_id in varchar2,
17. O_succ_flg out varchar2)
18. Strsql varchar2 (500 );
19. CNT number: = 0;
20. V_policycode t_contract_master.policy_code % type;
21. Begin
22. Delete from test_policy;
23. Commit;
24. Insert into test_policy values ('20140901', '1 ');
25. Select count (1) into CNT from test_policy;
26. Dbms_output.put_line ('records of the test_policy is '| CNT );
28. P_insert_policy_new ('20140901', '2 ');
29. Select count (1) into CNT from test_policy;
30. Dbms_output.put_line ('records of the test_policy is '| CNT );
31. Rollback;
32. Select policy_code into v_policycode from test_policy;
33. Dbms_output.put_line ('policy _ code: '| v_policycode );
34. Commit;
35. Select count (1) into CNT from test_policy;
36. Dbms_output.put_line ('records of the test_policy is '| CNT );
37. Rollback;
39. Select count (1) into CNT from test_policy;
40. Dbms_output.put_line ('records of the test_policy is '| CNT );
42. End test_pl_ SQL _entry;
43. Run PL/SQL =>
44. Records of the test_policy is 1 -- master trans has been committed.
45. Records of the test policy table is: 0 -- Auto trans isn't affected by Master trans.
46. Records of the test policy table is: 1 -- Auto trans has been committed.
47. Records of the test_policy is 2
48. Policy_code: 2010042102 -- rollback affected master Trans
49. Records of the test_policy is 1
50. Records of the test_policy is 1

Procedure p_insert_policy_new(i_policy_code Varchar2(20),                                i_policy_type char(1)) as  Pragma Autonomous_Transaction;--define auto trans  cnt number := 0;  begin      select count(1) into cnt from test_policy;      Dbms_Output.put_line('records of the test policy table is: '||cnt);                Insert into Test_Policy values(I_Policy_code, I_Policy_type);                                commit;      select count(1) into cnt from test_policy;      Dbms_Output.put_line('records of the test policy table is: '||cnt);   end p_insert_policy_new;--call auto trans procedurePROCEDURE TEST_PL_SQL_ENTRY(                              I_POL_ID IN VARCHAR2,                              O_SUCC_FLG OUT VARCHAR2) AS  strSql varchar2(500);  cnt number := 0;  v_policyCode t_contract_master.policy_code%type;  BEGIN     delete from test_policy;     commit;     insert into test_policy values('2010042101', '1');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);          p_insert_policy_new('2010042102', '2');     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;     select policy_code into v_policyCode from test_policy;     Dbms_Output.put_line('policy_code: '|| v_policyCode);     commit;     select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);     rollback;          select count(1) into cnt from Test_Policy;     Dbms_Output.put_line('records of the test_policy is '|| cnt);       END TEST_PL_SQL_ENTRY;Run pl/sql=>records of the test_policy is 1 –-Master trans has been committed.records of the test policy table is: 0 -–Auto trans isn’t affected by master trans.records of the test policy table is: 1—-Auto trans has been committed.records of the test_policy is 2policy_code: 2010042102—-rollback affected master transrecords of the test_policy is 1 records of the test_policy is 1

3. Conclusion:

(1) The Pragma keyword is used to notify the PL/SQL Compiler to split the PL/SQL code block that declares it into an autonomous or independent transaction. When defining autonomous transactions, follow the following rules:

1) if the PL/SQL block is anonymous, the anonymous PL/SQL block must be a top-level block.

2) If the PL/SQL block is not anonymous, it must be a process or function that is part of the package or stored in a program unit. When an autonomous transaction is defined in a package, only the specific functions or processes in the package can be specified as autonomous transactions.

3) PL/SQL blocks can also be a method for storing object types.

4) PL/SQL block can also be a database trigger

(2) Some Key Issues in autonomous transactions

1) autonomous transactions with alter session

An autonomous transaction should share a session with the primary transaction. Therefore, any modifications to the session through the alter session statement should be visible to both the autonomous transaction and the primary transaction. However, the execution of autonomous transactions is different from that of the primary transaction. Any calls to autonomous subprograms caused by autonomous blocks share the same transaction context as autonomous transactions.

2) autonomous transactions and deadlocks

Autonomous transactions must be defined in this way:

A deadlock occurs when an autonomous transaction attempts to access the resources occupied by the primary transaction. The primary transaction suspends until the autonomous transaction ends. When the autonomous transaction crashes and other primary transactions release resources, the result may lead to a deadlock.

3) define conditions for autonomous transactions

Only top-level anonymous blocks can include Pragma autonomous_transtraction.

4) commit or rollback behavior and autonomous transactions

An autonomous transaction ends with a commit or rollback statement. Therefore, a commit or rollback statement should be explicitly included in the autonomous transaction program. If this is not done, any unconfirmed transaction will be rolled back. This is a transaction-level rollback, not a statement-level rollback.

5) exceptions and autonomous transactions

When an autonomous transaction exits in an abnormal way, a transaction-level rollback occurs, and all unconfirmed changes in the autonomous transaction will be rolled back.

6) Multiple autonomous transactions

Initializes multiple autonomous transactions in the context of an autonomous transaction. You can define multiple commit or rollback statements in an autonomous block to complete this task. When a rollback statement is included, it will belong to the current transaction rather than the main transaction.

7) concurrency of autonomous transactions

Autonomous transactions and Primary transactions run concurrently. The transactions parameter in the initialization file init. ora determines the number of concurrent transactions in each session.

8) Call User-defined functions through autonomous transactions and SQL

Through autonomous transactions, you can call user-defined functions from SQL to execute DML operations. You only need to define a user-defined function as an autonomous transaction, and the function can be called from SQL.

9) autonomous transactions and isolation levels

The commit or rollback command should be provided in the autonomous transaction. Once commit or rollback is executed within the autonomous transaction, those changes are visible to the primary transaction. However, Oracle allows you to set the isolation level of the master transaction to serializable, rather than the default read committed, to hide these changes to the master transaction, which can be completed through the set transction statement, the syntax is as follows:

SET transaction isolation level serializable;

Note the following two points about the isolation level:

A) when the autonomous transaction ends with commit or rollback, the changes caused by the autonomous transaction are visible to other transactions except the primary transaction.

B) set the isolation level to serializable to hide changes to autonomous transactions in the master transaction. Until the primary transaction is committed, once the primary transaction is committed, changes in the autonomous transaction are visible to the autonomous transaction.

10) isolation level

Isolation level is a method for processing and modifying database transactions. It affects the visibility of changes in one transaction to another. Sql92 defines four isolation levels:

SQL code

1. Read uncommitted
2. Repeatable read
3. Read commotted
4. Serializable

READ UNCOMMITTED  REPEATABLE READ  READ COMMOTTED  SERIALIZABLE 

Oracle supports read commotted and serializable isolation levels

Read committed: This is the default setting for Oracle. It enables Oracle queries to view the data that was nearly obtained before the query. In other words, transactions in this mode are based on the consistent transaction set of each sentence.

Serializable: This setting means that all statements in a transaction are in the database image at the beginning of a transaction operation. This means that no data submitted by other transactions can be seen in the current transaction before commit.