oracle维护读写一致,[20130902]Oracle 11G数据库的一致读性读取行为的改变.txt

[20130902]Oracle 11G数据库的一致读性读取行为的改变.txt

昨天看了崔华的帖子，blog中给出一个例子，例子如下：

create or replace procedure p_demo_cr_read_change is

cursor c1 is select * from emp where empno=7369;

employee_rec emp%rowtype;

begin

open c1;

dbms_lock.sleep(60);

fetch c1 into employee_rec;

while (c1%found) loop

dbms_output.put_line('employee id: ' || employee_rec.empno);

dbms_output.put_line('employee name: ' || employee_rec.ename);

fetch c1 into employee_rec;

end loop;

close c1;

end p_demo_cr_read_change;

/

按照以前的理解，如果在open 光标后，结果已经确定，即使别的回话修改了 empno=7369的ename值，显示的结果

select * from emp where empno=7369;EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

但从Oracle 11g开始，Oracle更改了在某些特定条件一致读的行为，这使得一些看起来不合常理的行为在Oracle 11g以及后续的版本

中得以出现，即在Oracle 11g以及后续的版本中，当满足一定的条件时，我们就可以马上读到commit后的数据，而不再存在以前的那种

一致读的行为。

Oracle将这种改动称为"RowCR Optimization"，Oracle简单的描述了什么是RowCR Optimization：A brief overview of this

optimization is that we try to avoid rollbacks while constructing a CR block if the present block has no uncommitted

changes.这里的avoid rollback，意味着在满足特定的条件时，Oracle就不做一致读了。

RowCR Optimization通过隐含参数"_row_cr"来控制，但遗憾的是，Oracle在11g及其后续的版本中将这个参数的默认值改成了TRUE，

这意味着上述这种"在满足特定的条件时，Oracle就不做一致读"的行为在Oracle 11g及其后续的版本中在默认情况下就已经被开启了，

这也许有些激进。国内的某银行在升级到Oracle 11g后就出现了一致读的问题，在这次的CAB技术峰会上，Oracle负责高可用性研发的VP

Wei Hu承认："我们在默认情况下开启了RowCR Optimization，这也许是不恰当的。"

这些可能导致与早期的理解不一致，重复他的例子来说明问题：

1. 测试环境:

SCOTT@test> @verBANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--建立过程p_demo_cr_read_change，忽略,执行脚本在上面。

SCOTT@test> select empno,ename from emp where empno=7369;EMPNO ENAME---------- ----------7369 SMITH

2.开始测试：

--打开回话1：

set serveroutput on

exec p_demo_cr_read_change

--打开回话2：

update emp set ename='lfree' where empno=7369;

commit ;

--回到回话1：

employee id: 7369

employee name: lfree

PL/SQL procedure successfully completed.

可以发现回话1没有做常规的一致读，而是马上读到了commit后的数据，即此时已经发生了RowCR Optimization。

3.恢复原值，并且删除emp表的主键PK_EMP：

--打开回话2：

update emp set ename='SMITH' where empno=7369;

commit ;

alter table emp drop constraint pk_emp;

select empno,ename from emp where empno=7369;

EMPNO ENAME

---------- ----------

7369 SMITH

--然后重复测试:

--打开回话1：

set serveroutput on

exec p_demo_cr_read_change

--打开回话2：

update emp set ename='lfree' where empno=7369;

commit ;

--回到回话1：

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed.

--这个结果就是正常的一致性读取。此时并没有做RowCR Optimization，即并没有马上读到commit后的数据，这说明当我们把

--列empno上的主键drop掉后(即drop掉empno上的唯一性索引后)，Oracle并没有做RowCR Optimization，而是做了常规的一致读。

4.在列empno上创建一个名为i_emp_empno的非唯一性索引，还原原值：

update emp set ename='SMITH' where empno=7369;

commit ;

create  index i_emp_empno on emp(empno);

--索引为不唯一索引。

select empno,ename from emp where empno=7369;EMPNO ENAME---------- ----------7369 SMITH

--然后重复测试:

--回到回话1：

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed.

--同样也没有做RowCR Optimization，说明如果索引非唯一，Oracle也没有做RowCR Optimization，而是做了常规的一致读。

5.在列empno上创建一个名为i_emp_empno的唯一性索引，还原原值：

update emp set ename='SMITH' where empno=7369;

commit ;

drop index i_emp_empno ;

create  unique index i_emp_empno on emp(empno);

select empno,ename from emp where empno=7369;EMPNO ENAME---------- ----------7369 SMITH

--然后重复测试:

--回到回话1：

employee id: 7369

employee name: lfree

PL/SQL procedure successfully completed.

--列empno存在唯一性索引的情形下，Oracle选择做了RowCR Optimization。

6.还原原值，并且修改系统隐含参数"_row_cr"=false：

update emp set ename='SMITH' where empno=7369;

commit ;

alter system set "_row_cr" = false scope=memory;

--然后重复测试:

--回到回话1：

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed.

--可以即使列empno上存在唯一性索引，Oracle此时也没有做RowCR Optimization，即并没有马上读到commit后的数据，这说明隐含参数

--"_row_cr"确实能控制RowCR Optimization的开启与否。

Oracle在描述RowCR Optimization时曾经提到其生效的前提条件为：A brief overview of this optimization is that we try to

avoid rollbacks while constructing a CR block if the present block has no uncommitted changes.

7.还原原值，并且修改系统隐含参数"_row_cr"=true：

update emp set ename='SMITH' where empno=7369;

commit ;

alter system set "_row_cr" = true scope=memory;

--打开回话1：

set serveroutput on

exec p_demo_cr_read_change

--打开回话2：

update emp set ename='lfree' where empno=7369;

commit ;

update emp set ename='lfree1' where empno=7369;

--回到回话1：

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed.

--显示Oracle此时做了常规的一致读，即在被访问的数据块存在未commit的数据的情形下不会发生RowCR Optimization。

--注：我这里测试跟作者不一样，估计版本问题11.2.0.3修复了这个错误，作者的版本是11.2.0.1.

8.还原原值,继续测试：我自己补充的

update emp set ename='SMITH' where empno=7369;

commit ;

--打开回话1：

set serveroutput on

exec p_demo_cr_read_change

--打开回话2：修改empno=7369为8001

update emp set empno =8001 where empno=7369;

commit ;

--回到回话1：

employee id: 7369

employee name: SMITH

PL/SQL procedure successfully completed.

--显示Oracle此时做了常规的一致读。

最后，我们来总结一下，从上述测试过程我们可以得到如下结论：

在Oracle 11g以及后续的版本中，默认情况下(即隐含参数"_row_cr"的值为TRUE的情况下)，如果是通过唯一性索引去访问数据，

则我们就可以马上读到commit后的数据，而不再存在以前的那种一致读的行为。

补充1点：我个人认为是否会出现一些“错误”还是有待观察，毕竟这种方式与原来的不同。