oracle如何保证数据一致性和避免脏读

oracle通过undo保证一致性读和不发生脏读

1.不发生脏读
2.一致性读
3. 事务槽（ITL）小解

1.不发生脏读

例如：用户A对表更新了，没有提交，用户B对进行查询，没有提交的更新不能出现在用户的查询结果中

举例并通个dump数据块说明避免脏读的原理

创建测试表，并插入两条记录，会话A执行更新但不提交

  1. SQL>select*from test;
  3. ID NAME
  4. --------------------
  5. 1 A
  6. 2 B
  8. SQL> update test set name='C'where id=2;
  10. 1 row updated.

会话B查询，数据没变

  1. SQL>select*from test;
  3. ID NAME
  4. --------------------
  5. 1 A
  6. 2 B

通过下面sql语句查询数据所在的数据文件和块号，并进行dump

  1. SQL>select id, rowid, dbms_rowid.rowid_relative_fno(rowid) fn,dbms_rowid.rowid_block_number(rowid) bk from test order by id;
  3. ID ROWID FN BK
  4. ------------------------------------------------
  5. 1AAAzkeAAIAAAACDAAA8131
  6. 2AAAzkeAAIAAAACDAAB8131
  8. SQL> alter system dump datafile 8 block 139;
  10. System altered.

未提交的数据块dump结果

  1. ItlXidUbaFlagLckScn/Fsc
  2. 0x010x0002.001.000a90a20x00c00093.9f1d.16 C---0 scn 0x0000.395178de
  3. 0x020x0007.010.000a93c50x00c00f4b.9f5d.34----1 fsc 0x0000.00000000
  5. ---上面事务槽中Flag为----从0x00c00f4b undo地址中读取
  7. bdba:0x0200008b
  8. data_block_dump,data header at 0x7fb742fc8a64
  9. ===============
  10. tsiz:0x1f98
  11. hsiz:0x16
  12. pbl:0x7fb742fc8a64
  13. 76543210
  14. flag=--------
  15. ntab=1
  16. nrow=2
  17. frre=-1
  18. fsbo=0x16
  19. fseo=0x1f88
  20. avsp=0x1f70
  21. tosp=0x1f70
  22. 0xe:pti[0] nrow=2 offs=0
  23. 0x12:pri[0] offs=0x1f90
  24. 0x14:pri[1] offs=0x1f88
  26. block_row_dump:---下面是表中数据行的dump
  27. tab 0, row 0,@0x1f90
  28. tl:8 fb:--H-FL-- lb:0x0 cc:2
  29. col 0:[2] c1 02
  30. col 1:[1]41--第一行 A
  31. tab 0, row 1,@0x1f88
  32. tl:8 fb:--H-FL-- lb:0x2 cc:2---lb为0x2说明未提交
  33. col 0:[2] c1 03
  34. col 1:[1]43---第二行 C
  35. end_of_block_dump

说明：通过上面的dump文件发现，数据已经被修改成C（43），但是Oracle发现这条数据有lb: 0x2 对应的是ITL，从ltl为0x02的记录看到flag为—-，表示有事务标记，数据被加锁，需要从undo段的uba（undo block address）中读取

undo段中对应的块信息为：0x00c00f4b，这里是十六进制，下面先转换成10进制

  1. SQL>select to_number('00c00f4b','XXXXXXXXXXXXXXX')from dual;
  3. TO_NUMBER('00C00F4B','XXXXXXXXXXXXXXX')
  4. ---------------------------------------
  5. 12586827
  6. 得到值为12586827

对undo块进行dump

  1. 在通过下面的语句得到数据块信息：
  2. SQL>select dbms_utility.data_block_address_file(12586827), dbms_utility.data_block_address_block(12586827)from dual;
  4. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12586827) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12586827)
  5. ---------------------------------------------------------------------------------------------
  6. 33915
  9. SQL>select file#,name from v$datafile where file#=3;
  11. FILE# NAME
  12. ------------------------------------------------------------
  13. 3/u01/app/oracle/oradata/FGLDB/undotbs01.dbf
  15. SQL> alter system dump datafile 3 block 3915;
  17. System altered.

dump内容如下：

  1. *-----------------------------
  2. *Rec#0x34 slt: 0x10 objn: 211231(0x0003391f) objd: 211231 tblspc: 4(0x00000004) --objd 为object id
  3. *Layer:11(Row) opc:1 rci 0x00
  4. Undo type:Regular undo Begin trans Last buffer split:No
  5. TempObject:No
  6. TablespaceUndo:No
  7. rdba:0x00000000Ext idx:0
  8. flg2:0
  9. *-----------------------------
  10. uba:0x00c00f4b.9f5d.25 ctl max scn:0x0000.39516d48 prv tx scn:0x0000.39516db3
  11. txn start scn: scn:0x0000.395178de logon user:83
  12. prev brb:12586818 prev bcl:0
  13. KDO undo record:
  14. KTB Redo
  15. op:0x03 ver:0x01
  16. compat bit:4(post-11) padding:1
  17. op: Z
  18. ArrayUpdate of 1 rows:
  19. tabn:0 slot:1(0x1) flag:0x2clock:0 ckix:12
  20. ncol:2 nnew:1 size:0
  21. KDO Op code:21 row dependencies Disabled
  22. xtype:XAxtype KDO_KDOM2 flags:0x00000080 bdba:0x0200008b hdba:0x0200008a
  23. itli:2 ispac:0 maxfr:4858
  24. vect =3
  25. col 1:[1]42--- undo中数据为B

从undo块中发现值为B（42），故其他用户看到的是B，看不到C，避免了脏读

附：提交后的数据块dump结果

  1. ItlXidUbaFlagLckScn/Fsc
  2. 0x010x0002.001.000a90a20x00c00093.9f1d.16 C---0 scn 0x0000.395178de
  3. 0x020x0007.010.000a93c50x00c00f4b.9f5d.34 C---0 scn 0x0000.39517d7a
  4. ---Flag为C---
  6. bdba:0x0200008b
  7. data_block_dump,data header at 0x7fa3c77efa64
  8. ===============
  9. tsiz:0x1f98
  10. hsiz:0x16
  11. pbl:0x7fa3c77efa64
  12. 76543210
  13. flag=--------
  14. ntab=1
  15. nrow=2
  16. frre=-1
  17. fsbo=0x16
  18. fseo=0x1f88
  19. avsp=0x1f70
  20. tosp=0x1f70
  21. 0xe:pti[0] nrow=2 offs=0
  22. 0x12:pri[0] offs=0x1f90
  23. 0x14:pri[1] offs=0x1f88
  24. block_row_dump:
  25. tab 0, row 0,@0x1f90
  26. tl:8 fb:--H-FL-- lb:0x0 cc:2
  27. col 0:[2] c1 02
  28. col 1:[1]41
  29. tab 0, row 1,@0x1f88
  30. tl:8 fb:--H-FL-- lb:0x0 cc:2--- lb为0x0
  31. col 0:[2] c1 03
  32. col 1:[1]43
  33. end_of_block_dump


总结：数据库通过判断数据块头部的ITL槽的信息来确定是否有未提交的事务，如果事务槽Flag为—-，则通过事务槽中的undo块地址查询到原来的数据，进而达到数据隔离的效果，避免脏读。

2.一致性读

例如：假设某一个用户A在6点对某一个表发出了一个查询数据量很大的数据，需要15分钟才能把结果完全查询出来，在这期间，6点10分用户B对数据进行了更新并提交了，用户A查询的结果仍然是6点时候的表的数据，用户B更新的数据不出现在用户A的查询结果中，这就是一致性读。

  1. 用户A在执行开始的时候会记录当时的SCN号，如图中10021

  2. 在每次从数据块中读数据的时候会比较记录的SCN号和数据块事务槽中的SCN号（下一个事务的SCN号一定比当前的大）
    a.如果数据块中的SCN比当前分配的SCN号小，则认为该数据没有被修改，直接读取；
    b.如果数据块中的SCN号比当前分配的SCN大，则根据块中保存的地址去undo中读取当时分配SCN时间点的数据（根据undo数据块在内存中重新构造出该数据块，称为consistent read (CR)块）

    一个查询如果耗费很长时间，而查询的结果在查询的阶段被更改了，而且对应着undo段的数据已经被清理了，就会发生Oracle中著名的ORA-01555： snapshot too old（快照太久）错误。

    如果一条数据在查询期间被更新过多次并且提交，后放入undo段的块会记录相对的块上次放在undo段中的块地址，从而一路寻找到查询开始时间点在undo段中的数据块。

3. 事务槽（ITL）小解


ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分，位于数据块头（block header），itl由xid，uba，flag，lck和scn/fsc组成，用来记录该块所有发生的事务，一个itl可以看作是一条事务记录。当然，如果这个事务已经提交，那么这个itl的位置就可以被反复使用了，因为itl类似记录，所以，有的时候也叫itl槽位。如果一个事务一直没有提交，那么，这个事务将一直占用一个itl槽位，itl里面记录了事务信息，回滚段的入口，事务类型等等。如果这个事务已经提交，那么，itl槽位中还保存的有这个事务提交时候的SCN号。

Xid：事务id，在回滚段事务表中有一条记录和这个事务对应
Uba：回滚段地址，该事务对应的回滚段地址

  • 第一段地址：回滚数据块的地址，包括回滚段文件号和数据块号
  • 第二段地址：回滚序列号
  • 第三段地址：回滚记录号
    –查看UBA
    SELECT UBAFIL undo_file_id,UBABLK undo_blk_num,UBASQN undo_segment_num,UBAREC undo_recode_num FROM v$transaction;

Flag：事务标志位。这个标志位就记录了这个事务的操作，各个标志的含义分别是：

  • —– = 事务是活动的，或者在块清除前提交事务
  • C— = 事务已经提交并且清除了行锁定。
  • -B– = this undo record contains the undo for this ITL entry
  • –U- = 事务已经提交（SCN已经是最大值），但是锁定还没有清除（快速清除）。
  • —T = 当块清除的SCN被记录时，该事务仍然是活动的，块上如果有已经提交的事务，

那么在clean ount的时候，块会被进行清除，但是这个块里面的事务不会被清除。

Lck：影响的记录数

Scn/Fsc：快速提交（Fast Commit Fsc）的SCN或者Commit SCN。

每条记录中的行级锁对应于Itl列表中的序号，即哪个事务在该记录上产生的锁。


关注公众号：数据库技术分享，不定期分享技术干货

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/haoxiaoyu/p/314e2d07c0ab6c86a6378d79fd0facdf.html