以前只知道怎么一回事,今天决定去看看到底发生了什么?让我们一起学习。O(∩_∩)O~,想看下去的有木有?
先从update下手吧,
1、更新开始啦,在一窗口运行下面的语句如下
SQL> select * from test;
ID NAME SAL
---------- -------------------- ----------
1 test1 4000
SQL> update test set sal = 6000 where id = 1;
已更新 1 行。
SQL> select * from test where id = 1;
ID NAME SAL
---------- -------------------- ----------
1 test1 6000
先不提交啦,再打开另外一窗口一步步的研究哦。
2、获取事务信息
想获取事务信息查询v$transaction,下面就该视图的下文用到的字段做个介绍
v$transaction
XIDUSN Undo segment number
XIDSLOT Slot number
XIDSQN Sequence number
UBAFIL Undo block address (UBA) filenum
UBASQN UBA sequence number
UBABLK UBA block number
UBAREC UBA record number
上面是摘抄的,我帮助解释下个别的,XIDSQN:就是表示xidslot被重复使用的次数,UBASQN类似。
The slots in the transaction table in the undo segment header
are continuously being reused. Each time a slot it re-used,
its sequence number (xidsqn) goes up.
Similarly, undo blocks are re-used (though it takes more
time to get round to re-using them than transaction slots).
Each time an undo block is "newed" and reused, its
sequence number UBASQN goes up.
SQL> SELECT xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec FROM v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
6 36 5496 87 6 35
从以上查询我们看出该事务位于6号回滚段(XIDUSN ),在6号回滚段上,该事务位于第36号事务槽(XIDSLOT)
3、转储该回滚段段头信息
SQL> select * from v$rollname where usn = 6;
USN NAME
---------- ------------------------------
6 _SYSSMU6$
SQL> alter system dump undo header '_SYSSMU6$';
系统已更改。
SQL> select spid from v$process where addr = (
2 select paddr from v$session where sid =
3 (select distinct sid from v$mystat));
SPID
------------
6136
找到名字中含6136的跟踪日志。
4、分析跟踪日志
********************************************************************************
Undo Segment: _SYSSMU6$ (6)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x01800057 ext#: 3 blk#: 78 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 3
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080005a length: 7
0x008000e1 length: 8
0x01800089 length: 128
0x01800009 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1313051955
Extent Number:1 Commit Time: 1313052276
Extent Number:2 Commit Time: 1313132436
Extent Number:3 Commit Time: 1313132436
TRN CTL:: seq: 0x02a7 chd: 0x000d ctl: 0x0008 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x01800057.02a7.23 scn: 0x0000.00714f8c
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.02a7.22 ext: 0x3 spc: 0xdde
uba: 0x01800054.02a7.15 ext: 0x3 spc: 0x1070
uba: 0x00000000.02a7.06 ext: 0x3 spc: 0x5ce
uba: 0x00000000.0295.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.027f.01 ext: 0x2 spc: 0x1f88
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x1579 0x002d 0x0000.00715cf7 0x01800057 0x0000.000.00000000 0x00000001 0x00000000 1313392821
0x01 9 0x00 0x1578 0x001c 0x0000.00715335 0x01800053 0x0000.000.00000000 0x00000001 0x00000000 1313387325
............................................................................................
0x22 9 0x00 0x1577 0x0010 0x0000.00715235 0x0180004c 0x0000.000.00000000 0x00000001 0x00000000 1313386635
0x23 9 0x00 0x1577 0x002a 0x0000.007150e8 0x01800052 0x0000.000.00000000 0x00000001 0x00000000 1313385782
0x24 10 0x80 0x1578 0x0003 0x0000.00714509 0x01800057 0x0000.000.00000000 0x00000001 0x00000000 0
0x25 9 0x00 0x1577 0x0020 0x0000.00715be2 0x01800057 0x0000.000.00000000 0x00000001 0x00000000 1313392229
0x26 9 0x00 0x1578 0x0013 0x0000.007155a0 0x01800053 0x0000.000.00000000 0x00000001 0x00000000 1313388629
0x27 9 0x00 0x1575 0x000c 0x0000.00714fa2 0x01800052 0x0000.000.00000000 0x00000001 0x00000000 1313385029
...................................................................................................
从以上日志我们可以清晰的看到事务信息,该事务正好位于36号事务槽(0x24,粗体部分),state(事务)为10表示是活动事务。我们再来看看dba(data block address),这个dba指的是前镜像的数据块地址,此时的dba为0x01800057,怎么换算成数据文件号和数据块号呢,我在关于redo的一些东东提到了换算,这里在重复一次
0x01800057转换成2进制
0000 0001 1000 0000 0000 0000 0101 0111
前十位组合在一起是文件号,后面的是块号
0000 0001 10 00 0000 0000 0101 0111
文件号 块号
所以文件号是6,块号是87。
这和从事务表中查出的UBAFIL,UBABLK完全一致。
5、update多行前镜像分析
还是使用test表做测试
SQL> select * from test;
ID NAME SAL
---------- -------------------- ----------
1 test1 6000
4 test4 5000
2 test2 3000
3 test3 5000
SQL> update test set sal = 8000 where id = 1;
已更新 1 行。
SQL> update test set sal = 8000 where id = 2;
已更新 1 行。
SQL> update test set sal = 8000 where id = 3;
已更新 1 行。
SQL> SELECT xidusn, xidslot, xidsqn,ubasqn, ubablk, ubafil, ubarec FROM v$transaction;
XIDUSN XIDSLOT XIDSQN UBASQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
5 42 5489 604 1798 2 39
SQL> alter system dump datafile 2 block 1798;
系统已更改。
查看跟踪日志如下
Start dump data blocks tsn: 1 file#: 2 minblk 1798 maxblk 1798
buffer tsn: 1 rdba: 0x00800706 (2/1798)
scn: 0x0000.0072148e seq: 0x01 flg: 0x04 tail: 0x148e0201
frmt: 0x02 chkval: 0xce30 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
********************************************************************************
UNDO BLK:
xid: 0x0005.02a.00001571 seq: 0x25c cnt: 0x27 irb: 0x27 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1b70 0x02 0x1b0c 0x03 0x1ac0 0x04 0x1a5c 0x05 0x1a10
0x06 0x14b4 0x07 0x1460 0x08 0x140c 0x09 0x1358 0x0a 0x1308
0x0b 0x128c 0x0c 0x1220 0x0d 0x119c 0x0e 0x1118 0x0f 0x1074
0x10 0x0fc0 0x11 0x0f70 0x12 0x0ef4 0x13 0x0e88 0x14 0x0e04
0x15 0x0d80 0x16 0x0ccc 0x17 0x0c7c 0x18 0x0c00 0x19 0x0b94
0x1a 0x0b10 0x1b 0x0a8c 0x1c 0x09f0 0x1d 0x0948 0x1e 0x0894
0x1f 0x0844 0x20 0x07c8 0x21 0x075c 0x22 0x06d8 0x23 0x0654
0x24 0x0608 0x25 0x0558 0x26 0x04e8 0x27 0x0478
注意啦,这里的xid是Transaction的标识,由三部分组成的。Undo Segment的段号,Slot号和Wrap号,其分别对应v$transaction XIDUSN.XIDSLOT.XIDSQNseq即为撤销(回滚)序列编号,其对应v$transaction.UBASQN。cnt对应v$transaction.UBAREC。irb: 0x27表示回滚段中记录的最近的未提交变更开始之处(即回滚段未记录号),如果开始回滚,这是起始的搜索点。rec Offset就是回滚信息的偏移量,最后一个偏移地址正是0x27的信息。
找到0x27的信息如下所示:
*-----------------------------
* Rec #0x27 slt: 0x2a objn: 57577(0x0000e0e9) objd: 57577 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x26
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800706.025c.26
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 8
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400492 hdba: 0x01400491
itli: 2 ispac: 0 maxfr: 4863
vect = 9
col 2: [ 2] c2 33
将c2 33转换成10进制,为194,51,下面就这个内部存储数值转换成实际数值做个介绍:
指数:194 - 193=1
数值1(33):(51-1)*100^(1-0)=5000
从上面可以看出这就是最后更新记录的前镜像(更新前的sal的值为5000), oracle就是这样通过回滚段保留前镜像信息的。rci表示undo chain(同一事务的多次修改,根据chain链接关联)的下一个偏移量,此时的数值为0x26,我们找到这条记录如下:
*-----------------------------
* Rec #0x26 slt: 0x2a objn: 57577(0x0000e0e9) objd: 57577 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x25
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800706.025c.25
Array Update of 1 rows:
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 8
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400492 hdba: 0x01400491
itli: 2 ispac: 0 maxfr: 4863
vect = 9
col 2: [ 2] c2 1f
col 2: [ 2] c2 1f 经换算后得到的数值为3000,恰好是我们更新的第二条记录的前镜像,在根据这条记录的rci我们去找0x25这条记录如下:
*-----------------------------
* Rec #0x25 slt: 0x2a objn: 57577(0x0000e0e9) objd: 57577 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800706.025c.24 ctl max scn: 0x0000.00716818 prv tx scn: 0x0000.0071681e
txn start scn: scn: 0x0000.00721485 logon user: 0
prev brb: 8390404 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.02b.00001575 uba: 0x008002e9.02f7.33
flg: C--- lkc: 0 scn: 0x0000.0071bf84
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400492 hdba: 0x01400491
itli: 2 ispac: 0 maxfr: 4863
vect = 9
col 2: [ 2] c2 3d
col 2: [ 2] c2 3d经换算得到的数值为6000,这是我们更新的第一条记录的前镜像。此时的 rci 为0x00,这表示这是undo chain的最后一条记录了。
从以上可以看出如果要进行回滚,是从最后一次更新记录开始回滚的。
我们也可以从视图x$bh中找到这些数据块
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,decode(bitand(flag,1), 0, 'N', 'Y') DIRTY
2 from x$bh a,dba_extents b
3 where b.RELATIVE_FNO = a.dbarfil
4 and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk
5 and b.owner='SYS' and b.segment_name='TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1169 4 3 N
TEST
5 5 1169 4 1 N
TEST
5 5 1170 1 3 N
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1170 1 3 N
TEST
5 5 1170 1 1 N
TEST
5 5 1170 1 3 N
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1170 1 3 N
已选择7行。
class为4的是段头,class为1的为数据块。如果此时有其他进程查询test,oracle需要构造一致性读,通过前镜像把变化前的数据显示出来。
SQL> select * from test;
ID NAME SAL
---------- -------------------- ----------
1 test1 6000
4 test4 5000
2 test2 3000
3 test3 5000
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,decode(bitand(flag,1), 0, 'N', 'Y') DIRTY
2 from x$bh a,dba_extents b
3 where b.RELATIVE_FNO = a.dbarfil
4 and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk
5 and b.owner='SYS' and b.segment_name='TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1169 4 3 N
TEST
5 5 1169 4 1 N
TEST
5 5 1170 1 0 N
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1170 1 3 N
TEST
5 5 1170 1 3 N
TEST
5 5 1170 1 3 N
SEGMENT_NAME
--------------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
5 5 1170 1 1 Y
TEST
5 5 1170 1 3 N
已选择8行。
此时buffer cache中多出来一个数据块,state为3的就是一致性读构造的前镜像。
6、转储buffer cache中含一致性读信息的数据块分析(由于是隔天写的,重新开始啦)
(1)在一会话中运行SQL如下:
SQL> select * from test;
ID NAME SAL
---------- -------------------- ----------
1 dada 3000
2 xzxz 4000
3 haha 6000
4 haha 5000
SQL> update test set sal = 8000 where id = 1;
已更新 1 行。
SQL> update test set sal = 8000 where id = 2;
已更新 1 行。
SQL> update test set sal = 8000 where id = 3;
已更新 1 行。
(2)在另一会话运行SQL(转储相应回滚段段头信息)如下:
SQL> SELECT xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec FROM v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 39 5477 456 6 33
SQL> select * from v$rollname where usn = 9;
USN NAME
---------- ------------------------------
9 _SYSSMU9$
SQL> alter system dump undo header '_SYSSMU9$';
系统已更改。
跟踪日志如下:
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 271
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x018001c8 ext#: 3 blk#: 63 ext size: 128
-----------------------------------------------------------------------------------------(略)
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x1566 0x000f 0x0000.0072f80f 0x018001c5 0x0000.000.00000000 0x00000001 0x00000000 1313544689
............................................................................................
0x26 9 0x00 0x1565 0x000d 0x0000.0072f599 0x018001c7 0x0000.000.00000000 0x00000001 0x00000000 1313543758
0x27 10 0x80 0x1565 0x0003 0x0000.00000000 0x018001c8 0x0000.000.00000000 0x00000001 0x00000000 0
0x28 9 0x00 0x1565 0x0007 0x0000.0072f589 0x018001c7 0x0000.000.00000000 0x00000001 0x00000000 1313543758
(3)在开启一会话运行SQL如下:
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,
2 decode(bitand(flag,1), 0, 'N', 'Y') DIRTY
3 from x$bh a,dba_extents b
4 where b.RELATIVE_FNO = a.dbarfil
5 and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk
6 and b.owner='SYS' and b.segment_name='TEST';
SEGMENT_NAME
------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
1 1 62089 4 3 N
TEST
1 1 62089 4 1 N
TEST
1 1 62090 1 3 N
SEGMENT_NAME
------------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
1 1 62090 1 3 N
TEST
1 1 62090 1 3 N
TEST
1 1 62090 1 1 N
已选择6行。
SQL> select * from test;
ID NAME SAL
---------- -------------------- ----------
1 dada 3000
2 xzxz 4000
3 haha 6000
4 haha 5000
SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,
2 decode(bitand(flag,1), 0, 'N', 'Y') DIRTY
3 from x$bh a,dba_extents b
4 where b.RELATIVE_FNO = a.dbarfil
5 and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk
6 and b.owner='SYS' and b.segment_name='TEST';
SEGMENT_NAME
----------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
1 1 62089 4 3 N
TEST
1 1 62089 4 1 N
TEST
1 1 62090 1 3 N
SEGMENT_NAME
----------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
1 1 62090 1 3 N
TEST
1 1 62090 1 3 N
TEST
1 1 62090 1 1 Y
SEGMENT_NAME
----------------------------------------------------------------------
FILE# DBARFIL DBABLK CLASS STATE D
---------- ---------- ---------- ---------- ---------- -
TEST
1 1 62090 1 3 N
已选择7行。
SQL> alter system dump datafile 1 block 62090;
系统已更改。
查看跟踪日志如下:
Start dump data blocks tsn: 0 file#: 1 minblk 62090 maxblk 62090
buffer tsn: 0 rdba: 0x0040f28a (1/62090)
scn: 0x0000.0073498a seq: 0x01 flg: 0x00 tail: 0x498a0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
*************************************************************
Block header dump: 0x0040f28a
Object id on Block? Y
seg/obj: 0xe1b5 csc: 0x00.73498a itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.017.0000157f 0x008001d9.02f8.07 C--- 0 scn 0x0000.0072f76e
0x02 0x0009.027.00001565 0x018001c8.02a3.21 ---- 3 fsc 0x0000.00000000
data_block_dump,data header at 0x89a225c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x089a225c
bdba: 0x0040f28a
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f68
avsp=0x1f4e
tosp=0x1f4e
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f92
0x14:pri[1] offs=0x1f84
0x16:pri[2] offs=0x1f76
0x18:pri[3] offs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f92
tl: 14 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 4] 64 61 64 61
col 2: [ 2] c2 51
tab 0, row 1, @0x1f84
tl: 14 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 4] 78 7a 78 7a
col 2: [ 2] c2 51
tab 0, row 2, @0x1f76
tl: 14 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 4] 68 61 68 61
col 2: [ 2] c2 51
tab 0, row 3, @0x1f68
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 4] 68 61 68 61
col 2: [ 2] c2 33
end_of_block_dump
注意以上这段日志的红色部分。在这个数据块中有itl(Interested Transaction List)事务槽信息,事务必须获得一个ITL事务槽才能够进行数据修改,ITL内容包括
ITL序号(Itl)、事务编号(XID)、回滚段地址(Uba)、事务标识(Flag)、锁定的行数(lck), Scn/Fsc。
( 注:xid=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap)
xid=0x0009.027.00001565分解后我们可以得知该事务指向9号回滚段,slot号为
39(027),Wrap为1565。至此我们可以在回头看看(2)部分中的红色部分,仔细对应一下,发现该事务正是红色部分的事务。
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
0x27 10 0x80 0x1565 0x0003 0x0000.00000000 0x018001c8 0x0000.000.00000000 0x00000001 0x00000000 0
将Uba=0x018001c8.02a3.21分解得到0x018001c8是前镜像地址,换算之后可以知道其位于file 6 block 456,与从v$transaction查询出来的UBAFIL,UBABLK相符。
02a3是顺序号(seq)。21是undo记录的开始地址(irb)。下面去验证下seq,irb是否与undo中的信息相符。
FLAG各种标记位代表不同意思,以下为不同标记位代表不同意思:
---- = transaction is active, or committed pending cleanout
C--- = transaction has been committed and locks cleaned out
-B-- = this undo record contains the undo for this ITL entry
--U- = transaction committed (maybe long ago); SCN is an upper bound
---T = transaction was still active at block cleanout SCN
lck 代表锁定几行,从上面日志我们可以看出锁定了3行。
Scn/Fsc Commit SCN/快速提交(Fast Commit Fsc)
SQL> alter system dump datafile 6 block 456;
系统已更改。
查看跟踪日志如下:
UNDO BLK:
xid: 0x0009.027.00001565 seq: 0x2a3 cnt: 0x21 irb: 0x21 icl: 0x0 flg: 0x0000
很清晰了吧,完全对应上了。
我们继续分析日志的剩下的部分,注意红色部分,lb指向0x2号事务槽,此时我们换算col 2: [ 2] c2 51这第三个字段的值为8000,这里我们可以清晰的看到前面3行的第三字段已经全部修改成了c2 51(也就是8000)。
总结一下事务基本流程:
(1)、首先当一个事务开始时,需要在回滚段事务表上分配一个事务槽
(2)、在数据块头部获取一个ITL事务槽,该事务槽指向回滚段头的事务槽
(3)、在修改数据之前,需要记录前镜像信息,这个信息以UNDO RECORD的形式存储在回滚段中,回滚段头事务槽指向该记录
(4)、锁定修改行,修改行锁定位(lb-lock byte)指向ITL事务槽
(5)、数据修改
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25964700/viewspace-704905/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25964700/viewspace-704905/