oracle事务提交前更新机制,ORACLE事务机制的一些个人理解

最新推荐文章于 2023-02-24 16:38:14 发布

超高校级维京猛男

最新推荐文章于 2023-02-24 16:38:14 发布

阅读量249

点赞数

文章标签： oracle事务提交前更新机制

一、分析undo segment header bock的结构

##dump undo段头块

SQL> alter system dump undo header '_SYSSMU7_3286610060$';

System altered.

SQL> select * from v$diag_info;

SQL> select * from v$diag_info where name='Default Trace File';

INST_ID NAME VALUE

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

1 Default Trace File /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_5131.trc

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

0x00 9 0x00 0x6eef 0x0007 0x0000.04f8de47 0x00c03dc2 0x0000.000.00000000 0x00000003 0x00000000 1467124072

0x01 9 0x00 0x6efb 0x001c 0x0000.04f8dcd4 0x00c03daf 0x0000.000.00000000 0x00000003 0x00000000 1467123537

0x02 9 0x00 0x6ef7 0x001a 0x0000.04f8de59 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1467124073

0x03 9 0x00 0x6ef0 0x0018 0x0000.04f8de68 0x00c03dcf 0x0000.000.00000000 0x00000003 0x00000000 1467124073

0x04 9 0x00 0x6f05 0x0020 0x0000.04f8de71 0x00c03dd3 0x0000.000.00000000 0x00000003 0x00000000 1467124073

// 0x05 10 0x80 0x6f03 0x0023 0x0000.04f8d0de 0x00c03dd3 0x0000.000.00000000 0x00000001 0x00000000 0

#猜测state=9,表示已经事务，state=10表示事务未提交

SQL> select to_number('3dd3','XXXXXXXXXXXXX') from dual;

TO_NUMBER('3DD3','XXXXXXXXXXXXX')

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

15827 //v$transaction中UBABLK字段值对应

SQL> select to_number('6f03','XXXXXXXXXXXXX') from dual;

TO_NUMBER('6F03','XXXXXXXXXXXXX')

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

28419 //v$transaction XIDSLOT字段值对应

二、描述延迟块清除的过程

事务发生时会从回滚段获取一个 ITL(事务槽)，分配空间，记录事务相关信息，Transaction 提交后，redo 完成记录，同时还清除回滚段事务信息

包括行级锁，ITL 信息(commit 标志，SCN 等)清除这些事务段的信息的过程就叫做块清除

三、用实验证明DDL操作有两个COMMIT

session1:

SQL> create table ilvtu(id number,name varchar2(30),age number);

Table created.

SQL> select * from ichangxin;

ID NAME

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

1 yy

1 DX

1 FH

SQL> delete from ichangxin where name='yy';

1 row deleted.

SQL> select * from ichangxin;

ID NAME

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

1 DX

1 FH

SQL> drop table ilvtu;

Table dropped.

session 2:

#update操作后，drop操作前

SQL> select * from ichangxin;

ID NAME

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

1 yy

1 DX

1 FH

#drop操作后

SQL> select * from ichangxin;

ID NAME

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

1 DX

1 FH

四：用一条UPDATE语句把REDO、UNDO、DATA关联起来(通过实验一步步分析)

事务的过程：

1.开始时，去绑定一个回滚段(RBS)，回滚段对应一个表，找到undo回滚段的段头(header)那个块里面，找到undo块(涉及到UNDO块的分配)

2.undo回滚段的段头块存放着一张事务表(TX-TABLE)，事务表由槽位组成。事务开始时，会分配一个slot

XID=USN+SLOT+SQN

3.slot会有一把锁，包括一些信息，包含undo块指针，指向undo block实际地址

4.执行数据修改，即逻辑操作

5.以上原子操作被记录在redo日志

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blockid,ichangxin.* from ichangxin;

FILE# BLOCKID ID NAME

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

1 92873 1 YY

1 92873 1 DX

1 92873 1 FH

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

4 1 13 209715200 512 1 YES INACTIVE 83409942 27-JUN-16 83409961 27-JUN-16

5 1 14 209715200 512 1 YES ACTIVE 83409961 27-JUN-16 83418585 28-JUN-16

6 1 15 209715200 512 1 NO CURRENT 83418585 28-JUN-16 2.8147E+14

//当前REDO日志组

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_REC

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

4 ONLINE /u01/oracle/oradata/edus/redo04.log NO

5 ONLINE /u01/oracle/oradata/edus/redo05.log NO

6 ONLINE /u01/oracle/oradata/edus/redo06.log NO

#查看v$transaction视图，注解如下：

XIDUSN:回滚段号为7，对应名称为_SYSSMU7_3286610060$的回滚段

XIDSLOT：第5号事务槽

XIDSQN:事务槽复用序列号

UBAFIL:UNDO块所在文件号为3

UBABLK:所使用UNDO块在3号文件第15827号块

SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS

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

7 5 28419 3 15827 575 4 ACTIVE

SQL> select * from v$rollname;

USN NAME

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

0 SYSTEM

1 _SYSSMU1_3780397527$

2 _SYSSMU2_2232571081$

3 _SYSSMU3_2097677531$

4 _SYSSMU4_1152005954$

5 _SYSSMU5_1527469038$

6 _SYSSMU6_2443381498$

7 _SYSSMU7_3286610060$

8 _SYSSMU8_2012382730$

9 _SYSSMU9_1424341975$

10 _SYSSMU10_3550978943$

11 rows selected.