[转]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 自己提交自己的 , 而不受外部影响也不影响外部 .