a question about data consistency

Gurus, I need a clarification. Oracle follows the philosophy of "read should not block write" and "write should not block read". How do I achieve that in Informix. I am having a discussion with a sr. technical architect who has a marked Oracle bias. Can a SELECT be issued without BEGIN WORK so that a consistent set of committed rows is returned. For e.g. a SELECT hits 10000 rows. While it is selecting a row, it gets updated by another process. IMO that query is useless if it returns the updated row. How will informix ensure that this updated row is not returned and it returns only pre-updated row. How do I achieve that , with or without BEGIN AND COMMIT WORK. I know the same can be achived by SELECT FOR UPDATE and using appropriate ISOLATION LEVEL. But if the application is just doing a read, how do we ensure a consistent image of data. Ravi.

I am slightly confused - it sounds like you want the row before it was updated (since you are just reading), but then you go on to indicate that you want the updated row. Sounds like you want the DIRTY READ isolation level. SELECT FOR UPDATE will lock the row in question. cheers j. ----- Original Message ----- From: "rkusenet " <rkusenet@sympatico.ca> To: <ids@iiug.org> Sent: Tuesday, May 13, 2003 2:48 PM Subject: a question about data consistency [1125] > Gurus, > > I need a clarification. Oracle follows the philosophy of "read should not block write" > and "write should not block read". How do I achieve that in Informix. > > I am having a discussion with a sr. technical architect who has a marked Oracle > bias. > > Can a SELECT be issued without BEGIN WORK so that a consistent set of committed > rows is returned. > > For e.g. a SELECT hits 10000 rows. While it is selecting a row, it gets updated > by another process. IMO that query is useless if it returns the updated row. > How will informix ensure that this updated row is not returned > and it returns only pre-updated row. How do I achieve that , with or without BEGIN > AND COMMIT WORK. > > I know the same can be achived by SELECT FOR UPDATE and using appropriate ISOLATION > LEVEL. But if the application is just doing a read, how do we ensure a consistent > image of data. > > Ravi. > > >

If you don't want the data modified while you are reading it, seems to me you should use the "REPEATABLE READ" isolation. Just make sure you have enough locks available - this isolation level places a lock on every row read. Joe V. -----Original Message----- From: Jack Parker [mailto:vze2qjg5@verizon.net] Sent: Tuesday, May 13, 2003 2:43 PM To: ids@iiug.org Subject: Re: a question about data consistency [1126] I am slightly confused - it sounds like you want the row before it was updated (since you are just reading), but then you go on to indicate that you want the updated row. Sounds like you want the DIRTY READ isolation level. SELECT FOR UPDATE will lock the row in question. cheers j. ----- Original Message ----- From: "rkusenet " <rkusenet@sympatico.ca> To: <ids@iiug.org> Sent: Tuesday, May 13, 2003 2:48 PM Subject: a question about data consistency [1125] > Gurus, > > I need a clarification. Oracle follows the philosophy of "read should not block write" > and "write should not block read". How do I achieve that in Informix. > > I am having a discussion with a sr. technical architect who has a marked Oracle > bias. > > Can a SELECT be issued without BEGIN WORK so that a consistent set of committed > rows is returned. > > For e.g. a SELECT hits 10000 rows. While it is selecting a row, it gets updated > by another process. IMO that query is useless if it returns the updated row. > How will informix ensure that this updated row is not returned > and it returns only pre-updated row. How do I achieve that , with or without BEGIN > AND COMMIT WORK. > > I know the same can be achived by SELECT FOR UPDATE and using appropriate ISOLATION > LEVEL. But if the application is just doing a read, how do we ensure a consistent > image of data. > > Ravi. > > >

Hmm, "a write should not block read". Your question is left to a little interpretation, but.. Maybe you need to set wait mode to wait ??. The question I have about not blocking a read would be, which row do you show if a row is in mid-transaction? If a row is inserted or updated or deleted and you want it read without worrying about getting a lock error, use "set isolation to dirty read". If you want the session to wait for the row to be committed, use "set lock mode to wait ??;". ?? being the amount of time in seconds for the session to wait on a lock. Usually a couple of seconds is good.. transactions should be kept at the minimum amount of time as possible. I would rather have the option to be blocked or not be blocked in my control. Patrick McDonough Joe Vidrine wrote: >If you don't want the data modified while you are reading it, seems to >me you should use the "REPEATABLE READ" isolation. Just make sure you >have enough locks available - this isolation level places a lock on >every row read. > >Joe V. > >-----Original Message----- >From: Jack Parker [mailto:vze2qjg5@verizon.net] >Sent: Tuesday, May 13, 2003 2:43 PM >To: ids@iiug.org >Subject: Re: a question about data consistency [1126] > >I am slightly confused - it sounds like you want the row before it was >updated (since you are just reading), but then you go on to indicate >that >you want the updated row. > >Sounds like you want the DIRTY READ isolation level. SELECT FOR UPDATE >will >lock the row in question. > >cheers >j. > >----- Original Message ----- >From: "rkusenet " <rkusenet@sympatico.ca> >To: <ids@iiug.org> >Sent: Tuesday, May 13, 2003 2:48 PM >Subject: a question about data consistency [1125] > > > > >>Gurus, >> >>I need a clarification. Oracle follows the philosophy of "read should >> >> >not >block write" > > >>and "write should not block read". How do I achieve that in Informix. >> >>I am having a discussion with a sr. technical architect who has a >> >> >marked >Oracle > > >>bias. >> >>Can a SELECT be issued without BEGIN WORK so that a consistent set of >> >> >committed > > >>rows is returned. >> >>For e.g. a SELECT hits 10000 rows. While it is selecting a row, it >> >> >gets >updated > > >>by another process. IMO that query is useless if it returns the >> >> >updated >row. > > >>How will informix ensure that this updated row is not returned >>and it returns only pre-updated row. How do I achieve that , with or >> >> >without BEGIN > > >>AND COMMIT WORK. >> >>I know the same can be achived by SELECT FOR UPDATE and using >> >> >appropriate >ISOLATION > > >>LEVEL. But if the application is just doing a read, how do we ensure a >> >> >consistent > > >>image of data. >> >>Ravi. >> >> >> >> >> > > > > > >

----LNX_Wed_May_14_2003_11:31:49_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.05.13 22:30:38 >Sender: rkusenet <rkusenet@sympatico.ca> > >I need a clarification. Oracle follows the philosophy of "read should no= t >block write" and "write should not block read". How do I achieve that in= Informix. = As far as I know you can achive that (no blocking at all) only with SET ISOLATION TO DIRTY READ . With all implications like phantom reads et=c. = >I am having a discussion with a sr. technical architect who has a marked= >Oracle bias. > >Can a SELECT be issued without BEGIN WORK so that a consistent set of co= mmitted >rows is returned. = I think that is not possible in Informix without locking (and possibly bl= ocking writers or being blocked by writers). Depending on your definition of 'consistent set of committed rows' you ne= ed SET ISOLATION TO COMMITTED READ or even REPEATABLE READ . If 'consistency' means all rows = of a table or even many tables you might need REPEATABLE READ . = Oracle has a 'multi-versioning' model that can't be emulated with Informi= x (without performance loss through locking problems). But keep in mind that the Oracle solution has it's drawbacks to: With Oracle's multi versions your application can read a row (committed a= nd consistent at the beginning of your transaction) that is already changed (and committed) at= the actual time you are reading it (or reading it again)! If your transaction/select spans five m= inutes at the end you could get rows with 'outdated' values. I hope I'm correct about that? Unfortunately most software is programmed for the Oracle model and migrat= ion isn't always easy. = > >For e.g. a SELECT hits 10000 rows. While it is selecting a row, it gets = updated >by another process. IMO that query is useless if it returns the updated = row. >How will informix ensure that this updated row is not returned >and it returns only pre-updated row. How do I achieve that , with orwith= out >BEGIN AND COMMIT WORK. = As stated above Informix doesn't have a multi-version concept. But you ha= ve to check your application if it really wants to get the pre-updated row (even if that r= ow has a committed update by the time you are reading it). = > >I know the same can be achived by SELECT FOR UPDATE and using appropriat= e >ISOLATION LEVEL. But if the application is just doing a read, how dowe e= nsure ... = Why not using SELECT FOR UPDATE just for reading? That might be the way t= o build a solution. Check the information about RETAIN UPDATE LOCKS in newer IDS = versions. = As you probably will have to use higher isolation levels (SELECT FOR UPDA= TE implies that, too), you always should try to read/select using indexes (even for small tables= ) to avoid locking problems. And be sure to set your tables to 'lock mode row' . = Regards, Andreas Kutsche ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Wed_May_14_2003_11:31:49_V3.33----