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):

[c-sharp] view plain copy print ?

1. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0   
2. Connected as tuser1  
3.    
4. SQL> select * from dept;  
5.    
6. DEPTNO DNAME          LOC  
7. ------ -------------- -------------  
8.     10 ACCOUNTING     NEW YORK01  
9.     20 RESEARCH       DALLAS  
10.     30 SALES          CHICAGO  
11.     40 OPERATIONS     BOSTON  
12.    
13. SQL> SET TRANSACTION READ ONLY;  
14.    
15. Transaction set  

Connected 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):

[c-sharp] view plain copy print ?

1. SQL> select * from dept;  
2.    
3. DEPTNO DNAME          LOC  
4. ------ -------------- -------------  
5.     10 ACCOUNTING     NEW YORK01  
6.     20 RESEARCH       DALLAS  
7.     30 SALES          CHICAGO  
8.     40 OPERATIONS     BOSTON  
9.    
10. SQL> insert into dept(deptno) values(50);  
11.    
12. 1 row inserted  
13.    
14. SQL> commit;  
15.    
16. Commit complete  
17.    
18. SQL> select * from dept;  
19.    
20. DEPTNO DNAME          LOC  
21. ------ -------------- -------------  
22.     10 ACCOUNTING     NEW YORK01  
23.     20 RESEARCH       DALLAS  
24.     30 SALES          CHICAGO  
25.     40 OPERATIONS     BOSTON  
26.     50                  

SQL> 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):

[c-sharp] view plain copy print ?

1. SQL> select * from dept;  
2.    
3. DEPTNO DNAME          LOC  
4. ------ -------------- -------------  
5.     10 ACCOUNTING     NEW YORK01  
6.     20 RESEARCH       DALLAS  
7.     30 SALES          CHICAGO  
8.     40 OPERATIONS     BOSTON  

SQL> 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):

[c-sharp] view plain copy print ?

1. SQL> declare  
2.   2  pragma AUTONOMOUS_TRANSACTION;  
3.   3  begin  
4.   4  update dept set loc = loc || '-XXX' where deptno=20;  
5.   5  end;  
6.   6  /  
7.    
8. declare  
9. pragma AUTONOMOUS_TRANSACTION;  
10. begin  
11. update dept set loc = loc || '-XXX' where deptno=20;  
12. end;  
13.    
14. ORA-06519: active autonomous transaction detected and rolled back  
15. ORA-06512: at line 6  
16.    
17. SQL> select * from dept;  
18.    
19. DEPTNO DNAME          LOC  
20. ------ -------------- -------------  
21.     10 ACCOUNTING     NEW YORK01  
22.     20 RESEARCH       DALLAS  
23.     30 SALES          CHICAGO  
24.     40 OPERATIONS     BOSTON  
25.    
26. SQL>   

SQL> 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):

[c-sharp] view plain copy print ?

1. SQL> commit;  
2.    
3. Commit complete  
4.    
5. SQL> select * from dept;  
6.    
7. DEPTNO DNAME          LOC  
8. ------ -------------- -------------  
9.     10 ACCOUNTING     NEW YORK01  
10.     20 RESEARCH       DALLAS  
11.     30 SALES          CHICAGO  
12.     40 OPERATIONS     BOSTON  
13.     50