Database Reference
In-Depth Information
Table 7-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.
So the record that was
Y=5
when you began the
UPDATE
is no longer
Y=5
. The consistent read component of the
UPDATE
says, “You want to update this record because
Y
was 5 when we began,” but the current version of the block
makes you think, “Oh, no, I can't update this row because
Y
isn't 5 anymore. It would be wrong.”
If we just skipped this record at this point and ignored it, then we would have a nondeterministic update. It would
be throwing data consistency and integrity out the window. The outcome of the update (how many and which rows
were modified) would depend on the order in which rows got hit in the table and what other activity just happened
to be going on. You could take the same exact set of rows and in two different databases, each one running the
transactions in exactly the same mix, and you could observe different results, just because the rows were in different
places on the disk.
In this case, Oracle will choose to
restart
the update. When the row that was
Y=5
when you started is found
to contain the value
Y=10
, Oracle will silently roll back your update (just the update, not any other part of the
transaction) and restart it, assuming you are using
READ COMMITTED
isolation. If you are using
SERIALIZABLE
isolation,
then at this point you would receive an
ORA-08177: can't serialize access for this transaction
error. In
READ
COMMITTED
mode, after the transaction rolls back your update, the database will restart the update (i.e., change the
point in time at which the update is “as of ”), and instead of updating the data again, it will go into
SELECT FOR UPDATE
mode and attempt to lock all of the rows
WHERE Y=5
for your session. Once it does this, it will run the
UPDATE
against
that locked set of data, thus ensuring this time that it can complete without restarting.
But to continue on with the “but what happens if. . .” train of thought, what happens if, after restarting the update
and going into
SELECT FOR UPDATE
mode (which has the same read-consistent and read-current block gets going on
as an update does), a row that was
Y=5
when you started the
SELECT FOR UPDATE
is found to be
Y=11
when you go to
get the current version of it? That
SELECT FOR UDPDATE
will restart and the cycle begins again.
This raises several interesting questions. Can we observe this? Can we see this actually happen? And if so, so
what? What does this mean to us as developers? We'll address these questions in turn now.
Seeing a Restart
It is easier to see a restart than you might, at first, think. We'll be able to observe one, in fact, using a simple one-row
table. This is the table we'll use to test with:
EODA@ORA12CR1> create table t ( x int, y int );
Table created.
EODA@ORA12CR1> insert into t values ( 1, 1 );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
