Re: Very Interested in Common Rollback Issue

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Mon, 19 Jul 1999 09:15:32 -0400
Message-ID: <379324F4.3AF75174@bigfoot.com>

Thanks again! Just a couple questions. I don't understand the consistent get. Let's say I open a cursor selecting 80,000 rows, but not for update. (I assume if I did it for update, rollback would immediately be generated for all of them),
but not in this case. So, I go through the logic, updating a different table with
information from the cursor. The logic again, is similar to this:

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. It's the DELETE that causes rollback information to be generated for the same table that I'm selecting from actively. 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? 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?

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

Jonathan Lewis wrote:

> Some comments interleaved:-
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Doug Cowles wrote in message <37912228.39A0E805_at_bigfoot.com>...
> > If I start his loop at SCN 5000, get 80,000 rows
> into it (
> >which is what actually happens in my case),
>
> In some ways you don't actually 'get' the row (unless you do a select
> for update in which the actually row entry is marked as locked on the
> block itself - but this lock would clear on the first commit).
>
> > and then my
> cursor loop is looking
> >for a block (tries the buffer cache first I assume), and notices it was
> stamped
> >at 5050, do I really "rollback transaction 5050" - etc.,
>
> 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.
>
> >I mean, it was already comitted wasn't it? Don't I really look in the
> rollback
> >segment for the block I'm looking for less than 5050, find one for 5023,
> try again,
> > and then fail to find one for 5000 and send out the error? (Maybe just a
> >re-intepretation).
>
> The rollback segment does not contain copies of blocks, it contains
> information
> it contains a list of deltas - these are applied in turn to the copied block
> to get back
> to the read-consistent image.
>
> >
> >In any case, if I'm close to understanding you - what do you think of the
> >follwing suggestions to get around this problem -
> >
> >A) Take the commit out of the loop - I think this would work, but may
> result
> >in a lot of rollback extension - your thoughts?
>
> Totally correct - if your process is the only one on the system
>
> >B) Don't do any deletes, just throw the rowid's into a table and after the
> loop
> >is finished, clear out the load table. - This seems like the best
> suggestion to me,
> > what do you think?
>
> You could still get 1555 from the table being updated.
>
> >C) Add some rollback segments - (This was suggested to me ) - I think this
> is
> > unlikely to help, and if it does, it would only be by chance since the
> commits are
> > invalidating the rollback entries, regardless of what segment they're in.
> Adding rollbacks
> >only seems >like a good idea if I get a lot of redo header contention,
> which it would seem
> >to me has >nothing to do with this problem... your thoughts?
>
> Correct, but many people do this and get lucky, so think it works.
>
> The only 'correct' solution is to commit just once at the end. But
> also to select all the rows for update anyway to stop anyone else
> on the system updating a row that you want to update and blowing
> you out of the water anyway.
>
> However, this is often unnecessary, and impractical.
> If you want to run on a short commit cycle, the best bet
> may be to run a PL/SQl look which starts as you have done,
> but after every N rows (say 250) commits and 'remembers'
> the value of the key field it had got to. The loop should the
> re-open the cursor for rows where 'key value > remembered value'.
>
> There are several minor variations on this theme, but they all revolve
> around using a cursor which is small enough to be safe, and throwing
> it away on commit.
Received on Mon Jul 19 1999 - 08:15:32 CDT