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

jbradshaw777_at_yahoo.com (John) wrote:
> ctcgag_at_hotmail.com wrote in message news:<20031113000943.508> >
> > > 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
>
> Thanks, I think this is where I did not have a good understanding. I
> thought the SCN in generated when the DML is issued and stick with the
> data blocks until the transaction ends.

DML has two (or maybe more) SCNs. When it starts, it has to grab an SCN so that it can roll back other transactions' changes (DML has to read data, too, and those reads need to be consistent.) Then at the end it generates an SCN with which the data it changed gets marked.

Or that's my understanding.

Xho

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