Re: buffer cache and Rollback

<plbiju@xxxxxxxxx> wrote in message
news:1185728429.637940.271770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jul 29, 9:10 am, "Richard Foote" <richard.fo...@xxxxxxxxxxxxxxxxxx>
wrote:
Comments embedded.

<plb...@xxxxxxxxx> wrote in message

news:1185712439.456536.54960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





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).

Note that B1-old doesn't exist as such. The changed data (and just the
changed data plus some overhead details) are actually stored in the
rollback
segment associated with this transaction. The "pointer" from B1 which is
part of the transaction information stored in the header of B1 points to
this rollback block. It's B1 and the rollback data that together may be
used
to create a consistent image of B1 to a prior point of time for read
consistency purposes.

4) Now User2 executes select query for R1, which will be served from
B1-old(No I/O Activity).

Is served by creating a new consistent image of B1 (called B1-old if you
wish) which restores the changed data stored in the rollback block and
makes
B1-old look as it did prior to the update statement.

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.

No. B1 will eventually be written to disk as it eventually ages. The
rollback block will eventually be written to disk if it ages. B1-old will
not be written to disk as it's only a consistent image and will simply be
over-written once it ages.

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

Did my best.

Cheers

Richard- Hide quoted text -

- Show quoted text -

Thanks for the response and correction.
In your statement u have mentioned "The changed data (and just the
changed data plus some overhead details) are actually stored in the
rollback
segment associated with this transaction."

I hope that the old image of the changed block are stored in the
rollback segment .. correct me if I am wrong..


Not the whole old image of the block, just the changes. Why store an old
image of a block if just one column in one row changes, right.

So Any update it require actual disk access for updating Rollback
segment with old data.. right?..

Wrong. If the rollback header block and rollback block to be assigned is
already in memory, then no disk access is required. If the rollback block is
not in memory, then still no disk access is required as Oracle can simply
build the block in memory as any previous data in the rollback block is
going to be discarded anyways. So no, the rollback block is not going to
require disk access unless the rollback segment header is not in memory
(unlikely).

Cheers

Richard


.

Relevant Pages

  • Re: buffer cache and Rollback
    ... retreived from the rollback segmentor from the same ... specific RBS specified in the transaction slot of the updating ... find the corresponding undo (previous state of changed data), ... Is served by creating a new consistent image of B1 (called B1-old if you ...
    (comp.databases.oracle.server)
  • Re: Classic Nest SP with Transaction Question
    ... enclosing transaction or should begin and commit/rollback its own ... > I echo Ron's points and also have been using an approach very similar to> Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the> ChildSP is called from the ParentSP. ... > The ParentSp now has to ROLLBACK everything up to that point. ... As Ron> mentioned, the ParentSP may have called many child SPs, and performed many> updates up to this point, and all of these need to be rolled back. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... This will take care of what is done in the child, but what I want to do is ... rollback the entire outer transaction - the one initiated in the outer SP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)