详细解析oracle中的transaction

之前已经写过:

详细解析9i10gdatafile header”

详细解析LMTdatafile的物理结构

详细解析datafilestatus”

 

这里是详细解析系列的第四篇文章,在这篇文章里,我们详细解析了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:01commit

2lockREPCAT$_REPCAT中的第一个itl

3lockREPCAT$_REPSCHEMA中的第二个itl

4lockDBMS_LOCK_ALLOCATED中的第三个itl

5 将表DBMS_LOCK_ALLOCATED的列EXPIRATION更新成2009-12-12 12:02:01commit

6、对表REPCAT$_REPCAT上的两个itl执行block clean outlockREPCAT$_REPCAT中的第二个itl

7、对表REPCAT$_REPSCHEMA上的两个itl执行block clean outlockREPCAT$_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: 0x0000d0f8 flg: 0x000a siz: 136 fbi: 0

            uba: 0x12c0002b.2eaa.01    pxid:  0x0000.000.00000000

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

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

            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

             0x00000000  prev ctl uba: 0x12c0002a.2eaa.44

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.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(0x0) 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:0x0008.bb666fd5 SEQ:  1 OP:11.5

KTB Redo

op: 0x11  ver: 0x01 

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

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: 0x1bc0000c

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0x0) 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: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: 0x0000d0f8 srt: 0 sta: 9 flg: 0x2

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 logdump文件中我们可以清晰的看出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.



Leave a Reply

Your email address will not be published. Required fields are marked *