Very Interested in Common Rollback Issue

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Fri, 16 Jul 1999 12:20:30 -0400
Message-ID: <378F5BCE.B5435B4F@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;

If so - you are causing the 1555 by commiting. We need the rollback segments to
reconstruct 'transaction_table' as it looked at the beginning of the cursor for
loop as we are going through the table. You are modifying the table -- causing
rollback to be generated for it. by committing you are releasing the rollback
you just generated (it can now get overwritten). At some point, we are coming
back to the rollback segment to reconstruct some block of transaction_table and
finding that the rollback we need no longer exists.

You postpone the 1555 by making the RBS larger -- it takes longer for you to
overwrite some data we really need.
" --------- End splice

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?

Received on Fri Jul 16 1999 - 11:20:30 CDT