北京网站建设找华网天下,360营销平台,公司网站免费建立,网站建设品牌策划SET TRANSACTION READ ONLY来实现事物级别的一致性。一个事物所有语句读到的数据都是一致的。我们开始试验一#xff0c;模拟语句级别读一致性。第一个session使用显示打开一个游标模拟数据读#xff0c;同时在游标读数据的过程中#xff0c;启动另外一个session更改数据模拟语句级别读一致性。第一个session使用显示打开一个游标模拟数据读同时在游标读数据的过程中启动另外一个session更改数据我可以看到另外一个session对数据的更改并不会改变到第一个session的读。这就是语句级别的读一致性。启动一个session连接数据库Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0Connected as cboSQL set serveroutput on;SQLSQL create table test(id number,name varchar2(10));Table createdSQL insert into test values(1,a);1 row insertedSQL insert into test values(2,b);1 row insertedSQL commit;Commit completeSQLSQL declare2 cursor cur is select * from test;3 begin4 for rec in cur5 loop6 dbms_output.put_line(rec.name);7 dbms_lock.sleep(10);--中间等待另外一个session启动并执行更新数据操作8 end loop;9 end;10 /abPL/SQL procedure successfully completedSQL在执行游标打印输出的时候同时启动另外一个进程,执行更新数据操作Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0Connected as cboSQL set serveroutput on;SQLSQL create table test(id number,name varchar2(10));Table createdSQL insert into test values(1,a);1 row insertedSQL insert into test values(2,b);1 row insertedSQL commit;Commit completeSQLSQL declare2 cursor cur is select * from test;3 begin4 for rec in cur5 loop6 dbms_output.put_line(rec.name);7 dbms_lock.sleep(10);8 end loop;9 end;10 /abPL/SQL procedure successfully completedSQL下面我们开始试验二模拟事务级别读一致性。首先启动一个SESSION,读一次数据SQL SET TRANSACTION READ ONLY;Transaction setSQL select * from test;ID NAME---------- ----------1 a2 bbbb接下来我们启动另外一个session执行更新数据操作SQL update test set name123456;2 rows updatedSQL commit;Commit complete最后我们回到第一session查看再次查看数据SQL select * from test;ID NAME---------- ----------1 a2 bbbb我们会发现读出的数据并没有发生改变。所以在设置了SET TRANSACTION READ ONLY后一个事务前后语句读取的数据不会因为其他seesion对数据的更新而改变。另外SET TRANSACTION READ ONLY是有一定的条件限制的官方文档说明如下TheSETTRANSACTIONstatement must be thefirst statement of a new transaction; if any DML statements (includingqueries) or other non-DDL statements (such asSET ROLE) precede aSET TRANSACTIONREADONLYstatement, an error is returned. Once aSETTRANSACTIONREADONLYstatement successfully executes, onlySELECTFORUPDATEclause),COMMIT,ROLLBACK, or non-DML statements (such asSETROLE,ALTERSYSTEM,LOCKTABLE) are allowed in the transaction. Otherwise, an error is returned. ACOMMIT,ROLLBACK,or DDL statement terminates the read-only transaction; a DDL statementcauses an implicit commit of the read-only transaction and commits inits own transaction.
