Introduction to Oracle's current pattern reading and consistency reading

In the previous article, it was mentioned that "if the corresponding block of data is changed between the time the transaction starts and the data block is read, then there may be something unexpected happening to us". Such unexpected results may be acceptable to us, but may also be difficult to accept.

Let's look at the following 2 Update statements first:

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 =?) where object_id = 102 and (select COUNT (*) from t_t Est2 T1, t_test2 T2) > 0;

Logically, we want the Lio of both records (OBJECT_ID=101 and object_id=102) to be the same, regardless of the statement being run.

However, because the UPDATE statement introduces both consistent and current mode reads, and because of the time difference between these two readings, we may get a result that we don't want to appear.

Here we show 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 =) 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 execute the above two statements concurrently, but we end up with a result that is at odds with our logical goal.

The SCN of transaction 1 is earlier than the SCN of transaction 2, so it uses a snapshot data (the old data read by consistency) to update the current data (the most recent data read by the current schema).

I can't say it's not a MVCC flaw, but it has at least created a logical mess.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/