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列表中的序號，即哪個事務在該記錄上產生的鎖。

關注公眾號：數據庫技術分享，不定期分享技術干貨