很多数据库的并发控制方式是通过MVCC(多版本并发控制)实现的。这种方式使得数据库可以通过读取数据块的快照,而不会发生读操作阻塞写操作的情况。Oracle通过undo回滚段实现一致性读取。当一条sql在读取数据块的时候,会记录读取时刻的SCN1,并且将这个SCN1与读取数据块头部ITL槽中的SCN2做比较,如果SCN1
先来看一下一致性读的一个例子:
drop table ming.t190514 purge;
create table ming.t190514(a int,b varchar2(20));
insert into ming.t190514 select 1,'ming' from dual;
commit;
session A发起事务但是不要提交:
update ming.t190514 set b='SHUO' where a=1;
SQL> select * from ming.t190514;
A B
---------- --------------------
1 SHUO
session B:
select dbms_rowid.rowid_relative_fno(rowid) fileno,dbms_rowid.rowid_block_number(rowid) blockno from ming.t190514;
FILENO BLOCKNO
---------- ----------
16 312790
SQL> select * from ming.t190514;
A B
---------- --------------------
1 ming
SQL> alter system dump datafile 16 block 312790;
session A update之后,session B再去查询这张表,这个时候就需要通过一致性读完成select了。
查看dump出来的trace文件:
Block header dump: 0x0404c5d6
Object id on Block? Y
seg/obj: 0x121cb csc: 0x0000000005e141e6 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x404c5d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00f.00002d00 0x018212f3.04bf.18 C--- 0 scn 0x0000000005e141a5
0x02 0x0003.019.000039c7 0x0181df46.04e0.0b ---- 1 fsc 0x0000.00000000
bdba: 0x0404c5d6
data_block_dump,data header at 0x11e06e064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x11e06e064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 53 48 55 4f
end_of_block_dump
通过seg/obj验证找到的这部分内容确实是t190514这张表。
SQL> select to_number('121cb','xxxxxxxxx') from dual;
TO_NUMBER('121CB','XXXXXXXXX')
------------------------------
74187
Elapsed: 00:00:00.00
15:01:54 SQL> select object_name from dba_objects where object_id=74187;
OBJECT_NAME
--------------------------------------------------------------------------------
T190514
lb: 0x2的falg是---。说明还有活动事务。查看col 1的值:
SQL> select utl_raw.cast_to_varchar2(replace('53,48,55,4f',',')) value from dual;
VALUE
--------------------------------------------------------------------------------
SHUO
session A确实已经更改了数据。
查询活动事务:
SQL> select xid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,status from v$transaction;
XID XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
---------------- ---------- ---------- ---------- ---------- ----------
UBASQN UBAREC STATUS
---------- ---------- ----------------
03001900C7390000 3 25 14791 10 122694
1248 11 ACTIVE
dump出undo块:
alter system dump datafile 10 block 122694;
查看dump出来的undo块的trace:
*-----------------------------
* Rec #0xb slt: 0x19 objn: 74187(0x000121cb) objd: 74187 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: 0x0181df46.04e0.08 ctl max scn: 0x0000000005e0b434 prv tx scn: 0x0000000005e0b43e
txn start scn: scn: 0x0000000005e141dd logon user: 83
prev brb: 25288513 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0404c5d6 hdba: 0x0404c5d2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
col 1: [ 4] 6d 69 6e 67
Block dump from disk:
buffer tsn: 2 rdba: 0x0181df46 (6/122694)
scn: 0x5e141dd seq: 0x01 flg: 0x04 tail: 0x41dd0201
frmt: 0x02 chkval: 0x66fd type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
查看col 1的值
SQL> select utl_raw.cast_to_varchar2(replace('6d,69,6e,67',',')) value from dual;
VALUE
--------------------------------------------------------------------------------
ming
还是更新之前的旧值。
update语句在执行的时候,需要先进行一致性读,过滤where条件,然后再做当前读,在这两个过程的时间间隔中如果数据块出现了变动,那么会出现意料之外的效果。
drop table t190514 purge;
create table ming.t190514(a int,b varchar2(20));
insert into ming.t190514 select 1,'ming' from dual;
insert into ming.t190514 select 2,'ming' from dual;
commit;
session A :
SQL> select * from ming.t190514;
A B
---------- --------------------
1 ming
2 ming
SQL> select count(*) from test2,t1;
COUNT(*)
----------
18000000
Elapsed: 00:00:14.37
update ming.t190514 set b='shuo';
commit;
现在t190514表的b值都是shuo。此时我想用a=1的值更新a=2的值,更新前后其实是不会改变数据的。这更加类似于试一次无效更新。但是我在where条件中加入了一个比较耗时的条件,也就是下面语句中的笛卡尔积。
14:27:17 SQL> update ming.t190514 set b=(select b from ming.t190514 where a=1) where a=2 and ( select count(*) from test2,t1)>17000000;
1 row updated.
Elapsed: 00:00:14.56
sql中的( select count(*) from test2,t1)>17000000这段要执行10几秒的时间,如果没有这个条件的话,那么下面再session B中的语句会hang住,很显然是由于行级排它锁导致的。有了这段就可以执行了,在返回结果前快速到session B :
SQL> set timing on time on
14:27:19 SQL> update ming.t190514 set b='x';
2 rows updated.
Elapsed: 00:00:00.00
14:27:19 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
回到之前的session A :
14:28:00 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
此时查询表的数据,发现是下面的情况了:
14:28:05 SQL> select * from ming.t190514;
A B
---------- --------------------
1 x
2 shuo
Elapsed: 00:00:00.00
session A的update语句执行时间点为T1,它会先执行where条件中17000000那部分的条件,这点从执行计划中可以看出。在T2时刻,session B快速更新了表的b为X。在T3时刻,执行完where条件后,需要开始一致性读来读取b的值,此时问题来了,是读取T1时刻update语句开始执行的时间点的值呢,还是读取T3时间点的b的值呢?如果把update看做一个整体的话,那么确实应该是读取T1的值,但其实它是读取了T3时刻的值,也就是说该语句用一致性读的旧值更新了当前读的新值。之前的打算是不改变数据的无效更新,结果发现被其他事务干扰了。update实际更新了b的值。