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

From: John <jbradshaw777_at_yahoo.com>
Date: 12 Nov 2003 21:29:49 -0800
Message-ID: <f2f59d82.0311122129.3802690c@posting.google.com>

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

The hole is already deep, give me a hand here, Howard. :-) That's was a typo, I meant to say session 2 does not need the rollback anymore.

This is where I might have a misunderstanding of the ORACLE transaction processing: when is the SCN created and assigned? ORACLE maintains read consistency by SCN, not by timestamp of course.

My thinking was that if SCN is 'generated' when the DML statement was issued, then if a query comes in later to visit the data blocks, the starting SCN of the query will be NEWER than the SCN of data blocks touched by the DML session. When the DML session subsequently commits, the SCN on the datab block becomes permanent. And since this SCN is generated before the query started, it will be OLDER than the starting SCN of the query, so the query should be able to read from them.

If the SCN is generated at 'the time commit is issued', then this SCN (all the data blocks modified by this DML have the same SCN, right?) will be NEWER than the starting SCN of the query and the query will have to try to read the data from rollback.

So, when is SCN generated and assigned? This is from ORACLE DOC

When a transaction is committed, the following occurs:

The internal transaction table for the associated rollback segment records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table. . .

From this statement, it is not clear when the SCN is 'generated'. God, I am getting a headache. How deep a hole am I in, Howard? It was so clear before - before I started reading the DOC about 'overwritten transaction slot' and 'delayed block clean out'. All of sudden, it is not so clear anymore.

John Received on Wed Nov 12 2003 - 23:29:49 CST