Differences between read in current mode and consistent read

Let me start with the following two examples.

 
 

  
  
Case1:    
  
  
HELLODBA.COM>set time on    
  
  
10:22:09 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;    
  
  
1 row updated.    
  
  
10:22:22 HELLODBA.COM>commit;    
  
  
Commit complete.    
  
  
Session 1:    
  
  
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;    
  
  
0 rows updated.    
  
  
10:23:15 HELLODBA.COM>    
  
  
Session 2:    
  
  
10:22:37 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;    
  
  
1 row updated.    
  
  
10:23:02 HELLODBA.COM>commit;    
  
  
Commit complete.    
  
  
10:23:04 HELLODBA.COM>    
  
  
Case2:    
  
  
10:25:38 HELLODBA.COM>update t_test1 set SECONDARY='A' where object_id = -1;    
  
  
1 row updated.    
  
  
10:25:48 HELLODBA.COM>commit;    
  
  
Commit complete.    
  
  
Session 1:    
  
  
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;    
  
  
0 rows updated.    
  
  
10:27:21 HELLODBA.COM>    
  
  
Session 2:    
  
  
10:26:16 HELLODBA.COM>update t_test1 set SECONDARY='B' where object_id = -1;    
  
  
1 row updated.    
  
  
10:26:41 HELLODBA.COM>commit;    
  
  
Commit complete.    
  
  
10:26:42 HELLODBA.COM>   
 
 

If you observe it carefully enough, you can see an interesting phenomenon from the above two examples: No matter whether session 1 HITS data or not, it does not modify the data. The root cause is the difference between the current read mode and consistent read mode.

We know that in order to reduce concurrency conflicts, Oracle introduced MVCC multi-version concurrency control, also called MCC) method. In this mechanism, concurrent transactions do not block each other because of consistency, unless they want to modify the same record. They will roll back all the logs whose SCN is greater than their own transaction SCN to ensure that the data block read by this transaction is consistent with the transaction SCN. In Oracle, such read behavior is called consistent read.

However, the data block read by consistent reads is only a snapshot at a certain point in time, that is, such data is read-only. To modify the data, oracle needs to read the current data block, that is, read in the current mode.

During an UPDATE process, oracle reads data snapshots consistent with the transaction SCN and filters data snapshots using the where condition. Then, based on the ID of the data block to be read, and then read from the current data to the corresponding data block for modification. However, if the corresponding data block changes during the period from when the transaction is started to when the data block is read, unexpected events may occur.

Let's look back at our first example. In session 1, we started the update transaction at 10:22:25. However, because a large subquery exists in the transaction, it will read the data to be modified after dozens of seconds. In Session 2, we started to update the data at 10:22:37 and committed the transaction at 10:23:02. The time is earlier than the time when the data is read in session 1. When the data change in session 2 is committed, the transaction in session 1 reads the data block. Because the transaction SCN in session 2 is greater than the transaction SCN in session 1, the data in the UNDO will be read for rollback. That is to say, it reads the data SECONDARY as 'A ', after filtering by the condition (SECONDARY = 'B'), no data is hit and no data is modified.

In the second example, a transaction in session 1 encountered a similar problem before it consistently reads data blocks. When it rolls back data, it consistently reads data blocks that meet the filtering conditions (SECONDARY = 'A. In this case, it needs to use the data block ID to read the data block from the current data. However, because the content of the current data block has been modified by the transaction in session 2, it still cannot be modified to the data.

I think, through these two examples, the reader should better understand the difference between the current read mode and consistent read.