Oracle事务总结

张兴康

1.什么是事务，事务的特性是什么？

事务的任务便是使数据库从一种状态变换成为另一种状态，这不同于文件系统，它是数据库所特用的。它的特性有四个：TOM总结为ACID即
原子性atomicity:语句级原子性，过程级原子性，事务级原子性
一致性consistency:状态一致，同一事务中不会有两种状态
隔离性isolation:事务间是互相分离的互不影响(这里可能也有自治事务)
持久性durability:事务提交了，那么状态就是永久的

对于语句级原子性，过程级原子性和事务级原子性可以查阅一下相关的信息

2.Oracle中的事务语句
commit=commit work 提交
rollback=rollback work 回滚
savepoint 事务的标记点，可以使一个事务在回滚到不同的阶段
set transaction  开始一个事务
rollback to savepoint 与savepoint对应
另外对于自治事务还有一个,下面会着重说一下关于自治事务
pragma autonomous_transaction

3.关于完整性约束与事务的关系
完整性约束的模式有immediate,deferred等
语法：set constraint c_fk defereed
这对于级联更新很有帮助，如下面的tom在书中举的例子：
SQL> create table p(pk int primary key);
表已创建。
SQL> create table c
  2  (fk constraint c_fk
  3  references p(pk)
  4  deferrable
  5  initially immediate
  6  )
  7  /
表已创建。
语句: set  constraint c_fk immediate;
      set  constraint c_fk deferred;
SQL> set constraint c_fk immediate;
约束条件已设置。
SQL> update p set pk=3;
update p set pk=3
*
ERROR 位于第 1 行:
ORA-02292: integrity constraint (FTITEM.C_FK) violated - child record found

SQL> set constraint c_fk deferred;
约束条件已设置。
SQL> update p set pk=3;
已更新 1 行。
SQL> update c set fk=3;
已更新 1 行。
SQL> commit;
提交完成。
SQL> set constraint c_fk immediate;
约束条件已设置。

4.在事务中两个不好的方法
tom在书上提到了两种不好的事务使用习惯，我在工作中也是经常犯的，主要是因为对于每种数据库的认识不到位，听好多朋友说数据库你只要会用了一个其它的就可以了，经过这段时间的学习，其实我们所说的会只是说对一SQL语句等，而并不是理解，比如对于临时表的用法，在sqlserver与oracle就不太一样（我只用过这两个数据库），两个不好的方法：
A：在循环中提交事务，这影响性能而且在快照(snapsot中也会有问题),还有一个是重新启动(在before update on table的触发器中会看到引用NEW,OLD会被触发两次)
B：使用自动提交事务，一定要手动控制事务的提交，因为自动提交会出现不必要的麻烦。

5.分布式事务
在oracle中会在一个事务中控制多个数据库，保证各个数据库中的数据完整性，主要通过dblink，看到这我想到了自己在工作中的问题：两台服务器不同的数据库，我一直认为不能同时用一个事务来控制，所以在开发程序中（我用delphi开发的）我用两个connection来进行联接不同的数据库，提交时分别提交，而且需要用状态标识来进行事务是否正常，之前用sqlserver时也这样操作，现在想想笨的要死，为什么没有用到事务的特性呢？究其原因是，自己对于数据库的理解差到极点了：（
在oracle中的分布式事务的限制条件：
(1)只能在主服务器中进行事务的开始，提交，回滚等，其它服务器会根据状态来进行判断，即主服务器为协调各个数据库的状态一致从而使其它从数据库达到状态一致。(应该说是站点，分站点)
(2)在dblink(数据链接)上不能做提交
(3)在dblink(数据链接)上不能做DDL操作
(4)在dblink(数据链接)不能发出savepoint等操作,即不能发出任何事务性语句

这里补充一下关于数据库链接的创建删除等语法：
创建数据链接：
方法1.create database link dblink_name connect to user_name identified by      
           password using 'server name';
方法2.create database link dblink_name  connect to user_name identified by   
           password
           using '(DESCRIPTION =
                          (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
                           )
                          (CONNECT_DATA =
                               (SERVICE_NAME = orcl)
                          )
                        )';
删除数据链接：
drop database link dblink_name;
使用数据链接：
select * from table@dblink_name;

6.自制事务

几天前在pub论坛中看到一个问题是在表A上建了一个触发吕，当对表进行相关操作时不论成功或失败,都想通过触发器提交一些信息，（描述的有点不清，见谅)，当时有人说用自治事务，我还寻思什么是自治事务呢？
自治事务：是独立于主事务的一个子事务，它的提交与回滚不影响主事务的操作（我的理解）
自治事务提供了一种用PL/SQL控制事务的新方法，可以用于：
1 顶层匿名块
2 本地，独立或打包的函数和过程
3 对像类型的方法
4 数据库触发器

自治事务存储过程：
SQL> 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是一个编译器指令，这是一种编辑器执行某种编译选项的方法。

非自法事务存储过程：
SQL> create or replace procedure nonautonomous_insert
  2  as
  3  begin
  4    insert into t values('nonautonomous insert');
  5    commit;
  6  end;
  7  /

过程已创建。

SQL> begin
  2    insert into t values('anonymous block');
  3    nonautonomous_insert;
  4    rollback;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> select * from t;

MSG
-------------------------
anonymous block
nonautonomous insert

因为在nonautonomous_insert中有一个commit,所以rollback基本没有可回滚的操作。

SQL> begin
  2    insert into t values('anonymous block');
  3    autonomous_insert;
  4    rollback;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> select * from t;

MSG
-------------------------
autonomous insert

这是因为autonomous_insert是一个自治事务，独立于匿名块的事务，所以rollback不会影响到它。

如何使用一个自治事务来记录表修改的信息：创建五个audit表来记录信息
SQL> create table audit_tab
  2  (username varchar2(30) default user,
  3  timestamp date default sysdate,
  4  msg varchar2(4000)
  5  )
  6  /

表已创建。
在表emp中建立触发器（这就可以实现最初的那个问题）
create or replace trigger emp_audit
before update on emp
for each row
declare
  pragma autonomous_transaction;
  l_cnt number;
begin
  select count(*) into l_cnt from dual
  where exists (select null from emp
                where empno=:new.empno
                start with mgr=(select empno
                                  from emp
                                 where ename=user)
                connect by prior empno=mgr);
  if (l_cnt=0)
  then
    insert into audit_tab(msg)
    values('attemp to update '||:new.empno);
    commit;
  raise_application_error(-20001,'access denied');
  end if;
end;


总结：
1.事务应该尽可能的短，即避免不必要的扩大事务
2.根据需要事务足够大
3.决定事务大小的关键是数据完整性。
4.能决定事务大小的唯一约束就是控制系统的业务规则，不是undo,不是锁等

张兴康

fteair

张兴康

fteair 发表于 2017-3-9 17:04