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):view plainConnected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0   Connected as tuser1     SQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON     SQL> SET TRANSACTION READ ONLY;     Transaction set   Step 2, @session 2(DML, insert a new record):view plainSQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON     SQL> insert into dept(deptno) values(50);     1 row inserted     SQL> commit;     Commit complete     SQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON      50                   Step 3, @session 1(query the same object and get the same result):view plainSQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON   Step 4, @session 1(autonomous transaction is not permitted):view plainSQL> declare    2  pragma AUTONOMOUS_TRANSACTION;    3  begin    4  update dept set loc = loc || '-XXX' where deptno=20;    5  end;    6  /     declare  pragma AUTONOMOUS_TRANSACTION;  begin  update dept set loc = loc || '-XXX' where deptno=20;  end;     ORA-06519: active autonomous transaction detected and rolled back  ORA-06512: at line 6     SQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON     SQL>    Step 5, @session 1(commit current transaction and we get the change):view plainSQL> commit;     Commit complete     SQL> select * from dept;     DEPTNO DNAME          LOC  ------ -------------- -------------      10 ACCOUNTING     NEW YORK01      20 RESEARCH       DALLAS      30 SALES          CHICAGO      40 OPERATIONS     BOSTON      50                  

?

?