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

"John" <jbradshaw777_at_yahoo.com> wrote in message news:f2f59d82.0311120937.4f75b28_at_posting.google.com...
> "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. This is OK for read consistency purpose.

Hold it right there! No it's not OK for read-consistency purposes. Session 2 must see all data as it was at time T2, because that's when it's report started. It's can't see any data at time T2+0.0001 or later. Only T2 or less.

> It
> then proceeded to read the data block from the table. So, even though
> the rollback data is now gone, it does not matter. Because session 1
> does not need it anymore.

You're starting to dig a deep hole for yourself. I advise you to stop!

1555s happen precisely *because* Session 1 doesn't need the rollback anymore. It's committed, so it can't rollback. But other *readers* (ie, your session 2) *do* still need it -because they started selecting before the commit, and thus mustn't see the committed data. The readers will have to rollback. But since session 1 has just said 'feel free to over-write my rollback because *I* don't need it any more', that's where the risk comes in: session 713 might do a transaction on a completely unrelated table, and place its rollback on top of that previously generated by session 1. If session 2 hasn't done it's read-consistent read yet, it's going to get a bit of a surprise when it subsequently tries to... at which point it gets a 1555.

The hole you're digging for yourself appears to stem from this one slight oversight: your statement that "This is OK for read consistency purposes". It isn't. The determinant of what you can see in the database is not whether it's been committed or not, but whether the SCN of the block you encounter is from a time before (or precisely at) the SCN of the database when you issued your select. You cannot ever see blocks with an SCN from a time after the start of your select.

Regards
HJR
>
> What happens if session 2 at T4 asked for block1, the same block it
> visitied at T2? Again it looks up the data block in the table, noticed
> the data has been committed, SCN is older than its starting SCN and
> decided to read from it. So, session 2 read block1 from rollback at T2
> and from table itslef at T4. There is no problem with this, it there?
>
> If anybody can see problems in my thinking, please do point it out.
> Thanks!
>
>
> John
Received on Wed Nov 12 2003 - 14:27:41 CST