Re: ORA-01555; does update/select sequence matter?

jbradshaw777_at_yahoo.com (John) wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> >news:<3fb1e4b4$0$13682the session running the query?
> > Put like that, I'd say it made no difference, yes. Once the select has
> > started, it must see all the data as it was at the time it started, and
> > subsequent DML means we rely on rollback segments to do that. If the
> > DML starts first (but presumably hasn't quite committed by the time the
> > select starts) we are still in the business of needing to rollback the
> > uncommitted changes for the select's benefit. So either way, 1555s
> > could happen.
> >
> > The only qualifier is if the DML starts and finishes before the select
> > gets underway. Then the select can see the modified data, and you're in
> > the clear.
> >
> > Regards
> > HJR
>
> Thanks, guys. I am having 2nd thought about the case when the DML
> session starts first. And frankly, it is making me nervous. It now
> seems to me that in this case, ORA-01555 may not happen. Let me try to
> make the picture a little clearer:
>
> 1. Session 1 modifies 100 blocks in a table at time T1. 100 blocks
> copied to rollback segment with SCN marked as, say, SCN1.
> 2. Session 2 comes in at time T2 and select one of the blocks, let's
> say block1. Since the data is being modified by session 1, session 2
> goes to the rollback for the block. Let's say the current SCN is SCN2.
> 3. At time T3 session 1 commits. The rollback is now up for grab.
> Let's say it is overwritten before session 2 finishes the long runnig
> query.
> 4. At time T4 session 2 ask for another data block, say block100.
>
> Here is the critical point. What happens when session 2 ask for
> block100 at T4? It looks up block100 and notice the data in the block
> has been committed and the SCN is SCN1 which is older than SCN2, the
> starting SCN of the query.

No, the SCN of the committed data is the SCN of the commit (T3), not the SCN of when the updating transaction started (T1). You can't commit retroactively. So session 2 does need to rollback the committed data, but can't.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB