Current Mode and Consistent Gets (2)

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2011-05-03 06:33:09

Share to 

In previous article, I described the difference between DB Gets in Current Mode and Consistent Gets with 2 special cases, and also mentioned that 'if the data blocks were changed during the period time after TX started and before data block read, we will get unexpected result'. Regarding to those 'unexpected results', some of them may be acceptable while others may be unacceptable.     We look into below 2 single UPDATE statements first. SQL代码 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;       From aspect of logic, we want that "lio" of row with object_id=101 and row with object_id=102 should be same no matter we ran which of these 2 UPDATE SQLs.     However, because that the UPDATE statement will invovle both Consistent Gets and Current Mode Gets, and considering time gap between Consistent Gets and Current Mode Gets, we may get an unacceptable result.     We show a trapped case here. SQL代码 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 case, we ran those 2 SQLs concurrently, but evnetually got the result deviated logical purpose.     The TX start SCN of transaction 1 is earlier than TX SCN of transaction. So, it updated current data (newest version, read in current mode) with sanpshot data (a old version, read in consistent mode).     I'm not sure if it's a defect of MVCC. It will really cause logical mess. --- Fuyuncat ---