What is the difference between the current mode read and the consistency reading of Oracle

I would like to use the following two examples to make it easier to understand the difference between current pattern reading and consistent reading.

Let me start with the following 2 examples of our discussion.

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>

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

If you observe carefully enough, you can see from the above 2 examples an interesting phenomenon: no matter whether session 1 hits the data, it ultimately does not modify the data. The root cause is the difference between current pattern reading and consistent reading.

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

However, consistency reading reads to a block of data only as a snapshot of a point in time, meaning that such data is read-only. If you want to modify the data, Oracle needs to read the current block of data, which is the current mode read.

During an update, Oracle will first consistently read the data snapshots consistent with the transaction SCN and filter with the Where condition. After that, the data is read to the data block ID, and then read to the corresponding blocks from the current data to modify. However, if the corresponding block of data is changed between the time the transaction starts and the data block is read, then something unexpected might happen to us.

Look back at our first example. In Session 1, we started the update transaction at 10:22:25. However, because there is a large subquery in the transaction, it will not read until a few 10 seconds before the data needs to be modified. In Session 2, we started to update the data at 10:22:37 and submitted the transaction at 10:23:02. This time is earlier than the time that the data was read in Session 1. When the data changes in session 2 are submitted, 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 Undo is read for rollback, that is, it reads the data secondary is ' A ', then passes the condition (secondary= ' B ' After filtering, no data is hit and therefore no data is modified.

In the second example, the transaction of Session 1 also happens before the data block is read in consistency. When it rolls back the data, it is consistent to read the block of data that satisfies the filter condition (secondary= ' A '). At this point, it needs to read the data block through the block ID and into the current data. However, because the contents of the current block have been modified by the transaction in Session 2, it has not been able to modify the data.

I think, through these two examples, readers should be more likely to understand the difference between current pattern reading and consistent reading.