昨天有朋友在问Oracle一致性读的问题。这个不仅是Oracle最得意的几项核心技术之一,也是Oracle优化的主要目标,我就从网上找了个了简介,直接copy过来了,再加上点自己的实验。
在Oracle数据库中,undo主要有三大作用:提供一致性读(Consistent Read)、回滚事务(Rollback Transaction)以及实例恢复(Instance Recovery)。
一致性读是相对于脏读(Dirty Read)而言的。假设某个
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
T中有10000条记录,获取所有记录需要15分钟时间。当前时间为9点整,某用户A发出一条查询语句:select * from T,该语句在9点15分时执行完毕。当用户A执行该SQL语句到9点10分的时候,另外一个用户B发出了一条delete命令,将T表中的最后一条记录删 除并提交了。那么到9点15分时,A用户将返回多少条记录?
如果返回9999条记录,则说明发生了脏读;如果仍然返回10000条记录,则说明发生了一致性读。很明显,在 9点钟那个时间点发出查询语句时,表T中确实有10000条记录,只不过由于I/O的相对较慢,所以才会花15分钟完成所有记录的检索。对于Oracle 数据库来说,没有办法实现脏读,必须提供一致性读,并且该一致性读是在没有阻塞用户的DML的前提下实现的。
那么undo数据是如何实现一致性读的呢?还是针对上面的例子。用户A在9点发出查询语句时,服务器进程会将9 点那个时间点上的SCN号记录下来,假设该SCN号为SCN9.00。那么9点整的时刻的SCN9.00一定大于等于记录在所有数据块头部的ITL槽中的 SCN号(如果有多个ITL槽,则为其中最大的那个SCN号)。
注:ITL(Interested Transaction List)是 Oracle数据块内部的一个组成部分,用来记录该块所有发生的事务,一个itl可以看作是一个记录,在一个时间,可以记录一个事务(包括提交或者未提交 事务)。当然,如果这个事务已经提交,那么这个itl的位置就可以被反复使用了,因为itl类似记录,所以,有的时候也叫itl槽位。
服务器进程在扫描表T的数据块时,会把扫描到的数据块头部的ITL槽中的SCN号与SCN9:00之间进行比较,哪个更大。如果数据块头部的SCN号比 SCN9.00要小,则说明该数据块在9点以后没有被更新,可以直接读取其中的数据;否则,如果数据块ITL槽的SCN号比SCN9.00要大,则说明该 数据块在9点以后被更新了,该块里的数据已经不是9点那个时间点的数据了,于是要借助undo块。
9点10分,B用户更新了表T的最后一条记录并提交(注意,在这里,提交或者不提交并不是关键,只要用户B更新了表T,用户A就会去读undo数据块)。 假设被更新记录属于N号数据块。那么这个时候N号数据块头部的ITL槽的SCN号就被改为SCN9.10。当服务器进程扫描到被更新的数据块(也就是N号 块)时,发现其ITL槽中的SCN9.10大于发出查询时的SCN9.00,说明该数据块在9点以后被更新了。于是服务器进程到N号块的头部,找到 SCN9.10所在的ITL槽。由于ITL槽中记录了对应的undo块的地址,于是根据该地址找到undo块,将 undo块中的被修改前的数据取出,再结合N号块里的数据行,从而构建出9点10分被更新之前的那个时间点的数据块
内容
财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容
,这样的数据块叫做CR块 (Consistent Read)。对于delete来说,其undo信息就是insert,也就是说该构建出来的CR块中就插入了被删除的那条记录。随后,服务器进程扫描该 CR块,从而返回正确的10000条记录。
让我们继续把问题复杂化。假设在9点10分B用户删除了最后一条记录并提交以后,紧跟着9点11分,C用户在同一个数据块里(也就是N号块)插入了2条记 录。这个时候Oracle又是如何实现一致性读的呢(假设表T的initrans为1,也就是只有一个ITL 槽)?因为我们已经知道,事务需要使用ITL槽,只要该事务提交或回滚,该ITL槽就能够被重用。换句话说,该ITL槽里记录的已经是SCN9.11,而 不是SCN9.10了。这时,ITL槽被覆盖了,Oracle的服务器进程又怎能找回最初的数据呢?
其中的秘密就在于,Oracle在记录undo数据的时候,不仅记录了改变前的数据,还记录了改变前的数据所在的数据块头部的ITL信息。因此,9点10 分B用户删除记录时(位于N号块里,并假设该N号块的ITL信息为[Undo_block0 / SCN8.50]),则Oracle会将改变前的数据(也就是insert)放到undo块(假设该undo块地址为Undo_block1)里,同时在 该undo块里记录删除前ITL槽的信息(也就是[Undo_block0 / SCN8.50])。删除记录以后,该N号块的ITL信息变为 [Undo_block1 / SCN9.10];到了9点11分,C用户又在N号块里插入了两条记录,则Oracle将插入前的数据(也就是delete两条记录)放到undo块(假 设该undo块的地址为Undo_block2)里,并将9点11分时的ITL槽的信息(也就是[Undo_block1 / SCN9.10])也记录到该undo块里。插入两条记录以后,该N号块的ITL槽的信息改为 [Undo_block2 / SCN9.11]。
那么当执行查询的服务器进程扫描到N号块时,发现SCN9.11大于SCN9.00,于是到ITL槽中指定的 Undo_block2处找到该undo块。发现该undo块里记录的ITL信息为[Undo_block1 / SCN9.10],其中的SCN9.10仍然大于SCN9.00,于是服务器进程继续根据ITL中记录的Undo_block1,找到该undo块。发现 该undo块里记录的ITL信息为[Undo_block0 / SCN8.50],这时ITL里的SCN8.50小于发出查询时的SCN9.00,说明这时undo块包含合适的undo信息,于是服务器进程不再找下 去,而是将N号块、Undo_block2以及Undo_block1的数据结合起来,构建CR块。将当前N号的数据复制到CR块里,然后在CR块里先回 退9点11分的事务,也就是在CR块里删除两条记录,然后再回退9点10分的事务,也就是在CR块里插入被删除的记录,从而构建出9点钟时的数据。 Oracle就是这样,以层层嵌套的方式,查找整个undo块的链表,直到发现ITL槽里的SCN号小于等于发出查询时的那个SCN号为止。正常来说,当 前undo块里记录的SCN号要比上一个undo块里记录的SCN号要小。
但是在查找的过程中,可能会发现当前undo块里记录的ITL槽的SCN号比上一个undo块里记录的SCN号还要大。这种情况说明由于事务被提交或回 滚,导致当前找到的undo块里的数据已经被其他事务覆盖了,于是我们无法再找出小于等于发出查询时的那个时间点的SCN号,这时Oracle就会抛出一 个非常经典的错误——ORA-1555,也就是snapshot too old的错误。
以上的描述可以用图来描述:
回滚事务则是在执行DML以后,发出rollback命令撤销DML所作的变化。Oracle利用记录在ITL槽里记录的undo 块的地址找到该undo块,然后从中取出变化前的值,并放入数据块中,从而对事务所作的变化进行回滚。
实例恢复则是在SMON进程完成前滚并打开数据库以 后发生。SMON进程会去查看undo segment头部(所谓头部就是undo segment里的第一个数据块)记录的事务表(每个事务在使用undo块时,首先要在该undo块所在的undo segment的头部记录一个条目,该条目里记录了该事务相关的信息,其中包括是否提交等),将其中既没有提交也没有回滚,而是在实例崩溃时被异常终止的 事务全部回滚。
前面的废话说多了,我们还是直接用实验来说话吧:
SQL> set serveroutput on;
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);--中间等待另外一个Session启动并执行更新数据操作
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
在执行游标打印输出的时候同时启动另外一个进程,执行更新数据操作:
SQL> set serveroutput on;
SQL> create table test(id number,name varchar2(10));
Table created
SQL> insert into test values(1,'a');
1 row inserted
SQL> insert into test values(2,'b');
1 row inserted
SQL> commit;
Commit complete
SQL> declare
2 cursor cur is select * from test;
3 begin
4 for rec in cur
5 loop
6 dbms_output.put_line(rec.name);
7 dbms_lock.sleep(10);
8 end loop;
9 end;
10 /
a
b
PL/SQL procedure successfully completed
下面我们开始试验二,模拟事务级别读一致性。
首先启动一个Session,读一次数据:
SQL> SET TRANSACTION READ ONLY;
Transaction set
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
接下来我们启动另外一个session,执行更新数据操作:
SQL> update test set name='123456';
2 rows updated
SQL> commit;
Commit complet
最后我们回到第一Session查看再次查看数据:
SQL> select * from test;
ID NAME
---------- ----------
1 a
2 bbbb
下面我们再说下DML UPDATE/DELETE与CR一致性读的关系。我们再来看个恶心一点的个问题:
环境为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)
为了
证明
住所证明下载场所使用证明下载诊断证明下载住所证明下载爱问住所证明下载爱问
和演示该事务级别的读一致性,设计了以下演示:
有一张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行数据。
算了,还是直接实验吧,纯SQLplus:
16:36:11 SQL> create table tab(t1 number(10),t2 varchar2(10));
Table created.
16:36:28 SQL> insert into tab values(600000,'U2');
1 row created.
16:36:37 SQL> insert into tab values(900000,'U1');
1 row created.
16:36:42 SQL> commit;
Commit complete.
在A)时刻,Session A使用SELECT .. FOR UPDATE锁住T1=600000的这一行:
session A:
16:37:38 SQL> select * from tab 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:
16:38:33 SQL> SELECT a.VALUE
|| b.symbol
|| c.instance_name
|| '_ora_'
|| d.spid
|| '.trc' trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name
FROM v$instance) c,
(SELECT spid
FROM v$Session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d;16:39:10 2 16:39:10 3 16:39:10 4 16:39:10 5 16:39:10 6 16:39:10 7 16:39:10 8 16:39:10 9 16:39:10 10 16:39:10 11 16:39:10 12 16:39:10 13 16:39:10 14 16:39:10 15 16:39:10 16 16:39:10 17
TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/Oracle/diag/rdbms/sm802/sm802/trace/sm802_ora_31999.trc
16:39:11 SQL> alter system flush buffer_cache;
System altered.
16:39:21 SQL> /
System altered.
16:39:22 SQL> alter Session set events '10046 trace name context forever,level 8 : 10201 trace name context forever,level 10';
session altered.
16:39:38 SQL> update tab set t2='U3' where t2='U2';
当然这个是11g,也可以直接这样,结果是一样的:
16:41:21 SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/Oracle/diag/rdbms/sm802/sm802/trace/sm802_ora_31999.trc
在之后的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:
16:36:47 SQL> select * from tab where t1=900000;
T1 T2
---------- ----------
900000 u1
16:40:00 SQL> update tab set t2='U2' where t1=900000;
1 row updated.
16:40:06 SQL> commit;
Commit complete.
16:40:17 SQL> alter system flush buffer_cache;
System altered.
之后Session A 执行 commit;session B得以继续update,得到实验的结果:
Session A:
16:38:12 SQL> commit;
Commit complete.
session B:
16:39:47 SQL> update tab set t2='U3' where t2='U2';
1 row updated.
16:41:01 SQL> alter session set events '10046 trace name context off';
Session altered.
我们再回过头来看一下 上面Session B产生的10201 undo apply和10046 trace的内容,可以发现更多内容,也就是一致性的本质:
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from tab where t1=600000 or t1=900000;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO(
------------------------------ ------------------------------
14603 5
14603 5
我们看下跟踪日志输的结果,截取:
WAIT #47637034598944: nam='db file scattered read' ela= 42 file#=5 block#=14603 blocks=5 obj#=27667 tim=3
WAIT #47637034598944: nam='db file sequential read' ela= 16 file#=3 block#=160 blocks=1 obj#=0 tim=8
Applying CR undo to block 5 : 140390b itl entry 02:
xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36
flg: ---- lkc: 1 fsc: 0x0000.00000000
WAIT #47637034598944: nam='db file sequential read' ela= 12 file#=3 block#=910 blocks=1 obj#=0 tim=7
CRS upd rd env [0x2b535cac3e44]: (scn: 0x0000.0177e99a 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.0177e969 flg: 0x00000661) undo env [0x7fff0130e070]: (
scn: 0x0000.0177e99a xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36 statement num=65536 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00000000 flg: 0x00000000)
CRS upd (before): 0xcefc3c48 cr-scn: 0x0000.0177e99a xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.0177e99b sfl: 0
CRS upd (after) : 0xcefc3c48 cr-scn: 0x0000.0177e99a xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36 cl-scn: 0x0000.0177e99b sfl: 0
FETCH #47637034598944:c=1000,e=546,p=7,cr=9,cu=0,mis=0,r=1,dep=1,og=1,plh=3827805242,tim=8
STAT #47637034598944 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=9 pr=7 pw=0 time=547 us)'
STAT #47637034598944 id=2 cnt=2 pid=1 pos=1 obj=27667 op='TABLE ACCESS FULL TAB (cr=9 pr=7 pw=0 time=522 us cost=2 size=574 card=82)'
CLOSE #47637034598944:c=0,e=3,dep=1,type=0,tim=9
=====================
PARSING IN CURSOR #47637034550304 len=38 dep=0 uid=54 oct=6 lid=54 tim=9 hv=1113827815 ad='e7c51c10' SQLid='druyb09167ag7'
update tab set t2='U3' where t2='U2'
END OF STMT
PARSE #47637034550304:c=3000,e=2840,p=8,cr=10,cu=0,mis=1,r=0,dep=0,og=1,plh=3273534852,tim=8
Applying CR undo to block 5 : 140390b itl entry 02:
xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36
flg: ---- lkc: 1 fsc: 0x0000.00000000
CRS upd rd env [0x2b535cac5eb4]: (scn: 0x0000.0177e99c 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.0177e969 flg: 0x00000061) undo env [0x7fff013196e0]: (
scn: 0x0000.0177e99c xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36 statement num=15728 parent xid: 0x0000.000.0131ae90 st-scn: 0xc3f0.00007fff hi-scn: 0x9f80.00007fff ma-scn: 0x0000.00000000 flg: 0x00007fff)
CRS upd (before): 0xc9fec968 cr-scn: 0x0000.0177e99c xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.0177e99c sfl: 0
CRS upd (after) : 0xc9fec968 cr-scn: 0x0000.0177e99c xid: 0x0003.014.000066e6 uba: 0x00c0038e.0498.36 cl-scn: 0x0000.0177e99c sfl: 0
*** 2013-05-07 17:44:35.162
WAIT #47637034550304: nam='enq: TX - row lock contention' ela= 52884291 name|mode=1415053318 usn<<16 | slot=196628 sequence=26342 obj#=27667 tim=4
WAIT #47637034550304: nam='db file sequential read' ela= 14 file#=5 block#=14603 blocks=1 obj#=27667 tim=9
WAIT #47637034550304: nam='db file scattered read' ela= 32 file#=5 block#=14604 blocks=4 obj#=27667 tim=6
WAIT #47637034550304: nam='db file sequential read' ela= 8 file#=3 block#=176 blocks=1 obj#=0 tim=7
WAIT #47637034550304: nam='db file sequential read' ela= 21 file#=3 block#=3939 blocks=1 obj#=0 tim=1
EXEC #47637034550304:c=1000,e=52885094,p=7,cr=11,cu=4,mis=0,r=1,dep=0,og=1,plh=3273534852,tim=8
STAT #47637034550304 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE TAB (cr=11 pr=7 pw=0 time=52885055 us)'
STAT #47637034550304 id=2 cnt=1 pid=1 pos=1 obj=27667 op='TABLE ACCESS FULL TAB (cr=9 pr=4 pw=0 time=331 us cost=3 size=7 card=1)'
WAIT #47637034550304: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=3
以上可以看到对T1=600000所在的数据块140390b=》datafile 5 14603 apply了UNDO,其环境SCN 为scn: 0×0000. 0177e99a:
对于T1=900000的数据行所在块140390b=》 datafile 5 14603 block同样apply了UNDO,其环境SCN为0177e99c。
两者都进行了UNDO操作,保证了DML操作的镜像性,而不是使用了满足条件的数据,接进进行下面的操作,即一切以SCN的大小为
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
来衡量是否进行UNDO,这也再次显示了SCN在Oracle中的唯一标准地位。
以看到以上实验的结果 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的原因之一。