undo异常事务回滚规则分析

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:undo异常事务回滚规则分析

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

undo事务具体是如何回滚,这里提供了大概的异常undo事务回滚的一个过程(更加准确的说,这个过程是在以下几种情况中发生的过程:1.数据库非正常关闭后启动,2.事务未提交会话终止),数据库先扫描所有回滚段,然后发现有事务未提交回滚段,然后根据这个回滚段定位到undo block,然后定位到data block,当一个undo block回滚完成之后,利用undo的链表规则完成下一个undo block的回滚操作,依次类此,从而实现数据库的回滚操作;回滚的过程是先回滚后操作的块(先进后出原则)
创建测试表

SQL> create table chf.t_xifenfei(a varchar2(4000));

Table created.

SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'a'));

1 row created.

SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'b'));

1 row created.

SQL> commit;

Commit complete.

SQL> update chf.t_xifenfei set a=lpad('www.xifenfei.com',4000,'F');

2 rows updated.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;

   REL_FNO   BLOCK_NO
---------- ----------
         9        421
         9        422

确保表中有两条记录,存储在两个block中

查询测试表相关信息

SQL> select object_id,data_object_id from dba_objects where owner='CHF' AND OBJECT_NAME='T_XIFENFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     53917          53917

SQL> SELECT TO_CHAR(53917,'XXXXX') FROM DUAL;

TO_CHA
------
  D29D

SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         2         28        513          2       3627        253          1

SQL> select to_char(28,'xxx') from dual;

TO_C
----
  1c

SQL> select to_char(513,'xxx') from dual;

TO_C
----
 201

------------------------
--xid=0002.01c.00000201
------------------------

SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(2,3627);

PL/SQL procedure successfully completed.

SQL> print dba

DBA
--------------------------------
8392235

SQL> select to_char(253,'xxx') from dual;

TO_C
----
  fd
---------------------
--uda=800e2a.00fd.01
---------------------

通过这些查询可以得知:
1)chf.t_xifenfei的object_id/data_object_id的值为53917/D29D
2)xid=0002.01c.00000201(Xid=usn.slot.wrap)
3)uda=800e2a.00fd.01(Uba=undo block dba.undo record number.undo block sequence number)

dump rollback header

SQL> select * from v$rollname where usn=2;

       USN NAME
---------- ------------------------------
         2 _SYSSMU2$

SQL>  alter system dump undo header "_SYSSMU2$";

System altered.

 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
0x00    9    0x00  0x0201  0x0012  0x0b2c.c02c9b85  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
   ………………
0x1b    9    0x00  0x0201  0x0028  0x0b2c.c02c9bf4  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
0x1c   10    0x80  0x0201  0x0002  0x0b2c.c02ca2a8  0x00800e2b  0x0000.000.00000000  0x00000002   0x00000000  0
0x1d    9    0x00  0x0200  0x0004  0x0b2c.c02c9a8b  0x00800e26  0x0000.000.00000000  0x00000001   0x00000000  1358780444
   ………………
0x2f    9    0x00  0x0201  0x001a  0x0b2c.c02ca1da  0x00800e29  0x0000.000.00000000  0x00000001   0x00000000  1358784176

------------------------------------------------------------------------------
==> Transaction Table 
==>  state      0 = IDLE 
                1 = Collecting       2 = Prepared            3 = Committed 
                4 = Forced Abort     5 = Forced Commit       6 = Forced Mixed 
                7 = try again later 
                9 = No TX (Committed)    10= 'a' = Active local TX 
==>  cflags     1 = TX has started storing collecting information 
 
                2 = TX has forced the collecting information 
                4 = Prepared TX needs distributed recovery 
                10= Rollback failed on this TX - mark SMON for recover 
                20= TX has rolled back its updates 
==>  wrap#      is incremented TX slot reuse. 
==>  uel        ? 
==>  scn        SCN for the TX prepare / commit 
==>  dba        is DBA of HEAD of the REDO - Ie: The MOST RECENT CHANG 
------------------------------------------------------------------------------

--通过上面的提示可以知道index为0x1c为未提交事务

SQL> select to_number('800e2b','xxxxxxx') from dual;

TO_NUMBER('800E2B','XXXXXXX')
-----------------------------
                      8392235

SQL> select dbms_utility.data_block_address_block(8392235) "block",
  2  dbms_utility.data_block_address_file(8392235)  "file" from dual;

     block       file
---------- ----------
      3627          2
--v$transaction表中查询出来的UBAFIL与UBABLK一致

dump undo block

SQL> alter system dump datafile 2 block 3627;

System altered.

UNDO BLK:  
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fdc     
 
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00  <--- 表明指向下一个block 
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00800e2a   <--- 为下一个undo block rdba
*-----------------------------
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a6  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858

--bdba表示undo对应的data block,这里对应的是datafile 9 block 422
--hdba表示chf.t_xifenfei的extent 0的 first block(first mapbit)

SQL> select to_number('800e2a','xxxxxxx') from dual;

TO_NUMBER('800E2A','XXXXXXX')
-----------------------------
                      8392234

SQL> select dbms_utility.data_block_address_block(8392234) "block",
  2  dbms_utility.data_block_address_file(8392234)  "file" from dual;

     block       file
---------- ----------
      3626          2

SQL> alter system dump datafile 2 block 3626;

System altered.

UNDO BLK:  
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fac     
 
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00   <--- 表明指向下一个block 
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000  <--- 下一个block为 0x00000000,表示事务到此为止
*-----------------------------
uba: 0x00800e29.00fd.24 ctl max scn: 0x0b2c.c02c99cc prv tx scn: 0x0b2c.c02c9a27
txn start scn: scn: 0x0b2c.c02ca0b3 logon user: 0
 prev brb: 8392230 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a5  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858

--bdba表示undo对应的data block,这里对应的是datafile 9 block 421

XID: this is the transaction ID, which matches the value made up previously from the query on V$TRANSACTION.

CNT: this is the number of undo records in this block.

IRB: this is the index if the first record to be considered in case of a rollback.

OBJN: this is the object number being altered by the undo record. This matches the value previously queried from DBA_OBJECTS.

RCI: this is the next undo record in the chain to be examined. When rolling back transactions, these undo records are applied one by one on the data blocks to undo the changes.

1. undo链表的意思为:从IRB表示undo 回滚的开始,RCI表示未IRB对应的下一条记录,依次类推。当RCI为0,而且rdba不为0的时候,表示undo 记录关联到下一个block;当RCI为0,而且rdba也为0表示undo结束
2. undo事务回滚是倒序的,这里可以看到事务先读取block 422,再读取block 421

dump data block

SQL> alter system dump datafile 9 block 421;

System altered.

Block header dump:  0x024001a5
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bc  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.22  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2a.00fd.01  ----    1  fsc 0x0000.00000000

SQL> alter system dump datafile 9 block 422;

System altered.

Block header dump:  0x024001a6
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bd  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.23  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2b.00fd.01  ----    1  fsc 0x0000.00000000

这里可以看到Xid,Uba和上面计算出来的一致,看出来事务未提交标记和上述一致.

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

undo异常事务回滚规则分析》有 1 条评论

  1. 惜分飞 说:

    补充在单个undo block中回滚记录顺序判断规则

    ********************************************************************************
    UNDO BLK:
    xid: 0x0001.004.000001c5 seq: 0x18 cnt: 0xe irb: 0xe icl: 0x0 flg: 0x0000     <==== (1)
    
     Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
    ---------------------------------------------------------------------------
    0x01 0x0734     0x02 0x0698     0x03 0x0624     0x04 0x05b0     0x05 0x053c
    0x06 0x04c8     0x07 0x0454     0x08 0x03e0     0x09 0x036c     0x0a 0x02f8
    0x0b 0x0284     0x0c 0x01e8     0x0d 0x0174     0x0e 0x0100
    
    ... /* Dumps of undo records 0x01 - 0x0b left out here for clarity. */
    
    *-----------------------------
    * Rec #0xc  slt: 0x04  objn: 10480(0x000028f0)  objd: 10480  tblspc: 0(0x00000000)     <===== (5)
    *       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: 0x0080f1e4.0018.02 ctl max scn: 0x0000.00039cb4 prv tx scn: 0x0000.00039d36
    txn start scn: scn: 0x0000.00000000 logon user: 0
     prev brb: 8450530 prev bcl: 0
    KDO undo record:
    KTB Redo 
    op: 0x03 ver: 0x01 
    op: Z
    KDO Op code: URP row dependencies Disabled
      xtype: XA flags: 0x00000000  bdba: 0x00416b22  hdba: 0x00416b21
    itli: 2  ispac: 0  maxfr: 1177
    tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
    ncol: 1 nnew: 1 size: -29
    col  0: [29]
     54 68 69 73 20 69 73 20 72 6f 77 20 31 20 77 69 74 68 20 73 6f 6d 65 20 74
     65 78 74 2e
    
    *-----------------------------
    * Rec #0xd  slt: 0x04  objn: 10480(0x000028f0)  objd: 10480  tblspc: 0(0x00000000)     <===== (4)
    *       Layer:  11 (Row)   opc: 1   rci 0x0c 
    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: 0x0080f1e4.0018.0c
    KDO Op code: URP row dependencies Disabled
      xtype: XA flags: 0x00000000  bdba: 0x00416b22  hdba: 0x00416b21
    itli: 2  ispac: 0  maxfr: 1177
    tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 8
    ncol: 1 nnew: 1 size: -29
    col  0: [29]
     54 68 69 73 20 69 73 20 72 6f 77 20 32 20 77 69 74 68 20 73 6f 6d 65 20 74
     65 78 74 2e
    
    *-----------------------------
    * Rec #0xe  slt: 0x04 objn: 10480(0x000028f0) objd: 10480 tblspc: 0(0x00000000)     <===== (2)
    *       Layer:  11 (Row)   opc: 1   rci 0x0d 
    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: 0x0080f1e4.0018.0d
    KDO Op code: URP row dependencies Disabled
      xtype: XA flags: 0x00000000  bdba: 0x00416b22  hdba: 0x00416b21     <===== (3)
    itli: 2  ispac: 0  maxfr: 1177
    tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 8
    ncol: 1 nnew: 1 size: -29
    col  0: [29]
     54 68 69 73 20 69 73 20 72 6f 77 20 33 20 77 69 74 68 20 73 6f 6d 65 20 74
     65 78 74 2e