[转]ORACLE DBA TRANSACTIONS

本文转自：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行一提交，他们的根据是

1. 把大的TRANSACTIOn变成小的TRANSACTION效率更高，
   
2. 而且会减少UNDO的使用，因而很大程度提高速度。
   

但是，这样做会导致你的数据进入一个无法控制的状态，只有全部作为一个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自己提交自己的,而不受外部影响也不影响外部.

 

原标题：[转]ORACLE DBA TRANSACTIONS

关键词：oracle