plsql事务处理(一)

4,事务处理

Oracle是基于事务的，oracle以用户事务来确保数据的完整性。一个事务就是将一系列的数据操纵的sql语句作为一个逻辑单元，逻辑单元里面的单个操作要么全做，要么全部不做，以保证数据的完整性。

事务结束时将产生数据库的更改，oracle使用undo来记录所有的更改，如果程序在事务未完成之前发生了错误，oracle将会检测到这个错误，并回滚当前的事务，撤销从事务开始时的更改。保存点（savepoint）标记了当前事务处理的一个状态点，结合使用rollback，savepoint可以撤销部分事务，使用set transaction可以设置事务的属性，比如读写访问和隔离级别。

 

事务隔离级别

SQL92定义了四种事务隔离级别(transaction isolation level)，主要是为了在并发事务执行时阻止下列现象发生：

脏读：事务读取了被其他事务写入但未提交的数据。

不可重复读：一个事务再次读取其之前曾经读取过的数据时，发现数据已被其他已提交的事务修改或删除。

幻象读（phantom read）：事务按照之前的条件重新查询时，返回的结果集中包含其他已提交事务插入的满足条件的新数据。

提供的四种隔离级别分别不同程度的对这三种现象做了限制：

隔离级别	脏读	不可重复读	幻象读
未提交读取 (read uncommitted)	允许	允许	允许
已提交读取 (read committed)	不允许	允许	允许
可重复读取 (repeatable read)	不允许	不允许	允许
串行化 (serializable)	不允许	不允许	不允许

 

Oracle支持三种事务隔离级别，分别是

已提交读取，串行化，只读(read only)，默认的事务隔离级别是已提交读取。

Oracle事务隔离级别	脏读	不可重复读	幻象读	事务内使用 DML操作数据
Read committed 已提交读取	不允许	允许	允许	允许
Serializable 串行化	不允许	不允许	不允许	允许
Read only 只读	不允许	不允许	不允许	不允许

Oracle允许在事务开始时使用语句来设定事务的级别，以下三个语句分别设定oracle的三种事务隔离级别：

set transaction isolation level read committed;

set transaction isolation level serializable;

set transaction read only;

Plsql事务处理的语句有下列一些：

Commit, rollback, savepoint, rollback to savepoint, set transaction ,lock table等。

例如：

Set transaction read only

这个语句建立一个事务级的读一致性，事务内所有的查询所见的都是事务开始之前的改变，只读事务对于表有修改的许多查询报表来说比较有用。这个语句不适用于SYS用户，意味着，即使SYS用户设置事务为只读，查询也会返回事务期间的更改。

测试：

Scott用户下：

Session1：

SQL> set transaction read only;

 

Transaction set

 

SQL> select sum(sal) from scott.emp ;

 

 SUM(SAL)

----------

  28124.2

 

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

 

14 rows updated

 

SQL> commit;

 

Commit complete

 

Session1：

SQL> select sum(sal) from scott.emp ;

 

 SUM(SAL)

----------

  28124.2

SQL> commit;

 

Commit complete

 

SQL> select sum(sal) from scott.emp ;

 

 SUM(SAL)

----------

  28139.6

很容易看出set transaction read only的效果，如果实在sys下面，情况则是这样的：

Session1：

SQL> set transaction read only;

 

Transaction set

 

SQL> select sum(sal) from scott.emp ;

 

 SUM(SAL)

----------

  28139.6

 

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

 

14 rows updated

 

SQL> commit;

 

Commit complete

 

SQL>

 

Session1：

SQL> select sum(sal) from scott.emp ;

 

 SUM(SAL)

----------

    28155

在plsql中使用set transaction read only需要在事务开始之前提交或者回滚，结束后也一般需要提交或者回滚例如：

declare

 v_sum_sal scott.emp.sal%type;

begin

 rollback work;

 set transaction read only;

 select sum(sal) into v_sum_sal from scott.emp;

 dbms_output.put_line(v_sum_sal);

 commit;

end;

/

 

事实上，这是由于使用set transaction read only以后，就会有一个事务产生：

SQL> set transaction read only;

 

Transaction set

 

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC from v$transaction a;

 

   XIDUSN    UBAREC    UBABLK USED_UBLK USED_UREC

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

        2         0         0         1         1

 

SQL> commit;

 

Commit complete

 

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC from v$transaction a;

 

   XIDUSN    UBAREC    UBABLK USED_UBLK USED_UREC

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

 

Set transaction read write

 

再来看看：

Savepoint ,rollback to savepoint,commit

下面这段代码很容易看出这几个语句的使用方式和效果

SQL> set serveroutput on;

 

declare

v_sal scott.emp.sal%type;

 begin

 savepoint sp_1;

 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;

 select sal into v_sal from scott.emp a where a.empno = 7369;

 dbms_output.put_line(v_sal);

 savepoint sp_2;

 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;

  rollback to sp_2;

 commit;

 select sal into v_sal from scott.emp a where a.empno = 7369;

 dbms_output.put_line(v_sal);

 end;

 16 /

 

810.6

810.6

 

PL/SQL procedure successfully completed

 

再看看lock table的几种方式:

Lock table in

ROW SHARE

ROW EXCLUSIVE

SHARE UPDATE

SHARE

SHARE ROW EXCLUSIVE

EXCLUSIVE

[nowait]

可以简单看看各种类型锁定的情况，结合nowait参数来看看lmode和ctime的变化。

示例share mode和row exclusive mode:

SQL> lock table scott.emp in share mode nowait;

 

Table(s) locked

 

SQL> select sid from v$mystat a where rownum = 1;

 

      SID

----------

      150

 

SQL> select * from v$lock a where a.SID = 150;

 

ADDR    KADDR          SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

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

41DC66F4 41DC670C       150 TM       51564         0         4         0        45         0

41E10938 41E1095C       150 TX      458775      3256         6         0        45         0

 

SQL> rollback;

 

Rollback complete

 

SQL> lock table scott.emp in row exclusive mode nowait;

 

Table(s) locked

 

SQL> select * from v$lock a where a.SID = 150;

 

ADDR    KADDR          SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

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

41DC66F4 41DC670C       150 TM       51564         0         3         0         6         0

41E10938 41E1095C       150 TX       65558      3276         6         0         6         0

 SQL> commit;

 

Commit complete