mysql的innodb引擎itl_bbed模拟提交事务一之修改itl – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 134...

我们都知道，根据oracle 事务的一致性,当我们在session 1中进行dml操作,如果未提交在其他会话中是无法看到修改后的值(只能看到修改前的值).这里通过bbed模拟部分提交事务从而实现在其他会话中查看到另外会话未提交事务(本质已经部分模拟提交,还有undo segment header中信息未清理,下篇补充)

create table(session 1)

SQL> create table chf.t_xifenfei

2 as

3 select object_id,object_name from dba_objects where rownum<20;

Table created.

SQL> select rowid,

2 dbms_rowid.rowid_relative_fno(rowid) rel_fno,

3 dbms_rowid.rowid_block_number(rowid) block_no

4 from chf.t_xifenfei;

ROWID REL_FNO BLOCK_NO

------------------ ---------- ----------

AAANL3AAEAAAAAcAAA 4 28

AAANL3AAEAAAAAcAAB 4 28

AAANL3AAEAAAAAcAAC 4 28

AAANL3AAEAAAAAcAAD 4 28

AAANL3AAEAAAAAcAAE 4 28

AAANL3AAEAAAAAcAAF 4 28

AAANL3AAEAAAAAcAAG 4 28

AAANL3AAEAAAAAcAAH 4 28

AAANL3AAEAAAAAcAAI 4 28

AAANL3AAEAAAAAcAAJ 4 28

AAANL3AAEAAAAAcAAK 4 28

AAANL3AAEAAAAAcAAL 4 28

AAANL3AAEAAAAAcAAM 4 28

AAANL3AAEAAAAAcAAN 4 28

AAANL3AAEAAAAAcAAO 4 28

AAANL3AAEAAAAAcAAP 4 28

AAANL3AAEAAAAAcAAQ 4 28

AAANL3AAEAAAAAcAAR 4 28

AAANL3AAEAAAAAcAAS 4 28

19 rows selected.

SQL> select * from chf.t_xifenfei;

OBJECT_ID OBJECT_NAME

---------- --------------------

20 ICOL$

44 I_USER1

28 CON$

15 UNDO$

29 C_COBJ#

3 I_OBJ#

25 PROXY_ROLE_DATA$

39 I_IND1

51 I_CDEF2

26 I_PROXY_ROLE_DATA$_1

17 FILE$

13 UET$

9 I_FILE#_BLOCK#

41 I_FILE1

48 I_CON1

38 I_OBJ3

7 I_TS#

53 I_CDEF4

19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

update record(session 1)

SQL> update chf.t_xifenfei set object_name ='www.xifenfei.com' where rownum<15;

14 rows updated.

SQL> col object_name for a20

SQL> select * from chf.t_xifenfei;

OBJECT_ID OBJECT_NAME

---------- --------------------

20 www.xifenfei.com

44 www.xifenfei.com

28 www.xifenfei.com

15 www.xifenfei.com

29 www.xifenfei.com

3 www.xifenfei.com

25 www.xifenfei.com

39 www.xifenfei.com

51 www.xifenfei.com

26 www.xifenfei.com

17 www.xifenfei.com

13 www.xifenfei.com

9 www.xifenfei.com

41 www.xifenfei.com

48 I_CON1

38 I_OBJ3

7 I_TS#

53 I_CDEF4

19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

--注意flush buffer_cache(不然后面bbed修改会被不能通过select显示,而且会被覆盖)

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987

0x02 0x000a.00a.0000017e 0x0081ffc7.01a2.22 ---- 14 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bbed commit Transaction(session 3)

BBED> p ktbbh

struct ktbbh, 96 bytes @20

ub1 ktbbhtyp @20 0x01 (KDDBTDATA)

union ktbbhsid, 4 bytes @24

ub4 ktbbhsg1 @24 0x0000d2f7

ub4 ktbbhod1 @24 0x0000d2f7

struct ktbbhcsc, 8 bytes @28

ub4 kscnbas @28 0xc02d1aec

ub2 kscnwrp @32 0x0b2c

b2 ktbbhict @36 3

ub1 ktbbhflg @38 0x32 (NONE)

ub1 ktbbhfsl @39 0x00

ub4 ktbbhfnx @40 0x01000019

struct ktbbhitl[0], 24 bytes @44

struct ktbitxid, 8 bytes @44

ub2 kxidusn @44 0xffff

ub2 kxidslt @46 0x0000

ub4 kxidsqn @48 0x00000000

struct ktbituba, 8 bytes @52

ub4 kubadba @52 0x00000000

ub2 kubaseq @56 0x0000

ub1 kubarec @58 0x00

ub2 ktbitflg @60 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @62

b2 _ktbitfsc @62 2860

ub2 _ktbitwrp @62 0x0b2c

ub4 ktbitbas @64 0xc02d1987

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x000a

ub2 kxidslt @70 0x000a

ub4 kxidsqn @72 0x0000017e

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x0081ffc7

ub2 kubaseq @80 0x01a2

ub1 kubarec @82 0x22

ub2 ktbitflg @84 0x000e (NONE)

union _ktbitun, 2 bytes @86

b2 _ktbitfsc @86 0

ub2 _ktbitwrp @86 0x0000

ub4 ktbitbas @88 0x00000000

struct ktbbhitl[2], 24 bytes @92

struct ktbitxid, 8 bytes @92

ub2 kxidusn @92 0x0000

ub2 kxidslt @94 0x0000

ub4 kxidsqn @96 0x00000000

struct ktbituba, 8 bytes @100

ub4 kubadba @100 0x00000000

ub2 kubaseq @104 0x0000

ub1 kubarec @106 0x00

ub2 ktbitflg @108 0x0000 (NONE)

union _ktbitun, 2 bytes @110

b2 _ktbitfsc @110 0

ub2 _ktbitwrp @110 0x0000

ub4 ktbitbas @112 0x00000000

BBED> m /x 0080 offset 84

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /u01/oracle/oradata/XFF/users01.dbf (0)

Block: 28 Offsets: 84 to 115 Dba:0x00000000

------------------------------------------------------------------------

00800000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 0, Block 28:

current = 0x03dc, required = 0x03dc

BBED> p ktbbh

struct ktbbh, 96 bytes @20

…………

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x000a

ub2 kxidslt @70 0x000a

ub4 kxidsqn @72 0x0000017e

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x0081ffc7

ub2 kubaseq @80 0x01a2

ub1 kubarec @82 0x22

ub2 ktbitflg @84 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @86

b2 _ktbitfsc @86 0

ub2 _ktbitwrp @86 0x0000

ub4 ktbitbas @88 0x00000000

…………

dump block(session 2)

SQL> alter system dump datafile 4 block 28;

System altered.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0b2c.c02d1987

0x02 0x000a.00a.0000017e 0x0081ffc7.01a2.22 C--- 0 scn 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

select data(session 4)

SQL> select object_id,object_name from chf.t_xifenfei;

OBJECT_ID OBJECT_NAME

---------- --------------------

20 www.xifenfei.com

44 www.xifenfei.com

28 www.xifenfei.com

15 www.xifenfei.com

29 www.xifenfei.com

3 www.xifenfei.com

25 www.xifenfei.com

39 www.xifenfei.com

51 www.xifenfei.com

26 www.xifenfei.com

17 www.xifenfei.com

13 www.xifenfei.com

9 www.xifenfei.com

41 www.xifenfei.com

48 I_CON1

38 I_OBJ3

7 I_TS#

53 I_CDEF4

19 IND$

19 rows selected.

这里可以看到,已经模拟出来在其他session中可以访问数据库为commit的记录(在该block级别已经模拟了commit)