Concurrency and Multiversioning - Oracle Database Transactions and Locking Revealed - page 70

call count query current rows
------- ------ ------ ---------- ----------
Parse 1 0 0 0
Execute 1 7 1 1
Fetch 0 0 0 0
------- ------ ------ ---------- ----------
total 2 7 1 1

So, during just a normal query, we incur seven query (consistent) mode gets . During the first UPDATE , we incur
the same seven I/Os (the search component of the update involves finding all of the rows that are in the table when
the update began, in this case) and three current mode gets as well. The current mode gets are performed in order
to retrieve the table block as it exists right now, the one with the row on it, to get an undo segment block to begin our
transaction, and an undo block . The second update has exactly one current mode get; since we did not have to do the
undo work again, we had only the one current get on the block with the row we want to update. The very presence of
the current mode gets tells us that a modification of some sort took place. Before Oracle will modify a block with new
information, it must get the most current copy of it.
So, how does read consistency affect a modification? Well, imagine you were executing the following UPDATE
statement against some database table:

Update t Set x = x+1 Where y = 5;

We understand that the WHERE Y=5 component, the read-consistent phase of the query, will be processed using
a consistent read (query mode gets in the TKPROF report). The set of WHERE Y=5 records that was committed in the
table at the beginning of the statement's execution are the records it will see (assuming READ COMMITTED isolation;
if the isolation is SERIALIZABLE , it would be the set of WHERE Y=5 records that existed when the transaction began).
This means if that UPDATE statement were to take five minutes to process from start to finish, and someone added
and committed a new record to the table with a value of 5 in the Y column, then that UPDATE would not see it because
the consistent read would not see it. This is expected and normal. But, the question is, what happens if two sessions
execute the following statements in order:

Update t Set y = 10 Where y = 5;
Update t Set x = x+1 Where y = 5;

Table 4-8 demonstrates the timeline.

Table 4-8. Sequence of Updates

Time Session 1 Session 2 Comment

T1 Update t

Set y=10 Where y=5;

This updates the one row that matches the criteria.

T2 Update t Set x=x+1

Where y=5;

Using consistent reads, this will find the record session 1
modified, but it won't be able to update it since session 1
has it locked. Session 2 will block and wait for this row.

T3 Commit; This releases session 2; session 2 becomes unblocked.

It can finally do the current read on the block containing
this row, where Y was equal to 5 when session 1 began its
update. The current read will show that Y is now
equal to 10, not 5 anymore.