Re: Very Interested in Common Rollback Issue

I'm beginning to lose track, it's a problem of a conversation with a 24 hour turn-around: so apologies if I start to repeat myself or (worse) contradict myself.

Comments interleaved:

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Doug Cowles wrote in message <379324F4.3AF75174_at_bigfoot.com>...
>
>for x in ( select * from transaction_table where ... order by .... )
> loop
> update some_other_table;
> delete from transaction_table where key = x.key;
> commit;
> end loop;
>
>- When I update some_other_table, some rollback is generated and then
canceled
>on the commit.

Rollback is never 'cancelled'. It may eventually be overwritten because it is no longer being protected by a TX lock. The TX lock is released on COMMIT.
> It's the DELETE that causes rollback information to
be generated
>for the same
>table that I'm selecting from actively.

    Correct

> So, question #1 - you said in my solution that if
>I dumped the rowid's into a table for deleting outside the loop, I could
still
>get a 1555 >from the table being updated (some_other_table).. is that
right?

    I was wrong when I said that - mixing and matching circumstances too much.

    The only way this could happen is if you did your long running update with a

    a single commit, and someone else was changing the 'other table' at the same time.

    If you did lots of commits in the middle of the loop, the 'other table would simply

    see a number of little changes in separate transactions that had nothing to do

    with each other. 1555 would not happen because of the 'other table'.

    Sorry to add to the confusion.

>

Is this
>because after we already generated rollback for some_other_table, we may
need to
>access it again? This is what I don't understand, you said that -
>
>"The session makes of copy of the current block, and tries to rollback back
>the changes in that copy - this is what 'consistent gets' are - Oracle
>trying to produce a copy of a block that was consistent with a given point
in
>time."
>
>How can you do this? Are these changes in the copy sitting as rollback
info
>right
>now? (Have they been committed?) So you're trying to create a
read-consistent
>image of the block for how it looked in the past? Where do you put this
block,
>in the
>buffer cache?
>

    Consistent read copies go in the buffer cache. Try this as SYS and see     what you get: (Version 8.0.4/5 version of x$bh)

        select dbarfil, dbablk,count(*) from x$bh
        group by dbarfil, dbablk
        having count(*) > 1

    You will probably find that there are a few blocks with multiple     copies in the db_block_buffer. One will be the current copy, the     others will be consistent read (i.e. copies with rollback reversed     into them).

    When a session no longer needs a read-consistent copy of a block     it will be flushed out of the buffer by the normal LRU algorithm. The     important thing, though, is that it is highly transient. Only the single

    CURRENT copy will ever be updated by new, real, changes.

>I'm distinguishing block, from rollback info, since as you pointed out,
they're
>not
>necessarily the same.
>

A sample (abbreviated) sequence of events.

Process A starts a long running query.

Process B makes a change to block 999 of table T and commits, this entails:

    Grab block 999 in current mode.
    Update row in block
    Update entry in the blocks 'ITL'
    Write entries into rollback block
    Write entries into redo log
Commit which entails

    Write entries to redo log, force to disk     Change state of rollback block to committed     Change state of data block to committed (fast commit)     Update SCN information in block

Process A now hits block 999 in its query. Spots that the block is newer than the start SCN of the query, so makes read-consistent image which entails

    Grab free buffer
    Pin block 999 briefly and copy into free buffer

        (NB Does not change the block)
    Hunt for transaction, then rollback, indicated by most recent ITL     Apply relevant rollback, to copy
    Which reverses the row change, and the ITL change, and the block SCN     Check if SCN of copy is now consistent with start of query SCN

Snapshot too old occurs when process A is unable to locate the rollback data required to build the read-consistent copy. Received on Mon Jul 19 1999 - 09:29:04 CDT