ITPub博客

首页 > 数据库 > Oracle > 设定transaction的读写属性与隔离级别

设定transaction的读写属性与隔离级别

原创 Oracle 作者:oliseh 时间:2014-09-30 23:15:19 0 删除 编辑


背景知识:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616861