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

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

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 Tue Nov 11 2003 - 23:38:33 CST