本文轉自：http://blog.sina.com.cn/s/blog_66f845010100qelf.html
一, Transaction control 默認Transaction 由修改數據開始(獲得TX LOCK), 手工也可以用set transaction或DBMS_TRANSACTION來控制, 由COMMIT,ROLLBACK結束(ROLLBACK TO SAVEPOINT並不會結束一個TRANSACTION). TRANSACTION語句包含以下COMMIT, ROLLBACK, SAVEPOINT ,ROLLBACK TO SAVEPOINT, SET TRANSACTION(設置TRANSACTION相關特性) 自動控制 Statement-Level Atomicity create table t ( x int check ( x>0 ) ); Insert into t values ( 1 ); Insert into t values ( -1 ); 的TRANSACTION CONTROL如下所示 Savepoint statement1; Insert into t values ( 1 ); If error then rollback to statement1; Savepoint statement2; Insert into t values ( -1 ); If error then rollback to statement2; 在本例中T中有1而無-1 Procedure-Level Atomicity 作一個名為P的PROCEDURE，裡面有兩個插入語句 create or replace procedure p 2 as 3 begin 4 insert into t values ( 1 ); 5 insert into t values (-1 ); 6 end; 然後調用此PROCEDURE P begin 2 p; 3 end; 相當於 begin 2 savepoint sp; 3 p; 4 exception 5 when others then 6 rollback to sp; 7 end; 也就是說兩個INSERT一起成功或失敗，本例中T表內沒有被插入數據。但是，如果我們加上exception則結果大不相同。 begin 2 p; 3 exception 4 when others then null; 5 end; 效果會和Statement-Level Atomicity例子的結果一樣，T表中有1，而-1插入失敗。 二, 錯誤的TRANSACTION的習慣 首先, TRANSACTION要盡量短，因為LOCK，BLOCK， DATA是非常耗資源的。其次為了實現讓TRANSACTION盡量短而設置循環中定時提交是錯誤的. 大家肯定都有過類似的經驗，就是在PROCEDURE作一個大的LOOP時，有人會告訴你要定期提交，比如1000行一提交，他們的根據是 但是，這樣做會導致你的數據進入一個無法控制的狀態，只有全部作為一個TRANSACTIOn提交或會滾才能保證一致性，分為小的TRANSACTIOn後的後果就是可能造成一部份提交，一部份回滾，這樣你就需要另外復雜的手段，比如記錄發生錯誤的點，以便下次繼續。因此，建議不要用ROWNUM去判斷提交的點，而要用商業規則去判斷，比如根據性別，或省市等信息。 他們的第一個觀點是錯誤的，相同的任務，放在一個TRANSACTIOn中要比分開來運行要快很多（在不考慮其他影響，比如BLOCK）。 SQL> create table twwm as select * from all_objects; 表已創建。 SQL> create table twwm2 as select * from twwm; 表已創建。 SQL> update twwm2 set object_name=lower(object_name); 已更新29128行。 已用時間: 00: 00: 01.09 begin for x in ( select rowid rid, object_name, rownum r from TWWM ) loop update TWWM set object_name = lower(x.object_name) where rowid = x.rid; if ( mod(x.r,100) = 0 ) then commit; end if; end loop; commit; end; PL/SQL 過程已成功完成。 已用時間: 00: 00: 06.03 他們第二個觀點也是錯誤的，因為在一個TRANSACTION中多次COMMIT會導致UNDO可能被重用，而這樣的結果就是可能會發生ORA-01555: snapshot too old。會影響本身的應用。 SQL> create table twwm as select * from all_objects; 表已創建。 SQL> create index i_wwm on twwm(object_name); 索引已創建。 SQL> exec dbms_stats.gather_table_stats('SYS','TWWM',cascade=>true); PL/SQL 過程已成功完成。 然後為了試驗，設置一個小的UNDO TABLESPACE,非自動擴展 SQL> create undo tablespace undo_small datafile 'D:\ORACLE\ORADATA\SBTTEST\UNDO0 2.DBF' size 2M autoextend off 2 / 表空間已創建。 然後設置默認UNDO TABLESPACE為此UNDO_SMALL. SQL> alter system set undo_tablespace=undo_small; 系統已更改。 然後運行一個批量修改的PL/SQL 塊. begin for x in ( select rowid rid, object_name, rownum r from TWWM where object_name > ' ' ) loop update TWWM set object_name = lower(x.object_name) where rowid = x.rid; if ( mod(x.r,100) = 0 ) then commit; end if; end loop; commit; end; begin * ERROR 位於第 1 行: ORA-01555: 快照過舊: 回退段號 11 在名稱為 "_SYSSMU11$" 過小 ORA-06512: 在line 2 當然,如果我們不COMMIT,那麼可能會導致 1 begin 2 for x in ( select rowid rid, object_name, rownumr 3 from TWWM 4 where object_name > ' ' ) 5 loop 6 update TWWM 7 set object_name = lower(x.object_name) 8 where rowid = x.rid; 9 end loop; 10 commit; 11* end; 12 / begin * ERROR 位於第 1 行: ORA-30036: 無法按 8 擴展段 (在撤消表空間 'UNDO_SMALL' 中) ORA-06512: 在line 6 但是, ORA-30036明顯比ORA-01555更容易接受,首先前面說過了, ORA-01555會導致數據一致性不可控制,並且ORA-01555是很難避免的,但是ORA-30036卻是可以解決的.所以, 多次COMMIT 並不會節省UNDO(表面的節省是以失去數據為代價的, 同時,這個例子也證明在單用戶系統中也會發生ORA-01555). 三, Distributed Transactions 我們在一個TRANSACTION裡可以連接多個DATABASE, 進行操作,一起提交或回滾. 連接多個數據庫一般通過DB LINK ,DB_LINK不能運行DDL,DCL. 四, 自治 Transactions 自治TRANSACTION是TRANSACTION中的TRANSACTION,他的任何操作不影響外部TRANSACTION.做兩個PROCEDURE測試下 1 create or replace procedure Autonomous_Insert 2 as 3 pragma autonomous_transaction; 4 begin 5 insert into t values ( 'Autonomous Insert' ); 6 commit; 7* end; 8 / 過程已創建。 這裡的PRAGMA是編譯指示,告訴ORACLE 按什麼去編譯. 再建普通PROCEDURE create or replace procedure NonAutonomous_Insert as begin insert into t values ( 'NonAutonomous Insert' ); commit; end; 先運行NonAutonomous begin insert into t values ( 'Anonymous Block' ); NonAutonomous_Insert; rollback; end; PL/SQL 過程已成功完成。 SQL> select * from t; X -------------------- Anonymous Block NonAutonomous Insert 可以看到NonAutonomous_Insert中的COMMIT完成了提交任務,所以外部的ROLLBACK沒起作用. 清除數據再用Autonomous SQL> set timing off SQL> delete from t; 已刪除2行。 SQL> commit; 提交完成。 SQL> begin 2 insert into t values ( 'Anonymous Block' ); 3 Autonomous_Insert; 4 rollback; 5 end; 6 / PL/SQL 過程已成功完成。 SQL> select * from t; X -------------------- Autonomous Insert 看到autonomous transaction procedure的COMMIT並不影響外圍的TRANSACTION. autonomous transaction會用在什麼地方呢? 類似SELECT SEQ.NEXTVAL FROM DUAL這樣的TRANSACTION會用到autonomous transactions,當發出這樣的查詢後, 內TRANSACTION會讀並修改SYS.SEQ$, 並自行提交或回滾而不受外部TRANSACTION的影響,這也就是為什麼NEXTVAL不能回滾的原因. 還有很多朋友會考慮, 記錄下用戶的操作(比如對某個重要的表的UPDATE), 一般會考慮用TRIGGER 解決,但是, 如果UPDATE本身失敗了, 那麼TRIGGER 就不會記錄下操作, 而是隨UPDATE的失敗一塊回滾. 這個時候也需要考慮用autonomous transaction. (審計功能也是這原理) create table audit_tab 2 ( username varchar2(30) default user, 3 timestamp date default sysdate, 4 msg varchar2(4000) 5 ) create or replace trigger EMP_AUDIT 2 before update on emp 3 for each row 4 declare 5 pragma autonomous_transaction; 6 l_cnt number; 7 begin 8 9 select count(*) into l_cnt 10 from dual 11 where EXISTS ( select null 12 from emp 13 where empno = :new.empno 14 start with mgr = ( select empno 15 from emp 16 where ename = USER ) 17 connect by prior empno = mgr ); 18 if ( l_cnt = 0 ) 19 then 20 insert into audit_tab ( msg ) 21 values ( 'Attempt to update ' || :new.empno ); 22 commit; 23 24 raise_application_error( -20001, 'Access Denied' ); 25 end if; 26 end; TRIGGER自己提交自己的,而不受外部影響也不影響外部.
