Re: query,updates,iso. levels-Oracle

Basically you can't do this - Oracle uses roll back segments to provide you with the time consistent data, and doesn't take out read locks so the subsequent updates aren't blocked - these two capabilities are pretty unique to Oracle at least amongst Unix and NT databases (DB2 OS/390 and Ingres do do something similar). Note that this is NOT a function of SET TRANSACTION READ ONLY in Oracle - you get this capability as the default (it's called multi version read consistency) > From: sathish_sadagopan@my-deja.com > Organization: Deja.com > Newsgroups: comp.databases.informix > Date: Thu, 01 Feb 2001 19:58:27 GMT > 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/