Re: Locking Question (nowait, UPDATE etc)

I have a situation very similar to the original poster: Large query constantly exporting data from the table while the data is updated by multiple other sessions. The difference is that the exporting query doesn't need to lock the records it is exporting. When the SELECT cursor, doing the export, gets to the end of records it closes and reopens again, so if it missed some of the records that were updated, it will get them on the next SELECT - the fact that some records it selects will be out of date is not a concern here.

What worries me here is that the query for the exporting cursor selects large number of records and stays open for some time, which is causing the "ORA-01555: snapshot too old" error.

This application previously worked in Informix and we used SET ISOLATION TO DIRTY READ, which was ideal for this particular case, but Oracle doesn't seem to have "dirty read" and default isolation level is fraught with "snapshot too old" error in this case.

Please advise about the possible solution to this. Application redesign is the obvious way, but is undesireble in our case - we a supposed to "quickly migrate" from Informix to Oracle and this problem is the biggest hurdle so far. Is there a way to emulate "DIRTY READ" in Oracle?

Any help will be greatly appreciated.

JG.

"Johannes Eggers" <jeggers_at_tetrix.com> wrote in message news:<b69kk1$31lh8$1_at_ID-178358.news.dfncis.de>...
> <ctcgag_at_hotmail.com> wrote in message
> news:20030328145448.490$ZP_at_newsreader.com...
> > "Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> > > <ctcgag_at_hotmail.com> wrote in message
> > > news:20030327145537.582$gY_at_newsreader.com...
> > > > "Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> > > > > I hope this question makes sense:
> > > > >
> > > > > We have some code (in stored procedures) that selects data FOR
> UPDATE
> > > > > NOWAIT, and it behaves as excepted.
> > > >
> > > > How is it expected to behave, and why does it do this?
> > >
> > > It's supposed to lock the records. We do it because we want the records
> > > locked (so they're not modified while we process them in our client)
> >
> > Why do want them locked so they can't be modified while you process them
> > in your client?
>
> Correct.
>
> >
> > Presumably, the updates happening in your table are occuring because
> > real-world facts (which the table models) have changed, and you need to
> > reflect that. If your client wants to ignore any changes occuring
> > after a certain point, that's fine. It is what Oracle's read consistency
> > does. But if your client wants to forbid anyone else from acknowledging
> > that the real world has changed, it sounds like your client is an ignorant
> > bully.
>
> Not really. It's acceptable, from a business process, to not permit updates
> while the other process is running.
>
> > > > Then do the select for update no wait on this, first.
> > >
> > > Tried this, it seems the lock doesn't survive the stored procedure that
> > > is putting the lock on it, amazingly enough. We can work-around that by
> > > doing a simple UPDATE, then select the records.
> >
> > I did:
> >
> > create procedure llll (x in number)
> > cursor main is select * from blah where col2=x for update;
> > begin
> > open main;
> > end;
> >
> > then did an exec llll(30)
> > In a separate session, I said "update blah set col1='hi' where col2=30"
> and
> > it blocked until I typed commit in the first session.
>
> I watched our DB guy do the same thing, and we didn't get your result
> (although we expected to).
Received on Wed Apr 02 2003 - 12:27:05 CST