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