事务

最新推荐文章于 2024-05-29 21:13:52 发布

ciqmm402244

最新推荐文章于 2024-05-29 21:13:52 发布

阅读量140

点赞数

事务

1.事务（Transaction）是数据库区别于文件系统的特性之一。事务的主要目的是：把数据库从一种状态转变为另一种状态。

2.事务的ACID特性：

原子性（atomicity）：事务中的所有动作要么都发生，要么都不发生。
一致性（consistency）：事务将数据库从一种状态转变为下一种一致状态。
隔离性（isolation）：一个事务的影响在该事务提交前对其他事务都不可见。
持久性（durability）：事务一旦提交，其结果就是永久性的。

3.事务控制语句

在oracle数据库中，隐含地，事务会在修改数据的第一条语句开始（也就是得到TX锁的第一条语句）。也可以使用SET TRANSACTION或DBMS_TRANSACTION包来显示的开始一个事物。（这与其他数据库不同，其他数据库都必须显示地开始事务）。
如果发出COMMIT或ROLLBACK语句，就会显示地结束一个事务。（注意：ROLLBACK TO SAVEPOINT命令不会结束事务！正确的写为ROLLBACK（只有着一个词）才能结束事务）。
一定要显示地使用COMMIT或ROLLBACK来终止你的事务；否则，你使用的工具／环境就会从中挑一个来结束事务。（如果正常退出SQL*Plus会话，而没有提交或回滚事务，SQL*Plus就会为你完成提交；如果只是退出一个Pro*C程序，就会发生一个隐式回滚）。所以不要过分依赖这些行为，一定要显示地COMMIT或ROLLBACK你的事务。
Oracle中的事务是原子性的，意味着两种情况：构成事务的每条语句都会提交（成为永久），或者所有语句都回滚。这种保护还延伸到单个的语句。一条语句要么完全成功，要么这条语句完全回滚。（注意：如果一条语句失败，并不会导致先前已经执行的语句自动回滚，他们的工作会保留，必须由你来提交或回滚）
四种事务控制语句：

COMMIT; SAVEPOINT; ROLLBACK ; SET TRANSACTION

4.原子性

语句级原子性

1> 例：这里表T上的一个INSERT或DELETE会触发一个触发器，它将适当地调整表T2中的CNT列。如果对表T的INSERT或DELETE失败，由于语句级原子性，触发器所做的相应工作也会回滚。

SQL> create table t2(cnt int);

Table created.

SQL> insert into t2 values(0);

1 row created.

SQL> commit ;

SQL> create table t (x int check (x>0) );

Table created.

SQL>create trigger t_trigger

before insert or delete on t for each row

begin

if (inserting) then

update t2 set cnt = cnt + 1;

else

update t2 set cnt = cnt - 1;

end if;

dbms_output.put_line('I fired and updated' ||

sql%rowcount || ' rows');

end;

/

Trigger created.

SQL> set serveroutput on

SQL> insert into t values (1);

I fired and updated1 rows

1 row created.

SQL> insert into t values (-1);

I fired and updated1 rows

insert into t values (-1)

*

ERROR at line 1:

ORA-02290: check constraint (ORACLE.SYS_C009729) violated

SQL> select * from t2;

CNT

----------

1

分析：我们可能认为现在T2中的CNT的值是2，毕竟通过DBMS_OUTPUT的输出可以证明触发器两次都成功地完成了T2的更新，但是CNT的实际值是1，这说明INSERT的失败也导致了对触发器所完成工作的回滚。因为：Oracle保证最初的INSERT(即导致触发器触发的插入语句)是原子性的，这个INSERT INTO T是语句，所以INSERT INTO T的任何副作用（此例为触发器的工作）都被认为是语句的一部分。为了得到这种语句级原子性，Oracle悄悄地在每个数据库调用外面包了一个SAVEPOINT。如对前面的INSERT实际上处理如下：

Savepoint statement;

Insert into t values(-1);

If error then rollback to statement;
过程级原子性

1> Oracle把PL/SQL匿名块当作是语句，例如考虑以下存储过程：

SQL> create or replace procedure p

as

begin

insert into t values (1);

insert into t values (-1);

end;

/

Procedure created.

SQL> select * from t;

no rows selected

SQL> select * from t2;

CNT

----------

0

SQL> set serveroutput on

SQL> begin

p;

end;

/

I fired and updated1 rows

I fired and updated1 rows

begin

*

ERROR at line 1:

ORA-02290: check constraint (ORACLE.SYS_C009729) violated

ORA-06512: at "ORACLE.P", line 5

ORA-06512: at line 2

SQL> select * from t;

no rows selected

SQL> select * from t2;

CNT

----------

0

说明：Oracle把这个存储过程调用处理为一个原子语句。客户提交了一个代码块BEGIN P; END;，Oracle在它外面包了一个SAVEPOINT。由于P失败了，Oracle将数据库回滚到调用这个存储过程之前的时间点。

2> 如果提交一个稍微不同的代码块，会得到完全不同的结构：

SQL> begin

p;

exception

when others then null;

end;

/

I fired and updated1 rows

I fired and updated1 rows

PL/SQL procedure successfully completed.

SQL> select * from t;

X

----------

1

SQL> select * from t2;

CNT

----------

1

说明：可以看到第一个INSERT成功，并被保留下来。这是因为该代码块自行捕获并忽略了错误，所以If error then rollback...没有起作用，Oracle没有回滚到SAVEPOINT .因此，在应用中，开发者要注意，向一个PL/SQL块增加异常处理器可能会显著的改变它的行为。（Thomas Kyte认为：如果代码中包含一个WHEN OTHERS异常处理器，但其中没有一个RAISE来重新引发异常，那么这样的代码都是有bug的。它会悄悄地忽略错误，这就改变了事务的语义。如果捕获WHEN OTHERS，并把异常转换为旧式的返回码，这会改变数据库本该有的表现。）
事务级原子性

1> 事务的总目标是把数据库从一种一致状态转变为另一种一致状态。

2> 为实现这个目标，事务也是原子性的，事务完成的所有工作要么完全提交并成为永久性的，要么回滚并撤销。

5.完整性约束和事务

什么时候检查完整性约束？

1> 默认情况下，在整个SQL语句得到处理后才进行检查；

2> 有一些可延迟的约束语序将完整性约束的验证延迟到应用请求时（发出一个SET CONSTRAINTS ALL IMMEDIATE命令）才完成，或者延迟到发出COMMIT时再检查。
IMMEDIATE约束

1> Oracle中的默认方式，完整性约束会在整个SQL语句得到处理之后立即检查。（注意：这里用的是“SQL语句”而不只是“语句”。如果一个PL/SQL存储过程中有多条SQL语句，那么在每条SQL语句执行之后都会立即验证其完整性约束，而不是在这个存储过程完成后才检查它。）

2> 为什么约束要在SQL语句执行后才验证呢？为什么不是在SQL语句执行期间验证？因为：一条语句可能会使表中各行暂时的“不一致”。尽管一条语句全部完成后的最终结果是对的，但如果查看这条语句所作的部分工作，会导致Oracle拒绝这个结果。例：

SQL> create table t (x int unique);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

现在，执行一个多行UPDATE：

SQL> update t set x = x+1;

2 rows updated.

说明：如果Oracle每更新一行之后都检查约束，那么无论什么时候，Oracle都有一半的可能性会失败。（如果Oracle先更新X=1的这一行，就会拒绝UPDATE）。所以Oracle会耐心等待语句结束后再执行约束验证。
DEFERRABLE约束和级联更新

1> 可延迟约束的好处：可能需要将一个主键的UPDATE级联到子键。有了可延迟的约束，就使得级联更新成为可能。

2> 例：

SQL> create table p (pk int primary key);

Table created.

SQL> create table c

(fk constraint c_fk references p(pk)

deferrable

initially immediate

);

Table created.

SQL> insert into p values (1);

1 row created.

SQL> insert into c values (1);

1 row created.

SQL> update p set pk=2;

update p set pk=2

*

ERROR at line 1:

ORA-02292: integrity constraint (ORACLE.C_FK) violated - child record found

SQL> set constraint c_fk deferred;

Constraint set.

SQL> update p set pk=2;

1 row updated.

SQL> set constraint c_fk immediate;

set constraint c_fk immediate

*

ERROR at line 1:

ORA-02291: integrity constraint (ORACLE.C_FK) violated - parent key not found

SQL> update c set fk=2;

1 row updated.

SQL> set constraint c_fk immediate;

Constraint set.

SQL> commit;

Commit complete.

3> 要延迟一个约束，必须这样来创建他们：先将其删除，再重新创建约束，这样才能把不可延迟的约束改变为可延迟约束。

6.不好的事务习惯

在oracle数据库中，锁、阻塞等问题并不是决定事务大小的关键，数据完整性才是确定事务大小的根本。
在循环中提交

1> 误导原因：

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

2）没有足够的undo空间。

2> 性能影响：

1）如果频繁地提交，通常并不会更快。一般地，在一个SQL语句中完成工作几乎总是更快一些。

2）例：对T1用一条SQL语句进行更新；对T2在循环中频繁的更新并提交：

SQL> create table t1 as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> create table t2 as select * from t1;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

SQL> set timing on

SQL> update t1 set object_name = lower(object_name);

53749 rows updated.

Elapsed: 00:00:00.82

SQL> begin

for x in (select rowid rid,object_name,rownum r from t2)

loop

update t2

set object_name = lower(object_name)

where rowid = x.rid;

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

commit;

end if;

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.13

说明：可以看出，倘若在循环中频繁地提交，就会慢上好几倍。如果能在一条SQL语句中完成，就要尽量这么做，因为这样几乎总是更快。

3）对于上例，即使我们要使用过程性代码，也要使用批处理来完成更新，如下：

SQL> declare

2 type ridArray is table of rowid;

3 type vcArray is table of t2.object_name%type;

4 l_rids ridArray;

5 l_names vcArray;

6 cursor c is select rowid,object_name from t2;

7 begin

8 open c;

9 loop

10 fetch c bulk collect into l_rids,l_names LIMIT 100;

11 forall i in 1..l_rids.count

12 update t2

13 set object_name = lower(l_names(i))

14 where rowid = l_rids(i);

15 commit;

16 exit when c%notfound;

17 end loop;

18 close c;

19 end;

20 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.01

说明：这确实比未使用批处理的过程性代码要快一些，但还是没有一条SQL语句来的快，而且代码变得越来越复杂。

4）由于带有WHERE子句的UPDATE语句可能会导致重启动，所以如果要针对一个行子集（有一个WHERE子句）执行先前的UPDATE语句，而其他用户正在修改这个UPDATE在WHERE子句中使用的列，就可能需要使用一系列较小的事务而不是一个大事务，或者更适合在执行大量更新之前锁定表。这样做的目标是减少出现重启动的机会。（Thomas Kyte认为：一次性大量更新或清除旧数据通常不会在活动高发期间完成。实际上，数据的清除根本不应受此影响，因为我们一般会使用某个日期字段来定位要清除的信息，而其他应用不会修改这个日期数据。）

3> Snapshot Too Old错误

1）试图通过在循环中频繁的提交以节俭地使用“受限资源”（undo段），不仅会更慢，而且会导致让人胆战心惊的ORA-01555：snapshot too old错误。

2）原因：Oracle的多版本模型会使用undo段数据依照语句或事务开始时的原样来重建块（究竟是语句还是事务，这取决于隔离模式）。如果必要的undo信息不再存在，你就会收到ORA-0155错误，查询也不会完成。所以，如果像前面的那个例子一样，你一边在读取表，一边在修改这个表，就会同时生成查询所需的undo信息。UPDAT E生成了undo信息，你的查询可能会利用这些undo信息来得到待更新数据的读一致视图。如果提交了所作的更新，就会允许系统重用刚刚填写的undo段空间。如果系统确实重用了undo 段空间,擦除了旧的undo 数据(查询随后要用到这些undo信息),你就有大麻烦了。SELECT 会失败,而UPDATE 也会中途停止。这样就有了一个部分完成的逻辑事务,而且可能没有什么好办法来重启动。

4> 可重启动的过程需要复杂的逻辑

1）如果采用“在逻辑事务结束之前提交”的方法，最验证的问题是：如果UPDATE半截失败了，这回经常将你的数据库置于一种未知的状态中。除非你提前对此做了规划，否则很难重启动这个失败的过程。

2）例如：将上例对列所应用的LOWER()函数改为以下的列函数：last_ddl_time=last_ ddl_time+1 ;如果UPDATE循环半路停止了，我们就不能简单地重新运行，因为这样有可能导致某些日期加2，而另外一些只加了1.

3）对于上述问题，我们需要更复杂的逻辑，必须有办法对数据“分区”。例如：可以处理以A开头的每一个OBJECT_NAME，然后处理以B开头的，依此类推：

SQL> create table to_do

as

select distinct substr(object_name,1,1) first_char

from t1;

Table created.

SQL> begin

2 for x in (select * from to_do)

3 loop

4 update t1 set last_ddl_time = last_ddl_time + 1

5 where object_name like x.first_char || '%';

6 dbms_output.put_line(sql%rowcount || ' rows updated');

7 delete from to_do where first_char = x.first_char;

8 commit;

9 end loop;

10 end;

11 /

说明：这种方法也有问题，除非有某个属性能均匀地划分数据。否则最终行的分布就会差异很大；另外如果其他会话正在访问这个表，并且在修改数据，假设我们已经处理完A对象，此后另外某个会话把名为Z的对象更新为A，我们就会漏掉这个记录；更进一步，与UPDATE T SET LAST_DDL_TIME = LAST_DDL_TIME+1相比，这个过程效率非常低。

5> 结论：

如果能在SQL中完成，那就在SQL里完成。如果不能在SQL中完成，就用PL/SQL实现。要用尽可能少的代码来完成，另外应当分配充分的资源。一定要考虑到万一出现错误会怎么样。应当正确地设置undo段的大小，这比起编写一个可重启动的程序来说，要容易的多。
使用自动提交

1> 原因：流行API（ODBC和JDBC）会默认地“自动提交”。

2> 解决办法：在JDBC打开一个链接之后紧接着应该有下面这样几行代码：

connect conn = DriverManager.getConnection

(“jdbc:oracle:oci:@database”,”scott”,”tiger”);

conn.setAutoCommit(false);

7.分布式事务

定义：分布式事务是指，在一个事务的范围内，可以更新多个不同数据库中的数据。提交时，要么提交所有实例中的更新，要么一个都不提交（他们都会回滚）。
分布式事务的关键是数据库链接（database link）。数据库链接是一个数据库对象，描述了如何从你的实例登录到另一个实例。一旦建立了一个数据库链接，访问远程对象就很简单，如下：

select * from T@another_database;

一般可以创建T的一个视图或同义词来隐藏T是一个远程表的事实，如：

create synonym T for T@another_database;
2PC协议：这是一个分布式协议，如果一个修改影响到多个不同的数据库，2PC允许原子性地提交这个修改。它会在提交之前尽可能地关闭分布式失败窗口。在多个数据库之间的一个2PC事务中，其中一个数据库（通常是客户最初登录的那个数据库）会成为分布式事务的协调器。这个站点会询问其他站点是否已经准备好提交。实际上，这个站点会转向其他站点，问他们是否准备就绪。其他的每个站点会报告他的“就绪状态”（YES或NO）。如果只要有一个站点投票NO，整个事务就会回滚。如果所有站点都投票YES，站点协调器会广播一条消息，使每个站点上的提交成为永久性的。
严重错误的窗口（时间窗）：在2PC上“投票”之前，任何分布式错误都会导致所有站点回滚。对于事务的结果来说，这里不存在疑义。在提交或回滚之后，分布式事务的结果同样没有疑义。只有一个非常短的时间窗除外，此时协调器要收集投票结果，只有在这个时候如果失败，结果可能有疑义。

例如：假设有 3 个站点参与一个事务,其中站点 1 是协调器。站点 1 问站点 2 是否准备好提交,站点2 报告说是。站点 1 再问站点 3 是否准备好提交,站点 3 也说准备好了。在这个时间点,站点 1 就是惟一知道事务结果的站点,它现在要负责把这个结果广播给其他站点。如果现在出现一个错误,比如说网络失败了,站点 1 掉电,或者其他某个原因,站点 2 和站点 3 就会“挂起”，它们就会有所谓的可疑分布式事务(in-doubt distributed transaction) 。2PC 协议力图尽可能地关闭这个错误窗口,但是无法完全将其关闭。站点 2 和站点 3 必须保持事务打开,等待站点 1 发出的结果通知。此时，REC O (分布式数据库恢复)进程会试图联系事务的协调器来发现协调的结果。有 FORCE 选项的 COMMIT 和 ROLLBACK 在这里就有了用武之地。如果问题的原因是站点 1、2 和 3 之间的网络故障,站点 2 和站点 3 的 DBA 可以打电话给站点 1 的 DBA,问他结果是什么,并相应地手动应用提交或回滚。
分布式事务的限制：

1> 不能在数据库链接上发出COMMIT。也就是说，不能发出COMMIT＠remote_site .只能从发起事务的那个站点提交。

2> 不能在数据库链接上完成DDL，因为DDL会提交。

3> 不能在数据库链接上发出SAVEPOINT。简单的说，不能在数据库链接上发出任何事务控制语句。所有事务控制都由最初打开数据库链接的会话继承得来；对于事务中的分布式实例，不能有不同的事务控制。

说明：1）可以通过设置站点的COMMIT_POINT_STRENGTH，从而改变具体的提交站点。提交点（commit-point strength）会为分布式事务中的服务器关联一个相对的重要性级别。服务器越重要（要求这个服务器上的数据有更大的可用性），它就越有可能协调这个分布式事务。2）在数据库链接上执行DDL的另一种方式：使用链接来调度一个远程作业，一旦提交就执行这个远程作业。采用这种方式，作业在远程主机上运行，这不是一个分布式事务，可以执行DDL。

8.自治事务

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

1> 例：创建两个过程，每个过程只是将其名字插入到消息表中，一个过程作为自治事务处理，一个作为正常事务处理：

创建消息表：

SQL> create table t (msg varchar2(25));

Table created.

创建过程：

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 /

Procedure created.

SQL> create or replace procedure NonAutonomous_Insert

2 as

3 begin

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

5 commit;

6 end;

7 /

Procedure created.

观察PL/SQL代码匿名块中非自治事务的行为：

SQL> begin

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

3 NonAutonomous_Insert;

4 rollback;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG

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

Anonymous Block

NonAutonomous Insert

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

观察PL/SQL代码匿名块中自治事务的行为：

SQL> delete from t;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> begin

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

3 Autonomous_Insert;

4 rollback;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG

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

Autonomous Insert

说明：只有自治事务中完成并已提交的工作会持久保留。匿名块中完成的INSERT由回滚语句回滚。自治事务过程的COMMIT对匿名块中开始的父事务没有影响。

2> 结论：如果在一个“正常”的过程中COMMIT，它不仅会持久保留自己的工作，也会使该会话中未完成的工作成为永久性的。不过，如果在一个自治事务过程中完成COMMTI，只会让这个过程本身的工作成为永久性的。
何时使用自治事务？

1> Thomas Kyte认为：在实际世界中，自治事务的合理使用实在很有限。如果发现你把它当作一个特性在经常使用,可能就得花些时间好好看看为什么会这样。自治事务的一种可能合法的使用是用于定制审计。（不过这里强调的只是“可能合法”。数据库中要对信息完成审计，与编写定制的触发器相比，还有许多更高效的方法。譬如：可以使用DBMS_FGA包或者是使用AUDIT命令本身）

2> 自治事务触发器用于定制审计的例子：先在表上放一个自治事务触发器,它能捕获一个审计跟踪记录,详细地指出谁试图更新表,这个人什么时候想更新表,另外还会提供一个描述性消息指出这个人想要修改什么数据。这个触发器的基本逻辑是:对于不向你直接或间接报告的员工,要防止更新这些员工记录的任何企图。

1）首先从SCOTT模式建立EMP的一个副本：

SQL> create table emp as select * from scott.emp;

Table created.

2）然后创建一个AUDIT_TAB表，存储审计信息：

SQL> create table audit_tab

2 (username varchar2(30) default user,

3 timestamp date default sysdate,

4 msg varchar2(4000)

5 );

3）接下来创建一个EMP_AUDIT触发器对EMP表上的UPDATE活动进行审计：

SQL> 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 ('Attempt to update ' || :new.empno);

commit;

raise_application_error(-20001,'Access Denied');

end if;

end;

/

Trigger created.

注意：代码块的第二个黑体部分是查询当前用户的所有直接或间接的下属员工，EXISTS就是为了验证员工:new.empno是否是当前用户的某个直接或间接的下属员工。换言之，它会验证我们试图更新的记录是当前用户的某个直接或间接的下属员工的记录，即这个人会在某个层次上向我们报告。

4）尝试在EMP表中更新一条记录：

SQL> update emp set sal = sal*10;

update emp set sal = sal*10

*

ERROR at line 1:

ORA-20001: Access Denied

ORA-06512: at "ORACLE.EMP_AUDIT", line 19

ORA-04088: error during execution of trigger 'ORACLE.EMP_AUDIT'

SQL> select * from audit_tab;

USERNAME TIMESTAMP MSG

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

ORACLE 30-JUL-09 Attempt to update 7369

说明：触发器发现了情况，能防止UPDATE发生，而与此同时，会为这个企图创建一个永久记录。

5）在SCOTT模式下，作为一个用户实际完成一个UPDATE：

SQL> grant all on emp to scott;

SQL> conn scott/tiger

Connected.

SQL> update oracle.emp set sal = sal*1.05 where ename = 'ADAMS';

1 row updated.

SQL> update oracle.emp set sal = sal*1.05 where ename = 'SCOTT';

update oracle.emp set sal = sal*1.05 where ename = 'SCOTT'

*

ERROR at line 1:

ORA-20001: Access Denied

ORA-06512: at "ORACLE.EMP_AUDIT", line 19

ORA-04088: error during execution of trigger 'ORACLE.EMP_AUDIT'

说明：由于ADAMS是SCOTT的下属，所以第一个UPDATE成功；再看第二个UPDATE，SCOTT试图给自己加薪，但是由于SCOTT不向SCOTT报告（SCOTT不是自己的下属），所以这个更新失败了。

6）再登录回到包括AUDIT_TAB表的模式，可以看到以下结果：

SQL> select * from audit_tab;

USERNAME TIMESTAMP MSG

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

ORACLE 30-JUL-09 Attempt to update 7369

SCOTT 30-JUL-09 Attempt to update 7788

说明：可以看到，SCOTT 试图完成的这个UPDATE也已经被记录下来。