HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Current Mode and Consistent Gets

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2011-05-03 03:55:10

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

  Let's begin our story with below 2 test cases.

SQL代码
  1. Case1:  
  2. HELLODBA.COM>set time on  
  3. 10:22:09 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;  
  4.   
  5. 1 row updated.  
  6.   
  7. 10:22:22 HELLODBA.COM>commit;  
  8.   
  9. Commit complete.  
  10.   
  11. Session 1:  
  12. 10:22:25 HELLODBA.COM>update t_test1 set SECONDARY='B' where  object_id = -1 and SECONDARY='B' and (select count(*) from t_test2 t1, t_test2 t2) > 0;  
  13.   
  14. rows updated.  
  15.   
  16. 10:23:15 HELLODBA.COM>  
  17.   
  18. Session 2:  
  19. 10:22:37 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;  
  20.   
  21. 1 row updated.  
  22.   
  23. 10:23:02 HELLODBA.COM>commit;  
  24.   
  25. Commit complete.  
  26.   
  27. 10:23:04 HELLODBA.COM>  
  28.   
  29. Case2:  
  30. 10:25:38 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;  
  31.   
  32. 1 row updated.  
  33.   
  34. 10:25:48 HELLODBA.COM>commit;  
  35.   
  36. Commit complete.  
  37.   
  38. Session 1:  
  39. 10:26:05 HELLODBA.COM>update t_test1 set SECONDARY='B' where  object_id = -1 and SECONDARY='A' and (select count(*) from t_test2 t1, t_test2 t2) > 0;  
  40.   
  41. rows updated.  
  42.   
  43. 10:27:21 HELLODBA.COM>  
  44.   
  45. Session 2:  
  46. 10:26:16 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;  
  47.   
  48. 1 row updated.  
  49.   
  50. 10:26:41 HELLODBA.COM>commit;  
  51.   
  52. Commit complete.  
  53.   
  54. 10:26:42 HELLODBA.COM>  

   If you observed these 2 cases carefully enough, you will find an intereting phenomena: No matter did Session 1 read the data block or not, it finally failed to update the data. The root cause is the difference between Current Mode Read and Consistent Gets.

    As we know, to reduce the concurent conflicts, Oracle invole MVCC(Multiversion Concurrent Control, also known as MCC) method. With MVCC, unless modifying the same records, concurrent transactions will not block each other for consistent reason. They will undo the changes from log to align the data with the SCN that transaction started with. In oracle, such reading action is known as Consistent Reads (CR).

    However, the CR data blocks are just a snapshot with specical timestamp, which means the data is read only. Hence, to modify the data, oracle should read CURRENT data blocks instead, which is DB Gets in Current Mode.

    In an UPDATE, oracle will consistents get the data blocks with spcified filter and TX SCN. Then, with block ID, it reads the data in current mode. Bu if the data blocks were changed during the period time after TX started and before data block read, we will get unexpected result.

    Look back to the 1st case. We started the UPDATE transaction in Session 1 at 10:22:25. However, because of a large subquery, it will read specical data block to be updated tens of seconds later. In Session 2, we updated this data at 10:22:37 and commited it 10:23:02, when before the data block be read in session 1. In session 1, after data changed commited in sesion 2, the transaction read the data block. But because the SCN of tansaction in Session 2 is larger than SCN of transaction in session 1, it read the undo block and rollbacked the change for consistent reason, which means it read the data with SECONDARY='A'. With the filter (SECONDARY='B'), the transaction in session 1 did not hit any data block, and correspondingly, it did not update any data rows.

    In the case 2, similar things happened in transaction of session 1 before it consistent get the data block. And after it rollbacked the undo changes in log, it got a data block fulfill with the filter condition (SECONDARY='A') in consistent mode. Then, it read the data block with the block id in current mode. However, becasue the current data block had been updated by tansaction in session 2, it failed to read the block due to the filter condition.

    I hope these 2 cases can help readers to understand the difference between DB gets in Current Mode and Consistent Gets.

--- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat