設定transaction的讀寫屬性與隔離級別

背景知識：

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