Database Reference
In-Depth Information
call count query current rows
------- ------ ------ ---------- ----------
Parse 1 0 0 0
Execute 1 7 3 1
Fetch 0 0 0 0
------- ------ ------ ---------- ----------
total 2 7 3 1
update t t2 set x = x+1
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
7-8
demonstrates the timeline.
