Oracle與SQL自治事務

標籤：

自治事務

自治事務是獨立的事務操作，如果考慮到交易回復，必須單獨寫成一個觸發器來完成，

一個事務A在另一個事務B內被調用，那個事務A是自治事務，自治事務A執行過程中會脫離其session內未執行完畢的事務的影響。

如果session從B事務開始——A事務開始和結束——B事務結束

上述的A事務不受沒有完成的B事務的影響，然後A事務執行完畢後再次回到B事務執行沒有完成的B事務。

通過pragma autonomous_transaction將一個pl/sql程式結構設定為自治事務，pragma是編譯器指令，

1、自治事務使用方式

無法復原的審計 ： 一般情況下利用觸發器禁止某些對錶的更新等操作時，若記錄日誌，則觸發器最後拋出異常時會造成日誌復原。利用自治事務可防止此點。
避免變異表： 即在觸發器中操作觸發此觸發器的表
在觸發器中使用ddl 寫資料庫：對資料庫有寫操作（insert、update、delete、create、alter、commit）的預存程序或函數是無法簡單的用sql來調用的，此時可以將其設為自治事務，從而避免ora-14552（無法在一個查詢或dml中執行ddl、commit、rollback）、ora-14551（無法在一個查詢中執行dml操作）等錯誤。需要注意的是函數必須有傳回值，但僅有in參數（不能有out或in/out參數）。 
開發更模組化的代碼： 在大型開發中，自治事務可以將代碼更加模組化，失敗或成功時不會影響調用者的其它操作，代價是調用者失去了對此模組的控制，並且模組內部無法引用調用者未提交的資料。

2、Oracle 自製事務是指的預存程序和函數可以自己處理內部事務不受外部事務的影響，用pragma autonomous_transaction來聲明，要建立一個自治事務，您必須在匿名塊的最高層或者預存程序、函數、資料包或觸發的定義部分中，使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION語句。在這樣的模組或過程中執行的SQL語句都是自治的。

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 when e_avg = 1 then 1 else 2 end) where entityindex = :new.entityindex;

COMMIT;
END;

create or replace procedure AutoNomouse_Insert is PRAGMA AUTONOMOUS_TRANSACTION; 

begin 

insert into Msg values(‘AutoNomouse Insert‘);   

commit;  

 end;

3、自治事務可以嵌套，嵌套深度等只受init.ora參數transactions（同時並發的事務數，預設為sessions的1.1倍）制約。因為自治事務是與主事務(簡稱MT)相分離的，所以它不能檢測到被修改過的行的目前狀態。這就好像在主事務提交之前，它們一直處於單獨的會話裡，對自治事務來說，它們是停用。然而，反過來情況就不同了：主事務能夠檢測到已經執行過的自治事務的結果。

4、如果AT試圖訪問被MT控制的資源,可能有deadlock發生.Package 不能被聲明為AT,只有package所擁有的function和procedure 才能聲明為AT。主事務與自治事務是完全不同的事務，因此無法共用鎖定等。結束一個自治事務必須提交一個commit、rollback或執行ddl，否則會產生Oracle錯誤ORA-06519: active autonomous transaction detected and rolled back 。儲存點無法在自治事務中復原到父事務中的一個儲存點，只能在內部使用儲存點。

5、可能遇到的錯誤
ora-06519 – 檢查到活動自治事務，復原——退出自治事務時沒有提交、復原或ddl操作
ora-14450 – 試圖訪問正在使用的事務級暫存資料表
ora-00060 – 等待資源時檢查到死結

 SQL

USE [TABLE]GOEXEC sp_addlinkedserver @server = N‘loopback‘ , @srvproduct = N‘ ‘ , @provider = N‘SQLNCLI‘ , @datasrc = @@SERVERNAMEEXEC sp_serveroption loopback, N‘rpc out‘ , ‘TRUE‘EXEC sp_serveroption loopback, N‘remote proc transaction promotion‘ , ‘FALSE‘GOBEGINIF (object_id(‘TR_UPDT_TEST_Pro‘, ‘P‘) is not null)     drop proc TR_UPDT_TEST_Pro;ENDGOCREATE proc TR_UPDT_TEST_Pro(@entityindex varchar(50),@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 when @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(32),@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;

　以上指令碼功能相同

參考連結：http://www.cnblogs.com/Ronger/archive/2012/02/15/2352527.html(摘抄)

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

Oracle與SQL自治事務