[20200512]oracle的事务隔离级别.txt

--//别人问的问题,我自己的工作也很少涉及这方面问题.我记忆最深的一次,开发要我导出一部分数据回去改程序.
--//由于导出时没有加入flashscn参数,造成对方导入时主外键不一致,无法导入.
--//参考APress Oracle Core Essential Internals for DBAs and Developers.pdf P18页:

ISOLATION LEVELS

Oracle offers three isolation levels: read committed (the default), read only, and serializable. As a brief
sketch of the differences, consider the following scenario: table t1 holds one row, and table t2 is identical
to t1 in structure. We have two sessions that go through the following steps in order:

1.  Session 1: select from t1;
2.  Session 2: insert into t1 select * from t1;
3.  Session 2: commit;
4.  Session 1: select from t1;
5.  Session 1: insert into t2 select * from t1;

If session 1 is operating at isolation level read committed, it will select one row on the first select, select
two rows on the second select, and insert two rows.

If session 1 is operating at isolation level read only, it will select one row on the first select, select one row
on the second select, and fail with Oracle error "ORA-01456: may not perform insert/delete/update operation inside a
READ ONLY transaction."

If session 1 is operating at isolation level serializable, it will select one row on the first select, select one
row on the second select, and insert one row.

--//正好3种方式的结果不一样,重复测试做1个记录.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t1 as select rownum id from dual;
create table t2 as select * from t1 where 1=0;
--//分析略.

2.read committed:
--//也就是基本缺省模式,平时工作中遇到不部分是这种模式:
--//sesion 1:
SCOTT@test01p> alter session set isolation_level=read committed;
Session altered.

SCOTT@test01p> select * from t1;
ID
---
  1

--//sesion 2:
SCOTT@test01p> insert into t1 select * from t1;
1 row created.

SCOTT@test01p> commit;
Commit complete.

--//session 1:
SCOTT@test01p> select * from t1;
 ID
---
  1
  1

SCOTT@test01p> insert into t2 select * from t1;
2 rows created.

--//If session 1 is operating at isolation level read committed, it will select one row on the first select, select
--//two rows on the second select, and insert two rows.

3.read only:
--//注:测试前还原建表时状态.
--//sesion 1:
SCOTT@test01p> alter session set isolation_level=read only;
alter session set isolation_level=read only
                                       *
ERROR at line 1:
ORA-02183: valid options: ISOLATION_LEVEL { SERIALIZABLE | READ COMMITTED }
--//不支持这样设置read only。

SCOTT@test01p> set transaction read only;
Transaction set.

SCOTT@test01p> select * from t1;
 ID
---
  1

--//sesion 2:
SCOTT@test01p> insert into t1 select * from t1;
1 row created.

SCOTT@test01p> commit;
Commit complete.

--//session 1:
SCOTT@test01p> select * from t1;
        ID
----------
         1

SCOTT@test01p> insert into t2 select * from t1;
insert into t2 select * from t1
            *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

--//If session 1 is operating at isolation level read only, it will select one row on the first select, select one row
--//on the second select, and fail with Oracle error "ORA-01456: may not perform insert/delete/update operation inside a
--//READ ONLY transaction."

4.serializable
--//注:测试前还原建表时状态.
--//sesion 1:
SCOTT@test01p> alter session set isolation_level=serializable;
Session altered.

SCOTT@test01p> select * from t1;
 ID
---
  1

--//sesion 2:
SCOTT@test01p> insert into t1 select * from t1;
1 row created.

SCOTT@test01p> commit ;
Commit complete.

--//session 1:
SCOTT@test01p> select * from t1;
 ID
---
  1

SCOTT@test01p> insert into t2 select * from t1;
1 row created.

--//If session 1 is operating at isolation level serializable, it will select one row on the first select, select one
--//row on the second select, and insert one row.