Description of the read and consistent read in the Current Mode

In the previous article "differences between read in the current mode and consistent read", I used two special examples to describe the differences between read in the current mode and consistent read, it also mentions that "if the corresponding data block changes during the period from when the transaction is started to when the data block is read, unexpected things may happen ". However, such unexpected results may be accepted by us, but they may be unacceptable.

Let's take a look at the following two UPDATE statements:

 
 

  
  
1:    
  
  
update t_test1 set lio=0 where object_id in (101,102);    
  
  
2:    
  
  
update t_test1 set lio=(select lio from t_test1 where object_id = 101) where object_id = 102 and (select count(*) from t_test2 t1, t_test2 t2) > 0;   
 
 

Logically, whether the statement is run, we want the lio of the two records object_id = 101 and object_id = 102) to be the same.

However, since the UPDATE statement will introduce both consistent read and current mode read, and there is a time difference between the two types of read, we may get unexpected results.

Here is an example.

 
 

  
  
13:27:23 HELLODBA.COM>update t_test1 set lio=1 where object_id in (101,102);    
  
  
    
  
  
2 rows updated.    
  
  
    
  
  
13:29:06 HELLODBA.COM>commit;    
  
  
    
  
  
Commit complete.    
  
  
    
  
  
Session 1:    
  
  
13:29:06 HELLODBA.COM>alter system flush buffer_cache;    
  
  
    
  
  
System altered.    
  
  
   
  
  
13:29:11 HELLODBA.COM>-- Transaction 1 begin ---    
  
  
13:29:11 HELLODBA.COM>update t_test1 set lio=(select lio from t_test1 where object_id = 101) where object_id = 102 and (select count(*) from t_test2 t1, t_test2 t2) > 0;    
  
  
    
  
  
1 row updated.    
  
  
    
  
  
13:29:25 HELLODBA.COM>commit;    
  
  
    
  
  
Commit complete.    
  
  
    
  
  
13:29:25 HELLODBA.COM>-- Transaction 1 end ---    
  
  
13:29:25 HELLODBA.COM>select object_id, lio from t_test1 t where object_id in (101,102);    
  
  
    
  
  
OBJECT_ID        LIO    
  
  
---------- ----------    
  
  
       101          0    
  
  
       102          1    
  
  
    
  
  
13:29:25 HELLODBA.COM>    
  
  
    
  
  
Session 2:    
  
  
    
  
  
13:29:11 HELLODBA.COM>-- Transaction 2 begin ---    
  
  
13:29:16 HELLODBA.COM>update t_test1 set lio=0 where object_id in (101,102);    
  
  
    
  
  
2 rows updated.    
  
  
    
  
  
13:29:16 HELLODBA.COM>commit;    
  
  
    
  
  
Commit complete.    
  
  
    
  
  
13:29:16 HELLODBA.COM>-- Transaction 2 end ---   
 
 

In this example, we run the preceding two statements concurrently, but finally we get a result that is different from our logical goal.

The SCN of transaction 1 is earlier than the SCN of transaction 2, so it uses a snapshot data to get the old data from consistent read) to update the latest data read by the current mode ).

I cannot say that this is not a defect of MVCC, but it has at least caused logical confusion.