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

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

http://www.dbsnake.net/oracle-cr-behavior-change.html

昨天看了崔华的帖子，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> @ver

BANNER

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

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点：我个人认为是否会出现一些“错误”还是有待观察，毕竟这种方式与原来的不同。