9i10g11g编程艺术——事务

1、在循环中提交

这样做的两个主要原因是：

频繁的提交大量小事务比处理和提交一个大事务更快，也更高效。

没有足够的undo空间。

a、性能影响

u1@ORCL> drop table t;

表已删除。

u1@ORCL> create table t as select * from all_objects;

表已创建。

u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL 过程已成功完成。

u1@ORCL> variable n number

u1@ORCL> exec :n := dbms_utility.get_cpu_time;

PL/SQL 过程已成功完成。

u1@ORCL> update t set object_name = lower(object_name);

已更新71069行。

u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n) || ' cpu hsecs...' );

78  cpu hsecs...

PL/SQL 过程已成功完成。

大多数人更喜欢像下面这样做：

u1@ORCL> drop table t;

表已删除。

u1@ORCL> create table t as select * from all_objects;

表已创建。

u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL 过程已成功完成。

u1@ORCL> exec :n := dbms_utility.get_cpu_time;

PL/SQL 过程已成功完成。

u1@ORCL> begin

  2     for x in ( select rowid rid, object_name, rownum r

  3                  from t )

  4     loop

  5          update t

  6             set object_name = lower(x.object_name)

  7           where rowid = x.rid;

  8          if ( mod(x.r,100) = 0 ) then

  9             commit;

 10          end if;

 11     end loop;

 12     commit;

 13  end;

 14  /

PL/SQL 过程已成功完成。

u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n) || ' cpu hsecs...' );

341  cpu hsecs...

PL/SQL 过程已成功完成。

对于这个小例子，倘若在循环中频繁的提交，就会慢上好几倍。

b、snapshot old错误

u1@ORCL> drop table t;

表已删除。

u1@ORCL> create table t as select * from all_objects;

表已创建。

u1@ORCL> create index t_idx on t(object_name);

索引已创建。

u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );

PL/SQL 过程已成功完成。

u1@ORCL> create undo tablespace undo_small datafile '+DG' size 10m reuse autoextend off;

表空间已创建。

u1@ORCL> alter system set undo_tablespace = undo_small;

系统已更改。

u1@ORCL> begin

  2      for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r

  3                   from t

  4                  where object_name > ' ' )

  5      loop

  6          update t

  7             set object_name = lower(x.object_name)

  8           where rowid = x.rid;

  9          if ( mod(x.r,100) = 0 ) then

 10            commit;

 11          end if;

 12     end loop;

 13     commit;

 14  end;

 15  /

begin

*

第 1 行出现错误:

ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小

ORA-06512: 在 line 2

这里的关键是，无法通过频繁提交来“节省”undo空间——你会需要这些undo信息。

2、自治事务

自治事务允许你创建一个“事务中的事务”，它能独立于其父事务提交或回滚。利用自治事务，可以挂起当前执行的事务，开始一个新事务，完成一些工作，然后提交或回滚，所有这些都不影响当前执行事务的状态。

u1@ORCL> create table t ( msg varchar2(25) );

表已创建。

u1@ORCL> 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  /

过程已创建。

u1@ORCL> create or replace procedure NonAutonomous_Insert

  2  as

  3  begin

  4          insert into t values ( 'NonAutonomous Insert' );

  5          commit;

  6  end;

  7  /

过程已创建。

注意这里使用了pragma autonomous_transaction。这个指令告诉数据库：执行这个过程时要作为一个新的自治事务来执行，而且独立于其父事务。下面来观察PL/SQL代码匿名块中非自治事务的行为：

u1@ORCL> begin

  2          insert into t values ( 'Anonymous Block' );

  3          NonAutonomous_Insert;

  4          rollback;

  5  end;

  6  /

PL/SQL 过程已成功完成。

u1@ORCL> select * from t;

MSG

-------------------------

Anonymous Block

NonAutonomous Insert

可以看到，匿名块执行的工作（insert）由NonAutonomous_Insert过程提交。两个数据行都已提交，所以rollback命令没有什么可以回滚。

u1@ORCL> delete from t;

已删除2行。

u1@ORCL> commit;

提交完成。

u1@ORCL> begin

  2          insert into t values ( 'Anonymous Block' );

  3          Autonomous_Insert;

  4          rollback;

  5  end;

  6  /

PL/SQL 过程已成功完成。

u1@ORCL> select * from t;

MSG

-------------------------

Autonomous Insert

在此，只有自治事务中完成并已提交的工作会持久保留。匿名块中完成的insert被rollback语句回滚。

总结一下，如果在一个“正常”的过程中commit，它不仅会持久保留自己的工作，也会使该会话中未完成的工作成为永久性的。不过，如果一个自治事务过程中完成commit，只会让这个过程本身的工作成为永久性的。

tom：“从我的经验看，自治事务唯一的合法使用，即记录错误日志或信息型消息，从而可以独立于父事务完成提交。”