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

[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.