背景知识:
Oracle提供了set transaction read only/read write来设定事务的读写属性;
Set transaction isolation level serializable/read commited来设定事务的隔离级别
测试内容:
对于上述四项设置,结合部分实例加以说明
【Set transaction read only】
必须是transaction的头一条命令,否则会收到” ORA-01453: SET TRANSACTION must be first statement of transaction”。提供了transaction-level read consistency,适合于对时间点一致性要求非常高的场景,整个transaction过程中看到的是运行set transaction read only命令所对应的scn时间点已经提交的内容;不能使用除了lock table,alter system,alter session,set role之外的语句。
【Set transaction read write】
Oracle默认的transaction属性,提供了statement-level read consistency
【Set transaction isolation level serializable】
串行级别的事务隔离,和set transaction read only一样,提供了transaction-level read consistency,区别是transaction里可以运行DML语句,在设定了Set transaction isolation level serializable后不能再设定Set transaction read only,否则会产生ORA-01453错误。要在serializable transaction里成功运行DML必须满足如下条件:
Serializable级别的transaction要更改的记录从transaction开始前必须处于commited状态,且从那一刻一直到serializable transaction更改这条记录期间不能有其它transaction更改这条记录,否则会出现锁等待(适用于serializable transaction开始前其它transaction已经对这条记录进行了更改却一直未能commit的情况)或者收到ORA-08177: can't serialize access for this transaction(适用于serializable transaction开始前其它transaction已经对这条记录进行了更改,并在serializable transaction更改前其它transaction进行了commit的场景,或者其它transaction 对这条记录的更改与提交操作晚于serializable transaction的开始时间但早于serializable transaction更改记录的时间)。对于ORA-08177的报错情况分为以下两种:
(1) 早于serialization transaction开始前其它session已经更改但未提交,在serialization transaction更改前其它Session进行了提交
--T1时刻
Session 1:
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 D CLERK 7782 23-JAN-82 1300 10
--不提交
Update emp1 set ename='E' where empno=7934;
select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
--T2时刻(T2>T1)
Session 2:
Set transaction isolation level serializable;
--T3时刻(T3>T2)
Session 1:
Commit;
select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
--T4时刻(T4>T3)
Session 2:
select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 D CLERK 7782 23-JAN-82 1300 10
SQL> Update emp1 set ename='E' where empno=7934;
Update emp1 set ename='E' where empno=7934
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
(2) serialization transaction开始后其它session更改,且在serialization transaction更改前其它Session进行了提交
--T1时刻
Session 1:
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
set transaction isolation level serializable;
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
--T2时刻(T2>T1)
Session 2:
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
update emp1 set ename='F' where empno=7934;
commit;
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 F CLERK 7782 23-JAN-82 1300 10
--T3时刻(T3>T2)
Session 1:
SQL> select * from emp1 where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 E CLERK 7782 23-JAN-82 1300 10
SQL> update emp1 set ename='F' where empno=7934;
update emp1 set ename='F' where empno=7934
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
【Set transaction isolation level read commited】
此为oracle中缺省采用的状态,提供了statement-level read consistency
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1286315/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-1286315/