Oracle数据库中一致读行为的改变

我们都知道,在Oracle数据库中是commit的数据我们读不到,commit后的数据我们也不一定能马上读到,这其中的后者当然是因为Oracle数据库中久负盛名的一致读行为的存在。

但从Oracle 11g开始,Oracle更改了在某些特定条件一致读的行为,这使得一些看起来不合常理的行为在Oracle 11g以及后续的版本中得以出现,即在Oracle 11g以及后续的版本中,当满足一定的条件时,我们就可以马上读到commit后的数据,而不再存在以前的那种一致读的行为

Oracle将这种改动称为“RowCR Optimization”Oracle简单的描述了什么是RowCR OptimizationA 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”来控制,但遗憾的是,Oracle11g及其后续的版本中将这个参数的默认值改成了TRUE,这意味着上述这种在满足特定的条件时,Oracle就不做一致读的行为在Oracle 11g及其后续的版本中在默认情况下就已经被开启了,这也许有些激进。国内的某银行在升级到Oracle 11g后就出现了一致读的问题,在这次的CAB技术峰会上,Oracle负责高可用性研发的VP Wei Hu承认:我们在默认情况下开启了RowCR Optimization,这也许是不恰当的。

 

说了这么多背景,我们现在来看一个RowCR Optimization的实例:

这里用的Oracle数据库版本为11.2.0.1,我们在其中创建了如下的存储过程p_demo_cr_read_change

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> create or replace procedure p_demo_cr_read_change is

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

  3    employee_rec emp%rowtype;

  4  begin

  5    open c1;

  6    dbms_lock.sleep(60);

  7 

  8    fetch c1 into employee_rec;

  9    while (c1%found) loop

 10 

 11       dbms_output.put_line(’employee id: ‘ || employee_rec.empno);

 12       dbms_output.put_line(’employee name: ‘ || employee_rec.ename);

 13 

 14      fetch c1 into employee_rec;

 15    end loop;

 16    close c1;

 17  end p_demo_cr_read_change;

 18  /

 

Procedure created

 

上述存储过程针对的是SCOTT用户下的表EMP,在列EMPNO上存在一个名为PK_EMP的主键:

SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;

 

  CREATE TABLE “SCOTT”.”EMP”

   (       “EMPNO” NUMBER(4,0),

            “ENAME” VARCHAR2(10),

            ……省略显示部分内容

            “DEPTNO” NUMBER(2,0),

             CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

 ……省略显示部分内容

FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE “USERS”

 

现在表EMPempno=7369的记录所对应的ename列的值是“SMITH”:

SQL> select empno,ename from emp where empno=7369;

 

EMPNO ENAME

—–    ———-

 7369  SMITH

 

我们现在开启两个Session,分别为Session 1Session 2,在Session 1中执行上述存储过程p_demo_cr_read_change,同时在Session 2中利用Session 1的存储过程p_demo_cr_read_changeopen c1fetch之间间隔的那60秒缓冲时间对表EMPempno=7369的记录做修改操作,即在这60秒缓冲时间内将表EMPempno=7369的记录所对应的ename列的值从“SMITH”改为“CUIHUA”并commit,这样通过观察Session 1中存储过程p_demo_cr_read_change最后的输出结果即可验证RowCR Optimization的行为,具体来说就是如下这样:

1Session 1中的最终输出结果如果是“SMITH”,则说明发生了常规的一致读,没有做RowCR Optimization

2Session 1中的最终输出结果如果是“CUIHUA”,则说明已经做了RowCR Optimization,没有做常规的一致读;

 

现在我们来实际测试一下,在Session 1中执行p_demo_cr_read_change

Session 1

SQL> set serveroutput on

SQL> exec p_demo_cr_read_change

……这里会等待60秒,然后才会显示输出结果

 

然后在60秒的缓冲时间段内去Session 2中做如下修改操作:

Session 2

SQL> update emp set ename=’CUIHUA’ where empno=7369;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename from emp where empno=7369;

 

EMPNO ENAME

—–    ———-

 7369  CUIHUA

 

等过了60秒的缓冲时间后,我们再回到Session 1,此时看到了如下输出结果:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: CUIHUA

 

PL/SQL procedure successfully completed

 

上述Session 1的最终输出结果显示Oracle此时确实没有做常规的一致读,而是马上读到了commit后的数据,即此时已经发生了RowCR Optimization

 

我们现在去Session 2中把表EMPempno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且drop掉表EMP上的主键PK_EMP

Session 2

SQL> update emp set ename=’SMITH’ where empno=7369;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename from emp where empno=7369;

 

EMPNO ENAME

—–    ———-

 7369  SMITH

 

SQL> alter table emp drop constraint pk_emp;

 

Table altered

 

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程, Session 1的最终输出结果为如下所示:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: SMITH

 

PL/SQL procedure successfully completed

 

    上述Session 1的最终输出结果显示Oracle此时并没有做RowCR Optimization,即并没有马上读到commit后的数据,这说明当我们把列empno上的主键drop掉后(即dropempno上的唯一性索引后),Oracle并没有做RowCR Optimization,而是做了常规的一致读。

 

我们现在去Session 2中把表EMPempno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且在列empno上创建一个名为idx_emp_empno的非唯一性索引:

Session 2

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> create index idx_emp_empno on emp(empno);

 

Index created

 

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程, Session 1的最终输出结果为如下所示:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: SMITH

 

PL/SQL procedure successfully completed

 

   上述Session 1的最终输出结果显示Oracle此时也没有做RowCR Optimization,即并没有马上读到commit后的数据,这说明即使列empno上存在非唯一性索引,Oracle也没有做RowCR Optimization,而是做了常规的一致读。

 

我们现在去Session 2中把表EMPempno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且在列empno上创建一个名为idx_uk_emp_empno的唯一性索引:

Session 2

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> drop index idx_emp_empno;

 

Index dropped

 

SQL> create unique index idx_uk_emp_empno on emp(empno);

 

Index created

 

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程, Session 1的最终输出结果为如下所示:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: CUIHUA

 

PL/SQL procedure successfully completed

 

   上述Session 1的最终输出结果显示Oracle此时没有做常规的一致读,而是马上读到了commit后的数据,即在列empno存在唯一性索引的情形下,Oracle选择做了RowCR Optimization

 

我们现在去Session 2中把表EMPempno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且将隐含参数“_row_cr”的值改为FALSE

Session 2

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> alter system set “_row_cr” = false scope=both;

 

System altered

 

SQL> select name,value from sys.all_parameters where name like ‘_row_cr%’;

 

NAME                 VALUE

——————–   ——————–

_row_cr               FALSE

 

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程, Session 1的最终输出结果为如下所示:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: SMITH

 

PL/SQL procedure successfully completed

 

上述Session 1的最终输出结果显示即使列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.

 

但经过我们测试,上述这种说法是不严谨的:

测试过程为如下所示:

我们现在去Session 2中把表EMPempno=7369的记录所对应的ename列的值恢复成原先的“SMITH”并且将隐含参数“_row_cr”的值改为TRUE

Session 2

……这里省略将ename列的值恢复成原先的“SMITH”的过程

SQL> alter system set “_row_cr” = true scope=both;

 

System altered

 

SQL> select name,value from sys.all_parameters where name like ‘_row_cr%’;

 

NAME                 VALUE

——————–   ——————–

_row_cr                TRUE

 

从如下查询结果中我们可以看到,表EMP13条记录全部在datafile 4block 151这个数据块中:

SQL> select empno, ename, dbms_rowid.rowid_relative_fno(rowid) || ‘_’ || dbms_rowid.rowid_block_number(rowid) location from emp;

 

EMPNO ENAME      LOCATION

—– ———- ——————–

 7369 SMITH      4_151

 7499 ALLEN      4_151

 7521 WARD       4_151

 7566 JONES      4_151

 7654 MARTIN     4_151

 7698 BLAKE      4_151

 7782 CLARK      4_151

 7788 SCOTT      4_151

 7844 TURNER     4_151

 7876 ADAMS      4_151

 7900 JAMES      4_151

 7902 FORD       4_151

 7934 MILLER     4_151

 

13 rows selected

 

然后我们再次以同样的方法重复之前测试RowCR Optimization的过程,只不过这一次在Session 2中做了如下的修改操作(即构造了datafile 4block 151这个数据块存在未commit数据的情形):

Session 2

SQL> update emp set ename=’CUIHUA’ where empno=7369;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select empno,ename from emp where empno=7369;

 

EMPNO ENAME

——-   ———-

7369  CUIHUA

 

SQL> update emp set ename=’CUIHUA1′ where empno=7499;

 

1 row updated

 

等过了60秒的缓冲时间后,我们再回到Session 1,此时看到了如下输出结果:

Session 1

SQL> exec p_demo_cr_read_change

 

employee id: 7369

employee name: CUIHUA

 

PL/SQL procedure successfully completed

 

上述Session 1的最终输出结果显示Oracle此时没有做常规的一致读,而是马上读到了commit后的数据,即在被访问的数据块存在未commit的数据的情形下也依然发生了RowCR Optimization

 

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

Oracle 11g以及后续的版本中,默认情况下(即隐含参数“_row_cr”的值为TRUE的情况下),如果是通过唯一性索引去访问数据,则我们就可以马上读到commit后的数据,而不再存在以前的那种一致读的行为