Oracle read consistency issues

To speed up transaction processing, Oracle adopts the read consistency mechanism. When data is not submitted after a session is updated, other sessions read the results before the update. This greatly improves the efficiency of data concurrency, but produces incorrect results under certain conditions.

The example below is described.

Insert 1 row record 100 in the first image table test (A number ()

Session1:

Update test set A = (select A + 1 from test)

Session2:

Update test set A = (select A + 1 from test)

When Session 1 is submitted and then Session 2 is executed, the result is 102.

When Session 1 is not submitted after execution, Session 2 is executed and the result is 101, not 102. This is because in order to provide concurrent data access speed, when data is not submitted after a session is updated, the data before the first session is updated by the select statement of the other session.

When there are multiple concurrent sessions, uncertain results will be generated. This is not what we want to see. Pay more attention to such issues During Concurrent System Development and perform multiple tests.

The above two sessions can be changed to the following practices to ensure accurate data updates

Session1:

Update test set A = A + 1

Session2:

Update test set A = A + 1

In other examples, when the processing result conflicts with the mechanism of the database, you need to try other implementation methods.