详细解析oracle中的transaction
Posted: December 12, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: oracle transaction | Leave a comment »之前已经写过：
“详细解析9i和10g的datafile header”
“详细解析LMT的datafile的物理结构”
“详细解析datafile的status”
这里是详细解析系列的第四篇文章，在这篇文章里，我们详细解析了oracle中的transaction的流程。
我们从一个实例开始说起：
$ sqlplus /nolog
SQL*Plus: Release
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL_astca>conn armshistemp/armshistemp@astcatest;
Connected.
SQL_astca>select count(*) from uplpdt;
COUNT(*)
———-
6962716
SQL_astca>alter system switch logfile;
System altered.
SQL_astca>select group#,status from v$log;
GROUP# STATUS
———- —————-
1 ACTIVE
2 CURRENT
3 INACTIVE
SQL_astca>select updagt,dump(updagt,16) from uplpdt where updprf=’999′ and updfrm=’240′ and updtkt=’9854125′ and updcpn=1;
UPDAGT DUMP(UPDAGT,16)
——– ——————————————————————————–
08300000 Typ=1 Len=8: 30,38,33,30,30,30,30,30
SQL_astca>update uplpdt set updagt=’08306432′ where updprf=’999′ and updfrm=’240′ and updtkt=’9854125′ and updcpn=1;
1 row updated.
SQL_astca>commit;
Commit complete.
SQL_astca>select updagt,dump(updagt,16) from uplpdt where updprf=’999′ and updfrm=’240′ and updtkt=’9854125′ and updcpn=1;
UPDAGT DUMP(UPDAGT,16)
——– ——————————————————————————–
08306432
Typ=1 Len=8: 30,38,33,30,36,34,33,32
SQL_astca>select member from v$logfile where group#=2;
MEMBER
——————————————————————————–
/dras10/oradata/astca/redo
/dras11/oradata/astca/redo02b.log
SQL_astca>alter system dump logfile ‘/dras10/oradata/astca/redo
System altered.
从上述dump文件中我们可以看到oracle在真正开始上述transaction前会做下述一系列的事情：
1、将job$的列THIS_DATE更新成2009-12-08 12:02:01后commit；
2、lock表REPCAT$_REPCAT中的第一个itl；
3、lock表REPCAT$_REPSCHEMA中的第二个itl；
4、lock表DBMS_LOCK_ALLOCATED中的第三个itl；
5、 将表DBMS_LOCK_ALLOCATED的列EXPIRATION更新成2009-12-12 12:02:01后commit；
6、对表REPCAT$_REPCAT上的两个itl执行block clean out并lock表REPCAT$_REPCAT中的第二个itl；
7、对表REPCAT$_REPSCHEMA上的两个itl执行block clean out并lock表REPCAT$_REPSCHEMA中的第一个itl；
……省略显示部分类似内容
8、更新表SMON_SCN_TIME。
在了解opcodes的情况下读懂上述redo log并不困难。
上述几条都做完后，oracle开始了我们真正关注的transaction：
REDO RECORD – Thread:1 RBA: 0x000003.00000017.0010 LEN: 0x01b8 VLD: 0x01
SCN: 0x0008.bb667126 SUBSCN: 1 12/08/2009 12:04:02
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0008.bb6670fd SEQ: 1 OP:5.2
ktudh redo: slt: 0x0016 sqn: 0x0000d
uba: 0x
CHANGE #2 TYP:1 CLS:34 AFN:75 DBA:0x
ktudb redo: siz: 136 spc: 0 flg: 0x
xid: 0x
ktubl redo: slt: 22 rci: 0 opc: 11.1 objn: 84192 objd: 85820 tsn: 83
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x
prev ctl max cmt scn: 0x0008.bb6646e9 prev tx cmt scn: 0x0008.bb664985
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0001.02e.0000da74 uba: 0x14837dfc
flg: C— lkc: 0 scn: 0x
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x1bc0000d hdba: 0x1bc
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x
ncol: 101 nnew: 1 size: 0
col 6: [ 8] 30 38 33 30 30 30 30 30
CHANGE #3 TYP:2 CLS: 1 AFN:111 DBA:0x1bc0000d SCN:0x0008.bb666fd5 SEQ: 1 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x
Block cleanout record, scn: 0x0008.bb667126 ver: 0x01 opt: 0x02, entries follow…
itli: 2 flg: 2 scn: 0x0008.bb666fd5
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x1bc0000d hdba: 0x1bc
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x
ncol: 101 nnew: 1 size: 0
col 6: [ 8] 30 38 33 30 36 34 33 32
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 20
serial number = 1751
transaction name =
REDO RECORD – Thread:1 RBA: 0x000003.00000018.0010 LEN: 0x0054 VLD: 0x01
SCN: 0x0008.bb66712b SUBSCN: 1 12/08/2009 12:04:13
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0008.bb667126 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0016 sqn: 0x0000d
ktucf redo: uba: 0x
END OF REDO DUMP
—– Redo read statistics for thread 1 —–
其中跟我们上述transaction所相关的opcodes的值及其含义为：
5.1 Generate undo block
5.2 Update rollback segment header
5.4 Commit Transaction(transaction table update)
11.5 Update Row Piece
URP表示update
从上述redo log的dump文件中我们可以清晰的看出oracle中的transaction的整体流程为：
1. Undo segment binding（即op:5.2）
2. Slot allocation in the transaction table
3. Undo block allocation（即op:5.1）
4. Find an interested transaction list (ITL) available in the block.
5. Lock the row you are modifying.
6. Generate the undo part of data describing （即op:5.1中的code:URP）
7. Generate the redo part of data describing the changes to the data block.（即op:11.5）
8. Create the redo record and apply the changes to the blocks.
9. Finds an commit SCN value（即0x0008.bb66712b）.
10. Updates the transaction table（即ktucm redo，注意这里的status已经变成9了）.
11. Puts the current undo block into the free block pool (under some conditions, 即ktucf redo).
12. Creates a commit record in the redo log buffer.（即op:5.4）
13. Flushes the redo log buffer to disk (for durability).
14. Releases locks held on rows and tables, may execute fast block cleanout.
Recent Comments