Oracle数据库读一致性的概念,【讨论话题】一致性读（Read Consistency）的深入解析...

本帖最后由 guoyJoe 于 2013-6-3 18:00 编辑

QQ图片20130522085046.jpg (52.07 KB, 下载次数: 211)

2013-5-22 08:51 上传

一致性读在Oracle中是一个非常重要的概念， 大家一起跟着我先来做下面的一个实验：

gyj@OCM> create table gyj (id int,name varchar2(10));

Table created.

gyj@OCM> insert into gyj values(1,'GGGGGG');

1 row created.

gyj@OCM> commit;

Commit complete.

gyj@OCM> select * from gyj;

ID NAME

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

1 GGGGGG

gyj@OCM> var x refcursor

gyj@OCM> exec open :x for select * from gyj;

PL/SQL procedure successfully completed.

gyj@OCM> update gyj set name='YYYYYY' where id=1;

1 row updated.

gyj@OCM> commit;

Commit complete.

gyj@OCM> update gyj set name='JJJJJJ' where id=1;

1 row updated.

gyj@OCM> commit;

Commit complete.

gyj@OCM> print :x

ID NAME

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

1 GGGGGG

能真正看懂为什么print所显示的这个结果是GGGGGG，而并不是JJJJJJ，那就说明你对一致性读已了解过了。

再往下分析深入分析：做一系列的dump:

gyj@OCM> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj;

ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

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

1 JJJJJJ                                        6                                  151

sys@OCM> alter system dump datafile 6 block 151;System altered.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0004.010.00000211 0x00c02d8a.0090.20 --U- 1 fsc 0x0000.00100118

0x02 0x0003.00d.000002db 0x00c02d16.00bc.06 C--- 0 scn 0x0000.00100109

bdba: 0x01800097

data_block_dump,data header at 0xd85664

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x00d85664

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f8b

avsp=0x1f77

tosp=0x1f77

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f8b

block_row_dump:

tab 0, row 0, @0x1f8b

tl: 13 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 6] 4a 4a 4a 4a 4a 4a复制代码gyj@OCM> select UTL_RAW.CAST_TO_VARCHAR2(replace('4a 4a 4a 4a 4a 4a',' ')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(REPLACE('4A4A4A4A4A4A',''))

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

JJJJJJ

sys@OCM>alter system dump datafile 3 block 11658;

System altered.

*-----------------------------

* Rec #0x20 slt: 0x10 objn: 74580(0x00012354) objd: 74580 tblspc: 7(0x00000007)

* Layer: 11 (Row) opc: 1 rci 0x00

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000Ext idx: 0

flg2: 0

*-----------------------------

uba: 0x00c02d8a.0090.1d ctl max scn: 0x0000.000ffd42 prv tx scn: 0x0000.000ffd5a

txn start scn: scn: 0x0000.00100109 logon user: 91

prev brb: 12594569 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: L itl: xid: 0x0007.003.0000021e uba: 0x00c02c50.00a7.3a

flg: C--- lkc: 0 scn: 0x0000.001000f5

Array Update of 1 rows:

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191

ncol: 2 nnew: 1 size: 0

KDO Op code: 21 row dependencies Disabled

xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01800097 hdba: 0x01800092

itli: 1 ispac: 0 maxfr: 4858

vect = 3

col 1: [ 6] 59 59 59 59 59 59复制代码gyj@OCM>select UTL_RAW.CAST_TO_VARCHAR2(replace('59 59 59 59 59 59',' ')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(REPLACE('595959595959',''))

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

YYYYYY

gyj@OCM>alter system dump datafile 3 block 11542;

System altered.

*-----------------------------

* Rec #0x6 slt: 0x0d objn: 74580(0x00012354) objd: 74580 tblspc: 7(0x00000007)

* Layer: 11 (Row) opc: 1 rci 0x00

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000Ext idx: 0

flg2: 0

*-----------------------------

uba: 0x00c02d16.00bc.04 ctl max scn: 0x0000.000fff10 prv tx scn: 0x0000.000fff16

txn start scn: scn: 0x0000.001000f5 logon user: 91

prev brb: 12594451 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x03 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: Z

Array Update of 1 rows:

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191

ncol: 2 nnew: 1 size: 0

KDO Op code: 21 row dependencies Disabled

xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01800097 hdba: 0x01800092

itli: 2 ispac: 0 maxfr: 4858

vect = 3

col 1: [ 6] 47 47 47 47 47 47复制代码gyj@OCM> select UTL_RAW.CAST_TO_VARCHAR2(replace('47 47 47 47 47 47',' ')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(REPLACE('474747474747',''))

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

GGGGGG

要看懂上面这些dump之间的关系，不仅需要了解数据块的结构、UNDO块的结构，更需要了解一致性读的内部的机制，好！我们先来看下面几个问题？

讨论话题：1、描述一致性读的概念？

2、解释ORA-01555快照过旧的原因，利用实验模拟重现ORA-01555错误？

3、说说与一致性读相关的一些重要数据字典视图？

讨论时间：2013.5.23--2013.6.4

讨论奖品：活动结束后将会抽取5-10名会员赠送ITPUB独家编写的《数据库设计与开发规范》一本

数据库设计与开发规范-01_副本.jpg (32.09 KB, 下载次数: 170)

2013-5-23 08:36 上传

chinaliukai   mlx_861201   chong023    fjkingx   ydjh460   wolfop   syzxlyx    ses19828  maohaiqing0304   ccceleven