Developing Successful Oracle Applications - Expert Oracle Database Architecture - page 33

Table 1-2. Multiversioning in Action

Time Query Account Transfer Transaction

T1 Reads row 1; balance = $500; sum = $500 so far.

T2 Updates row 1; puts an exclusive lock on row 1,

preventing other updates (but not reads).
Row 1 now has $100.

T3 Reads row 2; balance = $250; sum = $750 so far.

T4 Reads row 3 balance = $400; sum = $1150 so far.

T5 Updates row 4; puts an exclusive lock on row 4,

preventing other updates (but not reads).
Row 4 now has $500.

T6 Reads row 4; discovers that row 4 has been modified.

It will actually roll back the block to make it appear as
it did at time = T1. The query will read the value $100
from this block.

T7 Commits transaction.

T8 Presents $1250 as the answer.

At time T6, Oracle is effectively “reading through” the lock that our transaction placed on row 4. This is how
nonblocking reads are implemented: Oracle only looks to see if the data changed; it doesn't care if the data is currently
locked (which implies that the data may have changed). Oracle simply retrieves the old value from the undo segment
and proceeds to the next block of data.
This is another clear demonstration of multiversioning. Multiple versions of the same piece of information, all at
different points in time, are available in the database. Oracle is able to use these snapshots of data at different points
in time to provide us with read-consistent queries and nonblocking reads.
This read-consistent view of data is always performed at the SQL statement level. The results of any single SQL
statement are consistent with respect to the point in time they began. This quality is what makes a statement like the
following insert a predictable set of data:

for x in (select * from t)
loop
insert into t values (x.username, x.user_id, x.created);
end loop;

The result of the SELECT * FROM T is preordained when the query begins execution. The SELECT will not see
any of the new data generated by the INSERT . Imagine if it did—this statement might be a never-ending loop. If, as
the INSERT generated more rows in T , the SELECT could “see” those newly inserted rows, the preceding code would
create some unknown number of rows. If the table T started out with 10 rows, we might end up with 20, 21, 23, or an
infinite number of rows in T when we finished. It would be totally unpredictable. This consistent read is provided to all
statements so that an INSERT such as the following is predictable as well:

insert into t select * from t;