Re: Very Interested in Common Rollback Issue

The key point is the error in your comment:

    >If this is the only transaction on the box then WHY do we need to     >reconstruct the

Each commit terminates a transaction. There are lots of transactions going on on the box. (Really the cursor driving the loop should become invalid the first time you commit, and then the 1555 couldn't happen because the loop would break, of course).

The actual failure of read-consistency in this circumstance is caused in part by the ORDER BY in the driving loop, in part by the probable existence of indexes and finally by the fact that it is the BLOCK that needs to become read consistent, it has nothing to do with the specific row.

Consider:

    I am at step 1000 of the loop
    The loop started at system commit number 5000

    I got to block X which says the last commit that changed the block was 5050

    The row that I want to use APPEARS to be there, but how do I know it is     the same row that it was when I started the loop ?

    So I rollback transaction 5050 (which entails rolling back one deleted row, and

    updating the ITL entry in the block, and getting the entire block stepped backwards

    by one transaction).

    The new (older) version of the block now says that it was last changed by a

    transaction with a system commit number of 5023. The row I want is still there,

    and it still APPEARS to be the row IO expect but still it is possible that a transaction

    before 5023 changed it (or one deleted it and another put it back) - so I roll back again

    etc.

    until I roll the block back to an SCN prior to 5000, or I find that I can't find the

    rollback that I need and raise 1555.

--

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

Doug Cowles wrote in message <378F5BCE.B5435B4F_at_bigfoot.com>...
>A recent posting, from Nick Rumble (if he wants credit), which also
>reflects something happening on my account, indicated the following.
>If you are engaged in the following logic and are encountering a
>snapshot too old -
>Direct Splice ---------------
>"
> 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;
>
>
>
>I'm willing to accept this as the truth but I'm wondering if I can get
>more detail.
>If this is the only transaction on the box then WHY do we need to
>reconstruct the
>transaction table. If we are selecting one row after another, and after
>we delete
>a row we no longer need, at what point do we need to reconstruct that
>and generate
>a snapshot too old? I could see if someone else needed a read
>consistent view of the table but there is no one else on the box. The
>cursor is only opened once, correct?
>So after it is open, rollback is generated by the update logic, and
>thrown out after
>commits, but what would cause the desire to re-construct something that
>has been
>tossed off as done with?
>
>- Dc.
>
Received on Fri Jul 16 1999 - 11:56:04 CDT