Chinaunix首页 | 论坛 | 博客

吕途的ChinaUnix博客

首页　| 　博文目录　| 　关于我

lvchangxin1987

  • 博客访问： 96796
  • 博文数量： 20
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 202
  • 用 户 组： 普通用户
  • 注册时间： 2013-09-07 01:56
个人简介

数据库技术爱好者

文章分类

全部博文（20）

文章存档

2016年（11）

2015年（9）

我的朋友
最近访客
推荐博文
相关博文
ORACLE事务机制的一些个人理解

分类： Oracle

2016-06-26 23:52:01


点击(此处)折叠或打开

  1. 一、分析undo segment header bock的结构
  2. ##dump undo段头块
  3. SQL> alter system dump undo header '_SYSSMU7_3286610060$';

  5. System altered.

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

  10.    INST_ID NAME VALUE
  11. ---------- -------------------- ----------------------------------------------------------------------------------------------------
  12.          1 Default Trace File /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_5131.trc
  13.         
  14.  index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
  15.   ------------------------------------------------------------------------------------------------
  16.    0x00 9 0x00 0x6eef 0x0007 0x0000.04f8de47 0x00c03dc2 0x0000.000.00000000 0x00000003 0x00000000 1467124072
  17.    0x01 9 0x00 0x6efb 0x001c 0x0000.04f8dcd4 0x00c03daf 0x0000.000.00000000 0x00000003 0x00000000 1467123537
  18.    0x02 9 0x00 0x6ef7 0x001a 0x0000.04f8de59 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1467124073
  19.    0x03 9 0x00 0x6ef0 0x0018 0x0000.04f8de68 0x00c03dcf 0x0000.000.00000000 0x00000003 0x00000000 1467124073
  20.    0x04 9 0x00 0x6f05 0x0020 0x0000.04f8de71 0x00c03dd3 0x0000.000.00000000 0x00000003 0x00000000 1467124073
  21. // 0x05 10 0x80 0x6f03 0x0023 0x0000.04f8d0de 0x00c03dd3 0x0000.000.00000000 0x00000001 0x00000000 0
  22. #猜测state=9,表示已经事务，state=10表示事务未提交
  23. SQL> select to_number('3dd3','XXXXXXXXXXXXX') from dual;

  25. TO_NUMBER('3DD3','XXXXXXXXXXXXX')
  26. ---------------------------------
  27.                             15827 //v$transaction中UBABLK字段值对应
  28. SQL> select to_number('6f03','XXXXXXXXXXXXX') from dual;

  30. TO_NUMBER('6F03','XXXXXXXXXXXXX')
  31. ---------------------------------
  32.                             28419 //v$transaction XIDSLOT字段值对应
  33.                             
  34.                             
  35. 二、描述延迟块清除的过程
  36. 事务发生时会从回滚段获取一个 ITL(事务槽)，分配空间，记录事务相关信息，Transaction 提交后，redo 完成记录，同时还清除回滚段事务信息
  37. 包括行级锁，ITL 信息(commit 标志，SCN 等)清除这些事务段的信息的过程就叫做块清除


  40. 三、用实验证明DDL操作有两个COMMIT
  41. session1:
  42. SQL> create table ilvtu(id number,name varchar2(30),age number);

  44. Table created.

  46. SQL> select * from ichangxin;

  48.         ID NAME
  49. ---------- ------------------------------------------------------------
  50.          1 yy
  51.          1 DX
  52.          1 FH

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

  56. 1 row deleted.

  58. SQL> select * from ichangxin;

  60.         ID NAME
  61. ---------- ------------------------------------------------------------
  62.          1 DX
  63.          1 FH

  65. SQL> drop table ilvtu;

  67. Table dropped.

  69. session 2:
  70. #update操作后，drop操作前
  71. SQL> select * from ichangxin;

  73.         ID NAME
  74. ---------- ------------------------------------------------------------
  75.          1 yy
  76.          1 DX
  77.          1 FH
  78. #drop操作后
  79. SQL> select * from ichangxin;

  81.         ID NAME
  82. ---------- ------------------------------------------------------------
  83.          1 DX
  84.          1 FH





  90. 四：用一条UPDATE语句把REDO、UNDO、DATA关联起来（通过实验一步步分析）
  91. 事务的过程：
  92. 1.开始时，去绑定一个回滚段(RBS)，回滚段对应一个表，找到undo回滚段的段头(header)那个块里面，找到undo块(涉及到UNDO块的分配）
  93. 2.undo回滚段的段头块存放着一张事务表（TX-TABLE)，事务表由槽位组成。事务开始时，会分配一个slot
  94. XID=USN+SLOT+SQN

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

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

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

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

  104.      FILE# BLOCKID ID NAME
  105. ---------- ---------- ---------- ------------------------------------------------------------
  106.          1 92873 1 YY
  107.          1 92873 1 DX
  108.          1 92873 1 FH

  110. SQL> alter system switch logfile;

  112. System altered.


  115. SQL> select * from v$log;

  117.     GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
  118. ---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------
  119.          4 1 13 209715200 512 1 YES INACTIVE 83409942 27-JUN-16 83409961 27-JUN-16
  120.          5 1 14 209715200 512 1 YES ACTIVE 83409961 27-JUN-16 83418585 28-JUN-16
  121.          6 1 15 209715200 512 1 NO CURRENT 83418585 28-JUN-16 2.8147E+14
  122.                                                                         //当前REDO日志组
  123. SQL> select * from v$logfile;

  125.     GROUP# STATUS TYPE MEMBER IS_REC
  126. ---------- -------------- -------------- -------------------------------------------------- ------
  127.          4 ONLINE /u01/oracle/oradata/edus/redo04.log NO
  128.          5 ONLINE /u01/oracle/oradata/edus/redo05.log NO
  129.          6 ONLINE /u01/oracle/oradata/edus/redo06.log NO


  132. #查看v$transaction视图，注解如下：
  133. XIDUSN:回滚段号为7，对应名称为_SYSSMU7_3286610060$的回滚段
  134. XIDSLOT：第5号事务槽
  135. XIDSQN:事务槽复用序列号
  136. UBAFIL:UNDO块所在文件号为3
  137. UBABLK:所使用UNDO块在3号文件第15827号块        
  138. SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;

  140.     XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
  141. ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------
  142.          7 5 28419 3 15827 575 4 ACTIVE
  143.         

  145. SQL> select * from v$rollname;

  147.        USN NAME
  148. ---------- ------------------------------------------------------------
  149.          0 SYSTEM
  150.          1 _SYSSMU1_3780397527$
  151.          2 _SYSSMU2_2232571081$
  152.          3 _SYSSMU3_2097677531$
  153.          4 _SYSSMU4_1152005954$
  154.          5 _SYSSMU5_1527469038$
  155.          6 _SYSSMU6_2443381498$
  156.          7 _SYSSMU7_3286610060$
  157.          8 _SYSSMU8_2012382730$
  158.          9 _SYSSMU9_1424341975$
  159.         10 _SYSSMU10_3550978943$

  161. 11 rows selected.

  163. ##dump undo段头块
  164. SQL> alter system dump undo header '_SYSSMU7_3286610060$';

  166. System altered.

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

  171.    INST_ID NAME VALUE
  172. ---------- -------------------- ----------------------------------------------------------------------------------------------------
  173.          1 Default Trace File /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_5131.trc
  174.         
  175.  index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
  176.   ------------------------------------------------------------------------------------------------
  177.    0x00 9 0x00 0x6eef 0x0007 0x0000.04f8de47 0x00c03dc2 0x0000.000.00000000 0x00000003 0x00000000 1467124072
  178.    0x01 9 0x00 0x6efb 0x001c 0x0000.04f8dcd4 0x00c03daf 0x0000.000.00000000 0x00000003 0x00000000 1467123537
  179.    0x02 9 0x00 0x6ef7 0x001a 0x0000.04f8de59 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1467124073
  180.    0x03 9 0x00 0x6ef0 0x0018 0x0000.04f8de68 0x00c03dcf 0x0000.000.00000000 0x00000003 0x00000000 1467124073
  181.    0x04 9 0x00 0x6f05 0x0020 0x0000.04f8de71 0x00c03dd3 0x0000.000.00000000 0x00000003 0x00000000 1467124073
  182. // 0x05 10 0x80 0x6f03 0x0023 0x0000.04f8d0de 0x00c03dd3 0x0000.000.00000000 0x00000001 0x00000000 0
  183. #猜测state=9,表示已经事务，state=10表示事务未提交
  184. SQL> select to_number('3dd3','XXXXXXXXXXXXX') from dual;

  186. TO_NUMBER('3DD3','XXXXXXXXXXXXX')
  187. ---------------------------------
  188.                             15827 //v$transaction中UBABLK字段值对应
  189. SQL> select to_number('6f03','XXXXXXXXXXXXX') from dual;

  191. TO_NUMBER('6F03','XXXXXXXXXXXXX')
  192. ---------------------------------
  193.                             28419 //v$transaction XIDSLOT字段值对应


  196. #DUMP UNDO块                            
  197. SQL> alter system dump datafile 3 block 15827;

  199. System altered.

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

  203.    INST_ID
  204. ----------
  205. NAME
  206. --------------------------------------------------------------------------------
  207. VALUE
  208. --------------------------------------------------------------------------------
  209.          1
  210. Default Trace File
  211. /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_5427.trc
  212. #UNDO块DUMP内容
  213. UNDO BLK:
  214. xid: 0x0007.005.00006f03 seq: 0x23f cnt: 0x4 irb: 0x4 icl: 0x0 flg: 0x0000 //XID三部分内容

  216. Array Update of 1 rows:
  217. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  218. ncol: 2 nnew: 1 size: 0
  219. KDO Op code: 21 row dependencies Disabled
  220.   xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416ac9 hdba: 0x00416ac8 //bdba指向记录所在数据块
  221. itli: 2 ispac: 0 maxfr: 4863
  222. vect = 3
  223. col 1: [ 2] 59 59 //更改前的值

  225. SQL> select dump('yy',16) from dual;

  227. DUMP('YY',16)
  228. --------------------------------------
  229. Typ=96 Len=2: 79,79

  231. SQL> select dump('YY',16) from dual;

  233. DUMP('YY',16)
  234. --------------------------------------
  235. Typ=96 Len=2: 59,59



  239. SQL> select object_id from dba_objects where object_name='ICHANGXIN';

  241.  OBJECT_ID
  242. ----------
  243.      91644
  244. #dump redo日志

  246. SQL> alter system dump logfile '/u01/oracle/oradata/edus/redo06.log'
  247.   2 ;

  249. System altered.

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

  253.    INST_ID
  254. ----------
  255. NAME
  256. --------------------------------------------------------------------------------
  257. VALUE
  258. --------------------------------------------------------------------------------
  259.          1
  260. Default Trace File
  261. /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_5976.trc

  263. CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0

  265. #更新后的记录
  266. REDO RECORD - Thread:1 RBA: 0x00000f.00000b6b.0010 LEN: 0x0214 VLD: 0x0d
  267. SCN: 0x0000.04f8dee6 SUBSCN: 1 06/28/2016 07:29:05
  268. CHANGE #1 TYP:2 CLS:1 AFN:1 DBA:0x00416ac9 OBJ:91644 SCN:0x0000.04f8bc2d SEQ:2 OP:11.19 ENC:0 RBL:0
  269. KTB Redo
  270. op: 0x11 ver: 0x01
  271. compat bit: 4 (post-11) padding: 1
  272. op: F xid: 0x0007.005.00006f03 uba: 0x00c03dd3.023f.04
  273. Block cleanout record, scn: 0x0000.04f8dee4 ver: 0x01 opt: 0x02, entries follow...
  274.   itli: 1 flg: 2 scn: 0x0000.04f8bc2d
  275. Array Update of 1 rows:
  276. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
  277. ncol: 2 nnew: 1 size: 0
  278. KDO Op code: 21 row dependencies Disabled
  279.   xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416ac9 hdba: 0x00416ac8
  280. itli: 2 ispac: 0 maxfr: 4863
  281. vect = 3
  282. col 1: [ 2] 79 79
  283. CHANGE #2 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.04f8de81 SEQ:1 OP:5.2 ENC:0 RBL:0
  284. ktudh redo: slt: 0x0005 sqn: 0x00006f03 flg: 0x0012 siz: 188 fbi: 0
  285.             uba: 0x00c03dd3.023f.04 pxid: 0x0000.000.00000000

  287. #UNDO块更改记录
  288. CHANGE #3 TYP:0 CLS:30 AFN:3 DBA:0x00c03dd3 OBJ:4294967295 SCN:0x0000.04f8de7b SEQ:1 OP:5.1 ENC:0 RBL:0
  289. ktudb redo: siz: 188 spc: 7824 flg: 0x0012 seq: 0x023f rec: 0x04
  290.             xid: 0x0007.005.00006f03
  291. ktubl redo: slt: 5 rci: 0 opc: 11.1 [objn: 91644 objd: 91644 tsn: 0]
  292. Undo type: Regular undo Begin trans Last buffer split: No
  293. Temp Object: No
  294. Tablespace Undo: No
  295.              0x00000000 prev ctl uba: 0x00c03dd3.023f.03
  296. prev ctl max cmt scn: 0x0000.04f8dcc8 prev tx cmt scn: 0x0000.04f8dcca
  297. txn start scn: 0x0000.00000000 logon user: 0 prev brb: 12598697 prev bcl: 0 BuExt idx: 0 flg2: 0
  298. KDO undo record:
  299. KTB Redo
  300. op: 0x04 ver: 0x01
  301. compat bit: 4 (post-11) padding: 1
  302. op: L itl: xid: 0x0001.00f.0000991c uba: 0x00c1866f.0293.1e
  303.                       flg: C--- lkc: 0 scn: 0x0000.04f85ca8
  304. Array Update of 1 rows:
  305. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  306. ncol: 2 nnew: 1 size: 0
  307. KDO Op code: 21 row dependencies Disabled
  308.   xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416ac9 hdba: 0x00416ac8
  309. itli: 2 ispac: 0 maxfr: 4863
  310. vect = 3
  311. col 1: [ 2] 59 59

阅读(2799) | 评论(0) | 转发(0) |
0

上一篇：利用codis-ha使用codis 实例故障转移

下一篇：MySQL InnoDB数据块结构详解

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6