Tuesday Mar 19, 2013

DML UPDATE/DELETE与CR一致性读的秘密

这个问题源于OTN中文论坛的一个帖子<大事务中的更新丢失问题>

环境为Oracle 10.2.0.4 on Linux x64
有一个大表,百万级,col1字段全为0
t1 事务A启动,把所有记录col2全更新为1
t2 事务B启动,根据主键,把一条记录更新为2,然后commit
t3 事务A执行完成,并COMMIT
t4 查询此表,发现col1全部为1,事务B的更新丢失了。
这是为什么呢,其中逻辑是怎样的
谢谢!

对于这个问题我想说明的是对于事务transaction 而言Oracle同样提供读一致性,称为transaction-level read consistency:

The database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began.

Oracle Database Concepts
11g Release 2 (11.2)
Part Number E10713-04

为了证明和演示该事务级别的读一致性,我设计了以下演示:

有一张2个列的大表(T1,T2), 其中分别有 T1=600000,T2=’U2′ 和  T1=900000和 T2=’U1′的 2行数据,T1为主键。

在A)时刻,Session A使用SELECT .. FOR UPDATE锁住T1=600000的这一行

在之后的B)时刻,Session B尝试update TAB set t2=’U3′ where t2=’U2′ 即更新所有T2=’U2′的数据行,但是因为 T1=600000,T2=’U2′这一行正好被Session A锁住了,所以Session B会一直等待’enq: TX – row lock contention’;T1=900000和 T2=’U1′的数据行位于Session B处理 T1=600000,T2=’U2′行等待之后才能处理到的数据块中。

在之后的C)时刻,Session C更新update TAB set t2=’U2′ where t1=900000;并commit, 即将T1=900000和 T2=’U1′更新为 T1=900000和 T2=’U2′,这样就符合Session B 更新Update的条件t2=’U2′了。

在D)时刻, Session A执行commit释放锁,Session B得以继续工作,当他处理到T1=900000的记录时存在以下分歧:

1)若update DML满足transaction-level read consistency,则它应当看到的是session B事务开始环境SCN(env SCN)时的块的前镜像,即虽然session C更新了t2=’U2′满足其条件,但是为了一致性,session B仍需要对该行所在数据块做APPLY UNDO,直到满足该session B事务开始时间点的Best CR块,而CR一致镜像中t2=’U1′,不满足Session B的更新条件, 那么session B在整个事务中仅更新一行数据 T1=600000,T2=’U2′,session B only Update One Rows。

2) 若update DML不满足transaction-level read consistency,则session B看到的是当前read commited的镜像,即是Session C已更新并提交的块镜像,此时的记录为T1=900000和 T2=’U2′符合session B的更新条件,则session B要更新2行数据。

我们来看一下实际实验的结果:

构建实验环境:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

create table update_cr tablespace users as select rownum t1, 'MACLEAN     ' T2 
from dual connect by level  update update_cr set t2='U2' where t1=600000;

1 row updated.

SQL> update update_cr set t2='U1' where t1=900000;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from update_cr where t1=600000 or t1=900000;

        T1 T2
---------- ------------
    600000 U2
    900000 U1

在A)时刻,Session A使用SELECT .. FOR UPDATE锁住T1=600000的这一行:

session A:

SQL> select * from update_cr where t1=600000 for update;

        T1 T2
---------- ------------
    600000 U2

在之后的B)时刻,Session B尝试update TAB set t2=’U3′ where t2=’U2′ 即更新所有T2=’U2′的数据行,但是因为 T1=600000,T2=’U2′这一行正好被Session A锁住了,所以Session B会一直等待’enq: TX – row lock contention’;T1=900000和 T2=’U1′的数据行位于Session B处理 T1=600000,T2=’U2′行等待之后才能处理到的数据块中。

session B:

SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

SQL> alter session set events '10046 trace name context forever,level 8 : 10201 trace name context forever,level 10';

Session altered.

SQL> update update_cr set t2='U3' where t2='U2';

在之后的C)时刻,Session C更新update TAB set t2=’U2′ where t1=900000;并commit, 即将T1=900000和 T2=’U1′更新为 T1=900000和 T2=’U2′,这样就符合Session B 更新Update的条件t2=’U2′了。

session C:

SQL> select * from update_cr where t1=900000;

        T1 T2
---------- ------------
    900000 U1

SQL> update update_cr set t2='U2' where t1=900000;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

之后session A 执行 commit;session B得以继续update,得到实验的结果:

session A 执行 commit;

session B;

SQL> update update_cr set t2='U3' where t2='U2';

1 row updated.

以看到以上实验的结果 update仅更新了一行数据,证明了观点1″若update DML满足transaction-level read consistency,则它应当看到的是session B事务开始环境SCN(env SCN)时的块的前镜像,即虽然session C更新了t2=’U2′满足其条件,但是为了一致性,session B仍需要对该行所在数据块做APPLY UNDO,直到满足该session B事务开始时间点的Best CR块,而CR一致镜像中t2=’U1′,不满足Session B的更新条件, 那么session B在整个事务中仅更新一行数据 T1=600000,T2=’U2′,session B only Update One Rows。”的正确性。

即update/delete之类的DML在Oracle中满足transaction-level read consistency,保证其所”看到的”是满足事务开始时间点读一致性的Consistent Read,这也是为什么DML会产生Consistent Read的原因之一。

我们回过头来看一下 上面Session B产生的10201 undo apply和10046 trace的内容,可以发现更多内容:

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from update_cr where t1=600000 or t1=900000;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                2589                                    4
                                3558                                    4

WAIT #139924171154784: nam='db file sequential read' ela= 19504 file#=3 block#=128 blocks=1 obj#=0 tim=1258427129863987
Applying CR undo to block 4 : 1000a1d itl entry 03:
xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e
flg: ---- lkc: 1 fsc: 0x0000.00000000
WAIT #139924171154784: nam='db file sequential read' ela= 12957 file#=3 block#=12302 blocks=1 obj#=0 tim=1258427129877291
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e statement num=2 parent xid: 0x0000.000.00000000 st-scn: 0xe404.00000000 hi-scn: 0x2100.00000000 ma-scn: 0x0000.00000000 flg: 0x62515e33)
CRS upd (before): 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eba sfl: 0
CRS upd (after) : 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e cl-scn: 0x0000.00223eba sfl: 0

*** 2009-11-16 22:06:01.253
WAIT #139924171154784: nam='enq: TX - row lock contention' ela= 31358812 name|mode=1415053318 usn<<16 | slot=65552 sequence=631 obj#=76969 tim=1258427161253791
WAIT #139924171154784: nam='db file sequential read' ela= 36051 file#=4 block#=2589 blocks=1 obj#=76969 tim=1258427161290180
WAIT #139924171154784: nam='db file scattered read' ela= 14718 file#=4 block#=2590 blocks=98 obj#=76969 tim=1258427161305684
WAIT #139924171154784: nam='db file scattered read' ela= 26432 file#=4 block#=2690 blocks=126 obj#=76969 tim=1258427161338364
WAIT #139924171154784: nam='db file scattered read' ela= 38289 file#=4 block#=2818 blocks=126 obj#=76969 tim=1258427161384445
WAIT #139924171154784: nam='db file scattered read' ela= 24265 file#=4 block#=2946 blocks=126 obj#=76969 tim=1258427161416302
WAIT #139924171154784: nam='db file scattered read' ela= 21288 file#=4 block#=3074 blocks=126 obj#=76969 tim=1258427161444684
WAIT #139924171154784: nam='db file scattered read' ela= 23840 file#=4 block#=3202 blocks=126 obj#=76969 tim=1258427161476063
WAIT #139924171154784: nam='db file scattered read' ela= 27439 file#=4 block#=3330 blocks=126 obj#=76969 tim=1258427161511429
WAIT #139924171154784: nam='db file scattered read' ela= 24424 file#=4 block#=3458 blocks=126 obj#=76969 tim=1258427161543429
Applying CR undo to block 4 : 1000de6 itl entry 03:
xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34
flg: --U- lkc: 1 fsc: 0x0000.00223ef9
WAIT #139924171154784: nam='db file sequential read' ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
WAIT #139924171154784: nam='db file sequential read' ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223ef8 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 statement num=8192 parent xid: 0x0000.000.0baf3fa0 st-scn: 0x3fa0.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00000000 flg: 0x00000000)
CRS upd (before): 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eff sfl: 0
CRS upd (after) : 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 cl-scn: 0x0000.00223eff sfl: 0
WAIT #139924171154784: nam='db file scattered read' ela= 13029 file#=4 block#=3586 blocks=126 obj#=76969 tim=1258427161572511
WAIT #139924171154784: nam='db file scattered read' ela= 22282 file#=4 block#=3714 blocks=126 obj#=76969 tim=1258427161602324
WAIT #139924171154784: nam='db file sequential read' ela= 6127 file#=4 block#=522 blocks=1 obj#=76969 tim=1258427161615461
WAIT #139924171154784: nam='db file scattered read' ela= 13503 file#=4 block#=3842 blocks=42 obj#=76969 tim=1258427161629323

以上可以看到对T1=600000所在的数据块1000a1d=》datafile 4 259 apply了UNDO,其环境SCN 为scn: 0×0000.00223eb9:

Applying CR undo to block 4 : 1000a1d itl entry 03:
xid: 0×0001.010.00000277 uba: 0x00c0300e.007c.1e
flg: —- lkc: 1 fsc: 0×0000.00000000
WAIT #139924171154784: nam=’db file sequential read’ ela= 12957 file#=3 block#=12302 blocks=1 obj#=0 tim=1258427129877291
CRS upd rd env [0x7f42a284a704]: (scn: 0×0000.00223eb9 xid: 0×0000.000.00000000 uba: 0×00000000.0000.00 statement num=0 parent xid: 0×0000.000.00000000 st-scn: 0×0000.00000000 hi-scn: 0×0000.00000000 ma-scn: 0×0000.00223e78 flg: 0×00000060) undo env [0x7fffe3fe7f60]: (
scn: 0×0000.00223eb9 xid: 0×0001.010.00000277 uba: 0x00c0300e.007c.1e statement num=2 parent xid: 0×0000.000.00000000 st-scn: 0xe404.00000000 hi-scn: 0×2100.00000000 ma-scn: 0×0000.00000000 flg: 0x62515e33)
CRS upd (before): 0xa5fe9198 cr-scn: 0×0000.00223eb9 xid: 0×0000.000.00000000 uba: 0×00000000.0000.00 cl-scn: 0×0000.00223eba sfl: 0
CRS upd (after) : 0xa5fe9198 cr-scn: 0×0000.00223eb9 xid: 0×0001.010.00000277 uba: 0x00c0300e.007c.1e cl-scn: 0×0000.00223eba sfl: 0

对于T1=900000的数据行所在块1000de6=》 datafile 4 3558 block同样apply了UNDO,其环境SCN均为00223eb9

Applying CR undo to block 4 : 1000de6 itl entry 03:
xid: 0×0003.01f.00000345 uba: 0x00c03092.008f.34
flg: –U- lkc: 1 fsc: 0×0000.00223ef9
WAIT #139924171154784: nam=’db file sequential read’ ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0×0000.00223eb9 xid: 0×0000.000.00000000 uba: 0×00000000.0000.00 statement num=0 parent xid: 0×0000.000.00000000 st-scn: 0×0000.00000000 hi-scn: 0×0000.00000000 ma-scn: 0×0000.00223e78 flg: 0×00000060) undo env [0x7fffe3fe7f60]: (
scn: 0×0000.00223ef8 xid: 0×0003.01f.00000345 uba: 0x00c03092.008f.34 statement num=8192 parent xid: 0×0000.000.0baf3fa0 st-scn: 0x3fa0.00000000 hi-scn: 0×0000.00000000 ma-scn: 0×0000.00000000 flg: 0×00000000)
CRS upd (before): 0xa4fdaa08 cr-scn: 0×0000.00223eb9 xid: 0×0000.000.00000000 uba: 0×00000000.0000.00 cl-scn: 0×0000.00223eff sfl: 0
CRS upd (after) : 0xa4fdaa08 cr-scn: 0×0000.00223eb9 xid: 0×0003.01f.00000345 uba: 0x00c03092.008f.34 cl-scn: 0×0000.00223eff sfl: 0

http://www.askmaclean.com/archives/dml-updatedelete%E4%B8%8Ecr%E4%B8%80%E8%87%B4%E6%80%A7%E8%AF%BB%E7%9A%84%E7%A7%98%E5%AF%86.html 

Saturday Oct 13, 2012

Global Cache CR Requested But Current Block Received

实际测试发现_fairness_threshold似乎存在某个上限,虽然设置为200 但是在不多的几次CR serve后就Downgrade了lock, 这造成对data block的 CR Request最终还是Receive了 Current Block。[Read More]
About

author's avatar The Maclean Liu
Advanced Customer Services
AskMaclean Logo 10g_ocm SHOUG

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today