Plsql_ autonomous transactions and nesting understanding and usage (case)

2014-06-01 Baoxinjian in Capgemini

I. Summary

Nested things: Refers to one or more sub Transaction nested within a parent transaction. And the primary transaction interacts with each other, and this transaction is called a nested transaction. With commit as the end of the transaction

Autonomy: Refers to the autonomous management of transactions in subprograms such as function,procedure, and when these subprograms are called in other PL/SQL blocks, these subprograms do not follow the parent PL/SQL The block failed and rolled back, but managed to commit itself. With commit as the end of the transaction. Autonomous transactions are often used to write log or TRAC information to facilitate the lookup of errors.

Personal feeling, generally nested things because the use of savepoint&rollback this way to rollback, will undermine the readability of the program and modularity, especially a number of savepoint&rollback in the program, may even write their own people will be confused, Not to mention the maintenance personnel in the project operations process, so generally in the writing standard, will prohibit the use of nested things

Autonomous things can be modular, generally recommended writing, the main things and sub-things to isolate, so the back will also do a detailed interpretation of self-government things

Second, nested things savepoint

1. Basic role: To establish a rollback node, and then roll back the node directly, this node will still commit

2. Basic syntax

Save_point My_savepoint;
... ...
ROLLBACK to My_savepoint;

3. Because it is not recommended in the development process, so the case is omitted;)

Iii. autonomous_transation of self-government affairs

1. Basic role:

After Proc1 calls PROC2, Proc1 executes rollback, PROC2 will still commit;

The basic grammar used is to add pragma autonomous_transaction when affirming proc2;

2. Basic syntax

Affirming that PROC2 is an autonomous business

1Create or Replace procedureproc223 aspragma autonomous_transaction;45begin67 Insert  intoLog_infoValues(User, Sysdate,'Insert');89 Commit;Ten  OneEnd;

3. An example of an autonomous business

3.1 Affirming an autonomous thing Xxautonomouse_insert

1  procedureXxautonomouse_insert is2 3 pragma autonomous_transaction;4 5  begin6 7   Insert  intoxxap_viktor_autonomous8 9        Values(001,'xxap_invoice_20140410_01');Ten  One   Commit;--commit in an autonomous transaction A  -  End;

3.2 Affirm a Master thing, invoke the self-rule in the main thing,

1 procedureXxautonomouse_main is2 3  begin4 5 Xxautonomouse_insert;6 7   Insert  intoxxap_viktor_autonomous8 9         Values(002,'xxap_invoice_20140410_02');Ten  One   rollback;--rollback in the master transaction A  -  End;

3.3 The rollback in the main thing does not affect the commit and rollback of the autonomous affairs, and realizes the isolation between things.

4. The concept of autonomous affairs

(1) An autonomous transaction with an alter session should share a session with the primary transaction, so any modification to that session through the ALTER SESSION statement should be visible to both the autonomous transaction and the primary transaction. But an autonomous transaction executes in a different context than the primary transaction. Any autonomous subroutine calls raised from an autonomous block share the same transaction context as the autonomous transaction

(2) Autonomous transactions and deadlocks must define autonomous transactions in this way: deadlocks occur when an autonomous transaction attempts to access a resource that is consumed by the primary transaction, and the primary transaction hangs until the self-service ends. Autonomous transaction death the primary transaction frees resources, which can result in deadlocks

(3) The criteria for defining an autonomous transaction are only the top-level anonymous block to include PRAGMA autonomous_transtraction (4) Commit or ROLLBACK behavior with the self-governing transaction ending with a commit or ROLLBACK statement. Therefore, you should explicitly include a commit or ROLLBACK statement inside the self-service program. If this is not done, any undefined 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 unusual manner, a transaction-level rollback occurs, and all indeterminate changes in the autonomous transaction are rolled back

(6) Multiple autonomous transactions are initialized in the context of an autonomous transaction, which initializes multiple autonomous transactions. You can define multiple commit or ROLLBACK statements within an autonomous block to accomplish this task, and when a rollback is included, it belongs to the current transaction and not to the primary transaction

(7) Concurrency problem for autonomous transactions the autonomous transaction is run concurrently with the primary transaction, and the transactions parameter in the initialization file Init.ora determines the number of concurrent transactions in each session

Reference:lxzo123 http://blog.csdn.net/lxzo123/article/details/5942003

Reference: Hwhuang http://zohan.group.iteye.com/group/wiki/2297-oracle-transaction

Thanks and regards