So far, we’ve looked at read consistency: Oracle’s ability to use undo information to provide nonblocking 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 questions: What about writes/modifications? What happens when you run an UPDATE statement, as follows, and while that statement is running, someone updates a row it has yet to read from Y=5 to Y=6 and commits?
Update t Set x = 2 Where y = 5;
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:
SQL> exec dbms_monitor.session_trace_enable PL/SQL procedure successfully completed.
Next, we navigate to the trace file directory, locate the trace file we just generated, and process it with the TKPROF utility:
$ cd /opt/oracle/diag/rdbms/cdb/CDB/trace
$ tkprof CDB_ora_9000.trc sys=no output=myfile.txt
After we run TKPROF and examine the results (in myfile.txt), we’ll see something like this (note that I removed the ELAPSED, CPU, and DISK columns from this report):
select * from tcall count query current rows
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;
