Re: buffer cache and Rollback

On Jul 27, 8:32 pm, "Richard Foote" <richard.fo...@xxxxxxxxxxxxxxxxxx>
wrote:
<plb...@xxxxxxxxx> wrote in message

news:1185578894.204112.202140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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


Thanks Richard for your response.
If I understand correctly, the buffer cache works like below:
1) User1 does a select query for row R1 which is in Block B1.
2) Now B1 is in Buffer cache, any further select query for row R1 will
be served from Buffer cache.
3) Now User1 executes update R1.... and not commiting, so In buffer
cache oracle will copy the old image of B1 to B1-old and will have a
pointer in B1 to point to B1-old (No I/O Activity).
4) Now User2 executes select query for R1, which will be served from
B1-old(No I/O Activity).
5) If there are no space in the buffer cache than B1 will be moved to
Datafile and B1-old will be moved to Rollback segment.

Could you please correct me if any of the above statement is wrong..

Thanks

.

Relevant Pages

  • Re: Commit after transacton is completed
    ... Pradeep wrote: ... > Now I run a query to update Col1. ... > If I had run that update query with a COMMIT after every 500 rows, ... >>> transaction completes, otherwise it takes more time and generates more ...
    (comp.databases.oracle.server)
  • Re: mysql select some sort of caching
    ... even if it only contains selects (a rollback would ... lower the transaction isolation level below "repeatable ... ensuring that the same query ... I tried the commit suggestion and it works. ...
    (comp.lang.python)
  • Re: Commit after transacton is completed
    ... > Now I run a query to update Col1. ... > If I had run that update query with a COMMIT after every 500 rows, ... >> Pradeep wrote: ... >>> transaction completes, otherwise it takes more time and generates more ...
    (comp.databases.oracle.server)
  • Re: Commit after transacton is completed
    ... Now I run a query to update Col1. ... If I had run that update query with a COMMIT after every 500 rows, ... I know that commit only after a transaction is the best choice, ... > Pradeep wrote: ...
    (comp.databases.oracle.server)
  • Re: Commit after transacton is completed
    ... Now I run a query to update Col1. ... If I had run that update query with a COMMIT after every 500 rows, ... I know that commit only after a transaction is the best choice, ... > Pradeep wrote: ...
    (comp.databases.oracle.server)