RE: query,updates,iso. levels-Oracle

To perform that sort of thing, the data has to be cached somewhere. So if you want to simulate it, set your isolation level to repeatable read, dump the data that you desire into a temp table, then do all of your work off of the temp table. Perhaps that's not as efficient as caching only the pages which are pulled for update, but it will get you there. Anyone else have a better solution (is there something I don't know - well about this at any rate)? cheers j. > -----Original Message----- > From: sathish_sadagopan@my-deja.com > [mailto:sathish_sadagopan@my-deja.com] > Sent: Thursday, February 01, 2001 2:58 PM > To: informix-list@iiug.org > Subject: Re: query,updates,iso. levels-Oracle > > > Well actually , I am looking to simulate the way oracle returns data > and the equivalent of > Oracle's 'SET TRANSACTION READ ONLY', where the data returned by a > query is what existed when the query began,even if there were updates > to its data set while the query was executing. > > In Informix if I were to set Query 1 (the big select query) at an > Isolation of 'Repeatable Read', then the second query will not be able > to update (if both queries are looking at the same data), or > the second > query would have updated rows before the first gets to that > part of the > data.So,..., how would I retrieve data as it existed when the query > began and still allow other subsequent queries do updates against the > same table. > Thanks, > Sathish S > > > >From: "Parker, Jack" <JParker@Engage.com> > >To: sathish_sadagopan@my-deja.com > >Subject: RE: query,updates,iso. levels > >Date: Thu, 1 Feb 2001 12:35:58 -0500 > > > >It depends. If you are using an isolation level which > insists that it > >be > >able to return the same values from the table (Repeatable Read), then > >each > >row you read will be locked (unless you have a table or page lock > level >set) > >and your second query will not be allowed to change the data. > >Otherwise, > >the data will be read as it stands. If a row was read before the > >second > >query updated it, then you would get the old value, if after the > >update, > >then you would get the new value. > > > >cheers > >j. > > In article <95br6b$k1f$1@nnrp1.deja.com>, > sathish_sadagopan@my-deja.com wrote: > > Hi, > > Suppose I have large query which takes a long time to run since it > > reads a huge data set. > > If there is a second small,short query which begins after the first > > query began and if it updates a portion of the first queries data > set, > > > > Will the first query use old un-updated values or updated values ? > > would the isolation levels matter in this case ? > > Thanks, > > Sathish > > > > Sent via Deja.com > > http://www.deja.com/ > > > > > Sent via Deja.com > http://www.deja.com/ >

>> -----Original Message----- >> From: sathish_sadagopan@my-deja.com >> >> Well actually , I am looking to simulate the way oracle returns data >> and the equivalent of >> Oracle's 'SET TRANSACTION READ ONLY', where the data returned by a >> query is what existed when the query began,even if there were updates >> to its data set while the query was executing. Parker, Jack wrote in message <95eme2$4n7$1@news.xmission.com>... >To perform that sort of thing, the data has to be cached somewhere. So if >you want to simulate it, set your isolation level to repeatable read, dump >the data that you desire into a temp table, then do all of your work off of >the temp table. Perhaps that's not as efficient as caching only the pages >which are pulled for update, but it will get you there. > >Anyone else have a better solution (is there something I don't know - well >about this at any rate)? > Emm, a change of approach? Personally I like, nay love, nay wish to have babies with DIRTY READ MODE and all that steamy slippery stuff. You just need to philosophise, day dream and wave your hands around in animated discussion with your cow-orker friends until it all starts to make sense. Ask the difficult questions and find answers to them. Real non-gender-specific programmers don't use ANSI databases. Oracle's rollback segments look seductive and certainly sound like they present a clean supply of unadulterated records, but that must be about as exciting as no-alcohol beer... I've heard said that it comes at a performance cost. Some people I know who understand both engines sneer at the rollback segments due to the cost of them. But seriously now, I can't think of a clean way of doing that in Informix. Your suggestion of emulating it would mean that individual sets wouldn't be shared, unlike the Oracle thingies which no doubt are shared? Just thinking for 2 minutes about the required housekeeping in the Oracle engine has given me a headache. SCROLL CURSORS may be one easy way of getting an implicit "safe" set of rows. You would most likely have to FETCH LAST for most queries to "prime" the implicit temp table with a copy of everything, except for the more complex queries that are completed internally before any rows can be presented. But if you are worried about looking at your data as if you are the only user or as if the queries in a transaction all happen in the same instant (the effect of the Oracle rollback segments) how would you coordinate your sets, since you have to execute them sequentially over time? I really suggest that Sathish forget about emulating anything, studies Informix more, and learns the techniques and tricks. Nothing comes for free. The Oracle facilities cost machine resources, and I think emulating them would cost far more in Informix. Perhaps you can put in a feature request to Informix (he says with a devilish grin)? Here's a few keywords leading to useful studies in Informix programming: * DIRTY READ * pessimistic locking * promotable locks * update cursors * UPDATE ... WHERE CURRENT OF ... * establish "ownership rules" of relationships so you can claim a set of rows by locking one. * hmm - probably other things - too early in the morning to know that this list is complete;-) Not many programs in a complete system actually require the full horror of cursor stability or repeatable read, and I can't see the point of imposing such a severe regime on the entire application when you only need it on a small portion of an application suite.