Re: buffer cache and Rollback

<plbiju_at_gmail.com> wrote in message
news:1185578894.204112.202140_at_q75g2000hsh.googlegroups.com...
> Question:
>
> During updating any table, it updates the block in the buffercache and
> not the actual datafile. In a scenerio where the user executes the
> update and don't commit, Does the select query of other users will be
> retreived from the rollback segment(data file) or from the same
> datablock which is cached in the buffer cache.How does it work. During
> any updates does it copy the old image to the rollback
> segment(datafile), if so any update it requires disk access... please
> clarify...
>
> Thanks
>

Basically and simplistically, a query that reads data that has been updated but not committed by another session will read the "dirty" block, determine from the header and locking info that the row is in a dirty state, go to the specific RBS specified in the transaction slot of the updating transaction, find the corresponding undo (previous state of changed data), reconstruct a new read consistent block to the point of time prior to the update occurring, see if the block is now at a point of time prior to the select statement starting (if not repeat the process again and peel off another layer of the onion by reconstructing read consistent images until the block is at a state as at the start of the transaction) and retrieve the corresponding consistent data. This consistency check is always performed during a read by comparing the relevant header info in a block to the point of a query commencing.

A rollback/undo block is just another block, if it's in the buffer cache great read it from there, if not then read it from disk. Note that the first rollback/undo block at least is likely to be in cache as it's from a current transaction but if it's a long transaction either in terms of the number of updated blocks or time it takes to issue the commit, then even this might not be the case.

Hope it makes sense.

Cheers

Richard Received on Fri Jul 27 2007 - 19:32:53 CDT