Database Reference
In-Depth Information
That is, when your
UPDATE
began, some row had the value
Y=5
. As your
UPDATE
reads the table using consistent
reads, it sees that the row was
Y=5
when the
UPDATE
began. But, the current value for
Y
is now 6 (it's not 5 anymore)
and before updating the value of
X
, Oracle will check to see that
Y
is still 5. Now what happens? How are the updates
affected by this?
Obviously, we can't modify an old version of a block; when we go to modify a row, we must modify the current
version of that block. Additionally, Oracle can't just simply skip this row, as that would be an inconsistent read and
unpredictable. What we'll discover is that in such cases, Oracle will restart the write modification from scratch.
Consistent Reads and Current Reads
Oracle does do two types of block gets when processing a modification statement. It performs
•
Consistent reads
: When “finding” the rows to modify
•
Current reads
: When getting the block to actually update the row of interest
We can see this easily using
TKPROF
. Consider this small one row example, which reads and updates the single
row in table
T
from earlier:
EODA@ORA12CR1> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
X
----------
10001
EODA@ORA12CR1> update t t1 set x = x+1;
1 row updated.
EODA@ORA12CR1> update t t2 set x = x+1;
1 row updated.
When we run
TKPROF
and view the results, we'll see something like this (note that I removed the
ELAPSED
,
CPU
,
and
DISK
columns from this report):
select * from t
call count query current rows
------- ------ ------ ---------- ----------
Parse 1 0 0 0
Execute 1 0 0 0
Fetch 2 7 0 1
------- ------ ------ ---------- ----------
total 4 7 0 1
update t t1 set x = x+1
