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

"John" <jbradshaw777_at_yahoo.com> wrote in message news:f2f59d82.0311112138.2963b04e_at_posting.google.com...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:<3fb1704b$0$20185
>
> > > 2. It does not matter whether update or select comes first, ORA-01555
> > > can potentially happen, rihgt?
> >
> > Not sure what the question means. 1555s generally happen because an
> > update/insert/delete over-writes the rollback that a select elsewhere is
> > going to need to generate it's read-consistent image of the data. If the
DML
> > happened first (and was committed), and then the select was fired off,
> > there'd be no problem (because the select would be able to read the
modified
> > blocks directly. So the situation only really arises because one select
> > starts, and is still running when DML happens. But it's a bit difficult
to
> > draw tidy timelines like that when you have multi-user simultaneous
access.
> >
> > Regards
> > HJR
>
> Thanks, Howard. A bit sloppy on my side. I was thinking about the case
> of 2 sessions concurrent access to the same table. One session doing
> DML, the other doing query - on the same table. Would you agree that
> it does not matter which session starts first, ORA-01555 can happen to
> the 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
>
> If DML session starts first, query reads from rollback(for the data
> blocks in question). If DML session ends before query session does,
> rollback can be overwritten and ORA-01555 results.
>
> If query session starts first, DML session follows and modifies the
> data that was visited by query session, the data will be copied to
> rollback. If DML session ends before query session, the data on
> rollback can be overwritten. If the query session revisit the data
> blocks again, you get ORA-01555.
>
> I think it is all a matter of the timing between:
> first select from a db block, DML on that block, 2nd visit to that
> block(if 2nd visit is indeed necessary).
>
> I used to think I know this stuff inside out. Now I think I need to
> take a 2nd look and ask for confirmation just to be sure...
>
>
> John
Received on Wed Nov 12 2003 - 01:43:45 CST