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

"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. 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.

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 - 11:37:54 CST