Oracle PL/SQL之SET TRANSACTION READ ONLY（事務隔離性）

SET TRANSACTION READ ONLY實際上是實現資料庫四大事務（ACID）中隔離性（Isolation）的一種手段，用來將資料的讀一致性定在某一時間點，即不管其他事務如何更改資料（不能在當前session中再使用自治事務），在當前事務中進行查詢的結果始終不變。由於Oracle的讀一致性是通過undo段來實現的，所以如果在此期間DML修改的資料量很大而undo空間設定過小可能會導致ORA-01555（快照過舊）錯誤。

 

Test Code:

Step 1, @session 1(SET TRANSACTION READ ONLY):

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0<br />Connected as tuser1</p><p>SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON</p><p>SQL> SET TRANSACTION READ ONLY;</p><p>Transaction set

 

Step 2, @session 2(DML, insert a new record):

SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON</p><p>SQL> insert into dept(deptno) values(50);</p><p>1 row inserted</p><p>SQL> commit;</p><p>Commit complete</p><p>SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON<br /> 50

 

Step 3, @session 1(query the same object and get the same result):

SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON

 

Step 4, @session 1(autonomous transaction is not permitted):

SQL> declare<br /> 2 pragma AUTONOMOUS_TRANSACTION;<br /> 3 begin<br /> 4 update dept set loc = loc || '-XXX' where deptno=20;<br /> 5 end;<br /> 6 /</p><p>declare<br />pragma AUTONOMOUS_TRANSACTION;<br />begin<br />update dept set loc = loc || '-XXX' where deptno=20;<br />end;</p><p>ORA-06519: active autonomous transaction detected and rolled back<br />ORA-06512: at line 6</p><p>SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON</p><p>SQL>

 

Step 5, @session 1(commit current transaction and we get the change):

SQL> commit;</p><p>Commit complete</p><p>SQL> select * from dept;</p><p>DEPTNO DNAME LOC<br />------ -------------- -------------<br /> 10 ACCOUNTING NEW YORK01<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON<br /> 50