[20210304]关于11g一致性读取的测试.txt

--//链接：http://blog.itpub.net/267265/viewspace-2760454/ => [20210301]延迟显示输出.txt
--//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就不做一致读了.
--//这个提前条件就是索引要唯一.
--//自己好奇补充一些测试一致性读取的情况。

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)
   RETURN NUMBER
AS
BEGIN
   sys.DBMS_LOCK.sleep (seconds);
   RETURN seconds;
END;
/

2.建立测试环境：
SCOTT@book> create table t as select rownum id1,rownum id2,lpad(rownum||'x',10,rownum||'x') vc from dual connect by level<=10;
Table created.

SCOTT@book> create unique index i_t_id1 on t(id1);
Index created.

SCOTT@book> create  index i_t_id2 on t(id2);
Index created.
--//分析略.

3.测试:
--//session 1:
SCOTT@book> select * from t where id1=4 and sleep(12)=12 union all select * from t where id2=4 and sleep(1)=12;

--//session 2,以下命令最好事先输入,避免12秒内无法完成:
SCOTT@book> update t set vc=upper(vc) where id1=4;
1 row updated.

SCOTT@book> commit ;
Commit complete.

--//session 1:
SCOTT@book> select * from t where id1=4 and sleep(12)=12 union all select * from t where id2=4 and sleep(12)=12;
       ID1        ID2 VC
---------- ---------- --------------------
         4          4 4X4X4X4X4X
         4          4 4X4X4X4X4X
--//你可以发现vc输出是大写.而按照我前面的测试应该第1行vc应该是小写，第2行输出的vc是大写。

4.继续测试：
--//条件反过来查询呢？
--//session 1:
SCOTT@book> select * from t where id2=3 and sleep(12)=12 union all select * from t where id1=3 and sleep(12)=12;

--//session 2:
SCOTT@book> update t set vc=upper(vc) where id1=3;
1 row updated.

SCOTT@book> commit ;
Commit complete.

--//session 1:
SCOTT@book> select * from t where id2=3 and sleep(12)=12 union all select * from t where id1=3 and sleep(12)=12;
       ID1        ID2 VC
---------- ---------- --------------------
         3          3 3x3x3x3x3x
         3          3 3x3x3x3x3x

--//再次查询：
SCOTT@book> select * from t where id2=3 and sleep(1)=1 union all select * from t where id1=3 and sleep(1)=1;
       ID1        ID2 VC
---------- ---------- --------------------
         3          3 3X3X3X3X3X
         3          3 3X3X3X3X3X

--//11g这种改动可能会出现许多奇怪的情况，也许还可以测试更多的情况，不想做了。