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

最新推荐文章于 2025-01-04 00:48:43 发布

最新推荐文章于 2025-01-04 00:48:43 发布 · 119 阅读

文章标签：

本文探讨了在Oracle数据库中使用SET TRANSACTION READ ONLY指令实现事务隔离性，防止并发事务影响当前会话的操作。通过实例展示了如何在不同会话中进行读取、插入和更新操作，确保了查询结果的一致性和事务的独立性。同时，强调了undo空间配置对避免快照过旧错误的重要性。

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

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

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

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

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