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;
SQL>
SQL> declare
2 v_sal scott.emp.sal%type;
3
4 begin
5 savepoint sp_1;
6 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;
7 select sal into v_sal from scott.emp a where a.empno = 7369;
8 dbms_output.put_line(v_sal);
9 savepoint sp_2;
10 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;
11 rollback to sp_2;
12 commit;
13 select sal into v_sal from scott.emp a where a.empno = 7369;
14 dbms_output.put_line(v_sal);
15 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
自治事务
看看在oracle中一个事务调用另外一个事务的情况:
事务A:
Begin
DML;
事务B
End;
事务B:
Begin
Commit;
End;
事务A对事务B中的操作可见,如果在事务B充commit,会将事务A中的DML也commit掉,自治事务就是来解决这样的问题的,使得事务B中的提交或回滚对A不可见。
使用一个例子来对比一下自治事务和普通的事务的不同:
SQL> create or replace procedure pro_test_tran is
2 begin
3 commit;
4 end;
5 /
Procedure created
使用pragma autonomous_transaction;来标识使用自治事务
SQL>
SQL> create or replace procedure pro_test_auto is
2 pragma autonomous_transaction;
3 begin
4 commit;
5 end;
6 /
Procedure created
SQL> select a.sal from scott.emp a where a.empno = 7369;
SAL
------------
812.60
SQL> begin
2 update scott.emp a set a.sal = a.sal + .01 where a.empno = 7369;
3 pro_test_auto;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed
调用了pro_test_auto以后并没有将父事务中的dml提交掉:
SQL> select a.sal from scott.emp a where a.empno = 7369;
SAL
------------
812.60
SQL>
SQL> begin
2 update scott.emp a set a.sal = a.sal + .01 where a.empno = 7369;
3 pro_test_tran;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed
调用了pro_test_ tran将父事务中的dml提交掉了:
SQL> select a.sal from scott.emp a where a.empno = 7369;
SAL
------------
812.61
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-664426/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-664426/