Oracle and SQL Autonomous transactions

Autonomous transactions

Autonomous transactions are independent transactional operations, and if a transaction rollback is considered, a single trigger must be written to complete the

A transaction A is called within another transaction B, and that transaction A is an autonomous transaction, which is removed from the execution of a transaction that is not completed within its session.

If the session starts with a B transaction--a transaction start and end--b transaction end

The a transaction above is not affected by the unfinished B transaction, and then the a transaction is completed and then returned to the B transaction to execute the unfinished B transaction.

A PL/SQL program structure is set as an autonomous transaction through pragma autonomous_transaction, pragma is a compiler directive,

1. Use of self-government affairs

Auditing that cannot be rolled back: In general, when a trigger is used to prohibit certain operations such as updating a table, the log rollback is caused by a trigger that throws an exception when it is logged. This point can be prevented by using autonomous transactions.
Avoid variation tables: The table that triggers this trigger in the trigger
Using DDL in triggers to write a database: a stored procedure or function that writes to a database (INSERT, UPDATE, DELETE, create, alter, commit) cannot be simply invoked with SQL, and can be set as an autonomous transaction. This avoids errors such as ora-14552 (inability to execute DDL, commit, rollback) in a query or DML, ora-14551 (cannot perform DML operations in a query), and so on. It is important to note that the function must have a return value, but only the in parameter (cannot have an out or in/out parameter).
Develop more modular code: in large-scale development, autonomous transactions can make the code more modular, failing or succeeding without affecting the caller's other operations, at the expense of the caller losing control of the module, and the inside of the module cannot reference the caller uncommitted data.

2, Oracle self-made transactions refers to the stored procedures and functions can handle the internal transaction is not affected by external transactions, with pragma autonomous_transaction to declare that to create an autonomous transaction, you must be at the highest level of anonymous block or stored procedures, functions, In the definition section of the packet or trigger, use the pragma autonomous_transaction statement in PL/SQL. The SQL statements executed in such a module or procedure are autonomous.

Create or Replace Trigger Tr_updt_trigger
Before UPDATE of Currentstatus on m_table
For each ROW
DECLARE
E_count INTEGER;
E_sum number;
E_AVG number;
PRAGMA autonomous_transaction;
BEGIN
Select AVG (currentstatus) into E_avg
From m_businessentityextend where entityindex =: new.entityindex;
Select SUM (currentstatus) into e_sum
From m_businessentityextend where entityindex =: new.entityindex;
Select COUNT (currentstatus) into E_count
From m_businessentityextend where entityindex =: new.entityindex;

E_avg: = (E_sum-: Old.currentstatus +: new.currentstatus)/e_count;
Update m_businessentity Set entitystatus = (case if E_avg = 1 then 1 else 2 end) where Entityindex =: new.entityindex;

COMMIT;
END;

INSERT into MSG values (' Autonomouse Insert ');   

Commit  

End

3, autonomous transactions can be nested, nesting depth, and so only by the Init.ora parameter transactions (concurrent transaction number, the default is Sessions 1.1 times times) constraints. Because an autonomous transaction is separate from the main transaction (MT), it cannot detect the current state of the modified row. It is as if they were in separate sessions until the main transaction was committed, and they were not available for autonomous transactions. However, the reverse situation is different: the primary transaction can detect the results of an autonomous transaction that has already been performed.

4. If at attempts to access resources that are controlled by MT, deadlock may occur. The package cannot be declared as at, only the function and procedure owned by the package can be declared as at. A master transaction is a completely different transaction from an autonomous transaction, so a lock cannot be shared. End an autonomous transaction must commit a commit, rollback, or execute DDL, otherwise it will produce Oracle error Ora-06519:active autonomous transaction detected and rolled back. The savepoint cannot be rolled back to a savepoint in the parent transaction in an autonomous transaction, and the savepoint can only be used internally.

5. Errors that may be encountered
ora-06519– Check to active self-service, rollback-no commit, rollback, or DDL operation when exiting an autonomous transaction
ora-14450– attempting to access a transaction-level temporary table in use
ora-00060– when waiting for resources to check for deadlocks

Sql

Use [table]goexec sp_addlinkedserver @server = n ' loopback ', @srvproduct = N ', @provider = N ' sqlncli ', @datasrc = @ @SE Rvernameexec sp_serveroption loopback, n ' rpc out ', ' TRUE ' EXEC sp_serveroption loopback, n ' Remote proc transaction promoti On ', ' FALSE ' gobeginif (object_id (' Tr_updt_test_pro ', ' P ') are NOT null) drop proc Tr_updt_test_pro; endgocreate proc Tr_updt_test_pro (@entityindex varchar (), @newcurrentstatus int, @oldnewcurrentstatus int)     Asbegindeclare @e_count int, @e_sum numeric, @e_avg numeric;     Select @e_avg =avg (currentstatus) from m_businessentityextend where entityindex = @entityindex;     Select @e_sum =sum (currentstatus) from m_businessentityextend where entityindex = @entityindex;     Select @e_count =count (currentstatus) from m_businessentityextend where entityindex = @entityindex;    SET @e_avg = (@e_sum-@oldnewcurrentstatus + @oldnewcurrentstatus)/@e_count; Update m_businessentity Set entitystatus = (case if @e_avg = 1 then 1 else 2 end) where Entityindex [email protected]; END; Gobeginif (object_id (' Tr_updt_trigger ', ' TR ') is not null) DROP TRIGGER tr_updt_triggerend; Gocreate TRIGGER Tr_updt_triggeron M_tableinstead of updateasif Update (currentstatus) begindeclare @entityindex varchar     (+), @newcurrentstatus int, @oldnewcurrentstatus int; Select @entityindex =inserted.entityindex, @newcurrentstatus =inserted.currentstatus from Inserted;     Select @oldnewcurrentstatus =deleted.currentstatus from deleted; EXEC loopback. OSMP. Dbo.tr_updt_test_pro @entityindex, @newcurrentstatus, @oldnewcurrentstatus; END;

The above script features are the same

Reference Link: http://www.cnblogs.com/Ronger/archive/2012/02/15/2352527.html (excerpt)

http://blog.csdn.net/xman_78tom/article/details/5909124 (imitation)

Oracle and SQL Autonomous transactions