所谓读一致性是说在某一个级别上前后读数据的一致性。ORACLE缺省提供的是语句级别的读一致性。也就是说一个SELECT语句所读到的所有数据肯定是一个时间点的数据状态。在读的过程中,其他session对这个表的数据的更改都不会被读入。另外我们可以通过设置
SET TRANSACTION READ ONLY来实现事物级别的一致性。一个事物所有语句读到的数据都是一致的。
我们开始试验一,模拟语句级别读一致性。第一个session使用显示打开一个游标模拟数据读,同时在游标读数据的过程中,启动另外一个session更改数据,我可以看到另外一个session对数据的更改,并不会改变到第一个session的读。这就是语句级别的读一致性。
启动一个session连接数据库:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as cbo
SQL> set serveroutput on;
SQL>
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);--中间等待另外一个session启动并执行更新数据操作
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
SQL>
在执行游标打印输出的时候同时启动另外一个进程,执行更新数据操作:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as cbo
SQL> set serveroutput on;
SQL>
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
SQL>
下面我们开始试验二,模拟事务级别读一致性。
首先启动一个SESSION,读一次数据:
SQL> SET TRANSACTION READ ONLY;
Transaction set
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
接下来我们启动另外一个session,执行更新数据操作:
SQL> update test set name='123456';
2 rows updated
SQL> commit;
Commit complete
最后我们回到第一session查看再次查看数据:
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
我们会发现读出的数据并没有发生改变。所以在设置了SET TRANSACTION READ ONLY后,一个事务前后语句读取的数据不会因为其他seesion对数据的更新而改变。
另外SET TRANSACTION READ ONLY是有一定的条件限制的,官方文档说明如下:
The SET TRANSACTION statement must be the first statement of a new transaction; if any DML statements (including queries) or other non-DDL statements (such as SET ROLE) precede a SET TRANSACTION READ ONLY statement, an error is returned. Once a SET TRANSACTION READ ONLY statement successfully executes, only SELECTFOR UPDATE clause), COMMIT, ROLLBACK, or non-DML statements (such as SET ROLE, ALTER SYSTEM, LOCK TABLE) are allowed in the transaction. Otherwise, an error is returned. A COMMIT, ROLLBACK, or DDL statement terminates the read-only transaction; a DDL statement causes an implicit commit of the read-only transaction and commits in its own transaction.
我们开始试验一,模拟语句级别读一致性。第一个session使用显示打开一个游标模拟数据读,同时在游标读数据的过程中,启动另外一个session更改数据,我可以看到另外一个session对数据的更改,并不会改变到第一个session的读。这就是语句级别的读一致性。
启动一个session连接数据库:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as cbo
SQL> set serveroutput on;
SQL>
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);--中间等待另外一个session启动并执行更新数据操作
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
SQL>
在执行游标打印输出的时候同时启动另外一个进程,执行更新数据操作:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as cbo
SQL> set serveroutput on;
SQL>
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
SQL>
下面我们开始试验二,模拟事务级别读一致性。
首先启动一个SESSION,读一次数据:
SQL> SET TRANSACTION READ ONLY;
Transaction set
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
接下来我们启动另外一个session,执行更新数据操作:
SQL> update test set name='123456';
2 rows updated
SQL> commit;
Commit complete
最后我们回到第一session查看再次查看数据:
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
我们会发现读出的数据并没有发生改变。所以在设置了SET TRANSACTION READ ONLY后,一个事务前后语句读取的数据不会因为其他seesion对数据的更新而改变。
另外SET TRANSACTION READ ONLY是有一定的条件限制的,官方文档说明如下:
The SET TRANSACTION statement must be the first statement of a new transaction; if any DML statements (including queries) or other non-DDL statements (such as SET ROLE) precede a SET TRANSACTION READ ONLY statement, an error is returned. Once a SET TRANSACTION READ ONLY statement successfully executes, only SELECTFOR UPDATE clause), COMMIT, ROLLBACK, or non-DML statements (such as SET ROLE, ALTER SYSTEM, LOCK TABLE) are allowed in the transaction. Otherwise, an error is returned. A COMMIT, ROLLBACK, or DDL statement terminates the read-only transaction; a DDL statement causes an implicit commit of the read-only transaction and commits in its own transaction.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/77270/viewspace-592459/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/77270/viewspace-592459/