详细解析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 9.2.0.6.0 – Production on Tue Dec 8 12:00:39 2009

 

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/redo02a.log

/dras11/oradata/astca/redo02b.log

 

SQL_astca>alter system dump logfile ‘/dras10/oradata/astca/redo02a.log’;

 

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: 0×000003.00000017.0010 LEN: 0x01b8 VLD: 0×01

SCN: 0×0008.bb667126 SUBSCN:  1 12/08/2009 12:04:02

CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0×00800089 SCN:0×0008.bb6670fd SEQ:  1 OP:5.2

ktudh redo: slt: 0×0016 sqn: 0x0000d0f8 flg: 0x000a siz: 136 fbi: 0

            uba: 0x12c0002b.2eaa.01    pxid:  0×0000.000.00000000

CHANGE #2 TYP:1 CLS:34 AFN:75 DBA:0x12c0002b SCN:0×0008.bb667126 SEQ:  1 OP:5.1

ktudb redo: siz: 136 spc: 0 flg: 0x000a seq: 0x2eaa rec: 0×01

            xid:  0x0009.016.0000d0f8 

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

             0×00000000  prev ctl uba: 0x12c0002a.2eaa.44

prev ctl max cmt scn:  0×0008.bb6646e9  prev tx cmt scn:  0×0008.bb664985

KDO undo record:

KTB Redo

op: 0×04  ver: 0×01 

op: L  itl: xid:  0×0001.02e.0000da74 uba: 0x14837dfc.2c60.1d

                      flg: C—    lkc:  0     scn: 0x0002.968c0104

KDO Op code: URP row dependencies Disabled

  xtype: XA  bdba: 0x1bc0000d  hdba: 0x1bc0000c

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0×0) flag: 0x2c lock: 0 ckix: 0

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:0×0008.bb666fd5 SEQ:  1 OP:11.5

KTB Redo

op: 0×11  ver: 0×01 

op: F  xid:  0x0009.016.0000d0f8    uba: 0x12c0002b.2eaa.01

Block cleanout record, scn:  0×0008.bb667126 ver: 0×01 opt: 0×02, entries follow…

  itli: 2  flg: 2  scn: 0×0008.bb666fd5

KDO Op code: URP row dependencies Disabled

  xtype: XA  bdba: 0x1bc0000d  hdba: 0x1bc0000c

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0×0) flag: 0x2c lock: 1 ckix: 0

ncol: 101 nnew: 1 size: 0

col  6: [ 8]  30 38 33 30 36 34 33 32

CHANGE #4 MEDIA RECOVERY MARKER SCN:0×0000.00000000 SEQ:  0 OP:5.20

session number   = 20

serial  number   = 1751

transaction name =

 

REDO RECORD – Thread:1 RBA: 0×000003.00000018.0010 LEN: 0×0054 VLD: 0×01

SCN: 0×0008.bb66712b SUBSCN:  1 12/08/2009 12:04:13

CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0×00800089 SCN:0×0008.bb667126 SEQ:  1 OP:5.4

ktucm redo: slt: 0×0016 sqn: 0x0000d0f8 srt: 0 sta: 9 flg: 0×2

ktucf redo: uba: 0x12c0002b.2eaa.01 ext: 1 spc: 8012 fbi: 0

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（即0×0008.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.

---