Re: Another angle on this....

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 3 Mar 2002 22:30:06 -0600
Message-ID: <uadtohjo1.fsf@rcn.com>

On 3 Mar 2002, ganesh_at_gtfs-gulf.com wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in message

>> Can you point me to where this was written about?  I was under the
>> impression that once data was committed it was able to be seen by
>> all.  I'd like to be aware of when this isn't the case.

>
> Take the Case of a Select Stmt that Started at Time A. And an Update
> Stmt that Started A+1. The Update Stmt Updated the Records and
> commited the same at A+2 Butthe select is still Running and once the
> select see the ITL on the Block it goes to The Rollback to get the
> Consitent View as of Time A and will not return u the commited record.
>
> This is also given as an example for Ora-01555 Errors that arise out
> of select stmts.

After thinking about your response for a bit, it made total sense. Oracle is just making sure that you have the consistent state based on the time your query started. For it to figure out that the commit happened before the actual row was read by the select seems like it could get hairy but this behaviour seemingly would give you the most current data possible.

Plus, you can figure out when a query began and then you could figure out when an update happened, but you couldn't figure out when Oracle actually made it to the row in question during the query. Hm... well someone much more skilled than I probably could based on a raw trace file and looking at the non-current reads of data blocks but I don't think you actually see the reading of blocks broken down by time anywhere in any trace file no matter what event you might have enabled. (This would be one monster trace file if you did) Therefore, the reading process result's could get confusing and unreliable if you tried to go back and figure something out about the retrieved data.

The reading of that committed row after your select started could also violate some Codd principle, and Ellison (Oops I mean God) knows we wouldn't want to do that. :-)

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Sun Mar 03 2002 - 22:30:06 CST