Oracle 一致性读和当前读

 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. 0 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. 0 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.

总结：

两种情况

case1：session1在执行语句后会进行长时间的读，在读到要修改的块之前，session2对块进行了修改，在session1读到需要的块时发现scn比自己的要大，开始从undo中进行一致性读，但是原始数据是A，条件中是B，因此未找到满足条件的数据，导致更新数据为0

case2：session1在执行语句后会进行长时间的读，在读到要修改的块之前，session2对块进行了修改，在session1读到需要的块时发现scn比自己的要大，开始从undo中进行一致性读，此时正常进行一致性读，当要修改数据时，发现块已经被session2修改，因此最终修改的数据也是0