Something Different Part I of III

I was giving my seminar today and did the read/write consistency section. This is by far my favorite part of Oracle. It is the feature that made me choose Oracle above all other databases back in the early 1990’s. The following is from the forthcoming book – but I find it interesting enough to print here as well. You should also check out http://asktom.oracle.com/~tkyte/wc.html for links to some other “more raw” (not edited) reading on the topic. I think it is important for us all to understand this with regards to using Oracle. The following is quoted from the book

Part I of III; Write Consistency
So far, we’ve looked at read consistency: Oracle’s ability to use undo information to provide non-blocking query and consistent (correct) reads. We understand that as Oracle reads blocks for queries out of the buffer cache, it will ensure that the version of the block is “old” enough to be seen by that query.

But that begs the following question: What about writes/modifications? What happens when you run the following UPDATE statement:

Update t set x = 2 where y = 5;

and while that statement is running, someone updates a row it has yet to read from Y=5 to Y=6 and commits? 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 cannot 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 cannot 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:

ops$tkyte@ORA10GR1> alter session set sql_trace=true;
Session altered.

ops$tkyte@ORA10GR1> select * from t;

X
----------
10001

ops$tkyte@ORA10G> update t t1 set x = x+1;
1 row updated.

ops$tkyte@ORA10G> 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 ....3 ......0 ...1
------- ----- ----- ------- ----
total.......4 ....3 ......0 ...1

update t t1 set x = x+1

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

update t t2 set x = x+1

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

So, during just a normal query, we incur three query (consistent) mode gets. During the first UPDATE, we incur the same three 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:
Table 7-8. Sequence of Updates

T1 Session 1: Update t set y = 10 where y = 5; This updates the one row that matches the criteria.

T2 Session 2: 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 blocked. Session 2 will block and wait for this row.

T3 Session 1: 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 2 began its update.

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 cannot 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, you could observe different results, just because the rows were in different places on the disk.

In this case, Oracle chose 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 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 error for this transaction. 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 . . .” 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.
There are two questions to be addressed here—two questions that interested me, anyway. The first was, Can we observe this? Can we see this actually happen? And the second was, So what? What does this actually mean to us as developers? We’ll address these questions in turn now.

To be continued….