Oracle PL/SQL之SET TRANSACTION READ ONLY（事务隔离性）

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

?

?