Informix isolation levels

First of I have read the manuals. I am have difficulty with and application that have written to work against Informix or Oracle and the developer is saying that the way Informix handles transactions is wrong. In Oracle, user1 begins a transaction and updates a row, but doesn't issue a commit work. user2 issues a select which includes the row being updated by user1 user2 gets all rows including the "before image" of the row user1 what updating. This is not a "dirty read." In Informix, user1 begins a transaction and updates a row, but doesn't issue a commit work. user2 issues a select which includes the row being updated by user1 user2 gets the following error: 244: Could not do a physical-order read to fetch next row. 107: ISAM error: record is locked.I do not want to change user2's isolation level to "Dirty Read." How do I get the same functionality in Informix that Oracle has? If thats not possible how close can I get? Thanks Peter ---------- CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,is for the sole use of the intended recipient(s), even if addressed incorrectly, and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy or delete all copies of the original message and all attachments, including deletion from the trash or equivalent folder. Thank you. ------------=_1063370484-21154-83--

----LNX_Fri_Sep_12_2003_16:15:40_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.12 15:55:58 >Sender: Peter J Dia.... <pdiazdeleon@infinityhealthcare.com> > >First of I have read the manuals. > >I am have difficulty with and application that have written to work >against Informix or Oracle and the developer >is saying that the way Informix handles transactions is wrong. > >In Oracle, user1 begins a transaction and updates a row, but doesn't >issue a commit work. > > user2 issues a select which includes the row being >updated by user1 > user2 gets all rows including the "before image" ofthe >row user1 what updating. This is not a "dirty read." > >In Informix, user1 begins a transaction and updates a row, but doesn't >issue a commit work. > > user2 issues a select which includes the row being >updated by user1 > user2 gets the following error: 244: Could not do a >physical-order read to fetch next row. > >107: ISAM error: record is locked.> I do not want to change user2's isolation level to "Dir= ty Read." > >How do I get the same functionality in Informix that Oracle has? If >thats not possible how close can I get? = You can't get the same functionality in Informix as in Oracle. Oraclehas = a multiversion data model that make things quite easy for programmers (in m= ost cases). For short: writers never block readers AND readers never block wr= iters. = Now to Informix and your problem (and possible solutions): = Does user2 really need the row updated by user1 OR does he need other rows from the same table? In the second case you should create appropriate indexes to avoid a seque= ntial scan for user2 (as the error messages physical-order read indicates). = Is a "Dirty Read" really unacceptable for your application? Often youcan = do dirty reads on one/more tables and later get single (or a few rows) with = a higher isolation level (or with select for update which is never 'dir= ty'). = Try to change the lock mode. Default is 'nowait', try for example 'wait 2= ' - in that the select of user2 waits for up to 2 seconds to get the rows (the transa= ction of user1 has hopefully finished in that time). = 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_Fri_Sep_12_2003_16:15:40_V3.33----

We don't have the same kind of rollback segments which Oracle has .... In order to alleviate your problem, you will have to use the SET LOCK MODE to WAIT <time> ; so that user2 will wait for <time> to test whether the lock is released or not ... HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Peter J Dia...." <pdiazdeleon@infinityheal To: ids@iiug.org thcare.com> cc: Sent by: Subject: Informix isolation levels [1852] forum.subscriber@iiug.org 09/12/2003 05:44 AM First of I have read the manuals. I am have difficulty with and application that have written to work against Informix or Oracle and the developer is saying that the way Informix handles transactions is wrong. In Oracle, user1 begins a transaction and updates a row, but doesn't issue a commit work. user2 issues a select which includes the row being updated by user1 user2 gets all rows including the "before image" of the row user1 what updating. This is not a "dirty read." In Informix, user1 begins a transaction and updates a row, but doesn't issue a commit work. user2 issues a select which includes the row being updated by user1 user2 gets the following error: 244: Could not do a physical-order read to fetch next row. 107: ISAM error: record is locked.I do not want to change user2's isolation level to "Dirty Read." How do I get the same functionality in Informix that Oracle has? If thats not possible how close can I get? Thanks Peter ---------- CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,is for the sole use of the intended recipient(s), even if addressed incorrectly, and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy or delete all copies of the original message and all attachments, including deletion from the trash or equivalent folder. Thank you. ------------=_1063370484-21154-83--

Two things. You need to issue 'SET LOCK MODE TO WAIT <n>' where '<n>' is blank for a number of seconds to wait before timing out waiting for locks to clear. This eliminates erroring out on trivial transitory locks. Second, if user1 does indeed commit the update what happens to user2? Does his update overwrite user1's? Does he then get an error? That's the way it should work. Informix assumes that two users working on the same record is an anomaly, and should not happen normally, hence the hard lock. You can perform the kind of optimistic locking in Informix at the application level and that code will work fine in all other databases as well. Simply put, do NOT BEGIN WORK when the user fetches the row (this prevents problems with users bringing up data then going out to lunch or home for the holidays leaving rows locked), just fetch without locking, present the data and let the user modify the data on screen. When the user pushes the update button THEN you BEGIN WORK, refetch the data FOR UPDATE, into a separate buffer, forcing a lock on the row, and compare the newly fetched row to the original unchanged data presented to the user (you can do this by comparing all non-key columns or using a timestamp column maintained by a DEFAULT clause and an UPDATE trigger and only fetching/comparing just that column). If the data has not been modified by another user since presented then you perform the update and COMMIT WORK. If the comparison shows another user has updated the row since the original data was presented you can do whatever is reasonable to the application, ie: ROLLBACK WORK and notify the user of the update clash, ask the user what to do, update anyway only if columns the other user changed were left alone by this user, present the new version and let the user try his changes again, whatever. This is a much better application design and it does not depend on any database server features of any specific server. All of my interactive apps are designed this way. Art S. Kagel ----- Original Message ----- From: Peter J Dia.... At: 9/12 9:37 > > First of I have read the manuals. > > I am have difficulty with and application that have written to work > against Informix or Oracle and the developer > is saying that the way Informix handles transactions is wrong. > > In Oracle, user1 begins a transaction and updates a row, but doesn't > issue a commit work. > > user2 issues a select which includes the row being > updated by user1 > user2 gets all rows including the "before image" of the > row user1 what updating. This is not a "dirty read." > > In Informix, user1 begins a transaction and updates a row, but doesn't > issue a commit work. > > user2 issues a select which includes the row being > updated by user1 > user2 gets the following error: 244: Could not do a > physical-order read to fetch next row. > > 107: ISAM error: record is locked.> I do not want to change user2's isolation level to > "Dirty Read." > > How do I get the same functionality in Informix that Oracle has? If > thats not possible how close can I get? > > Thanks > Peter > > > > > > > > ---------- > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,is for > the sole use of the intended recipient(s), even if addressed incorrectly, and > may contain confidential and privileged information. Any unauthorized review, > use, disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply e-mail and destroy or delete all > copies of the original message and all attachments, including deletion from the > trash or equivalent folder. Thank you. > > ------------=_1063370484-21154-83--

The developer is wrong. The way Informix handles transactions is right, the way Oracle handles them is wrong. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche From: "Peter J Dia...." <pdiazdeleon@infinityhealthcare.com> > >I am have difficulty with and application that have written to work >against Informix or Oracle and the developer >is saying that the way Informix handles transactions is wrong. _________________________________________________________________ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband

Thanks everyone!!! -Peter ---------- CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,is for the sole use of the intended recipient(s), even if addressed incorrectly, and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy or delete all copies of the original message and all attachments, including deletion from the trash or equivalent folder. Thank you.

----LNX_Fri_Sep_12_2003_21:17:19_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.12 20:53:46 >Sender: Obnoxio The.... <obnoxio@hotmail.com> > >The developer is wrong. The way Informix handles transactions is right, = the >way Oracle handles them is wrong. > >-- >Bye now, >Obnoxio = I don't think that's correct. I would prefer to say Informix and Oracle h= andle isolation levels differently. And the way Oracle handles them makes life = easier for programmers. When it comes to real transACTIONS (updates/deletes) on the same entities= then Informix and Oracle behave similar. Unfortunately code written for Oracle originally often relays on the fact= the readers are never blocked by writers and vice versa. In other words: With= Oracle there exists no 'dirty read', only 'committed read'. But that data= may be old data and already due to update in an open transaction. = 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_Fri_Sep_12_2003_21:17:19_V3.33----

Andreas.KUT.... wrote: > ----LNX_Fri_Sep_12_2003_21:17:19_V3.33-- > Content-Type: text/plain; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > >>Datum: 2003.09.12 20:53:46 >>Sender: Obnoxio The.... <obnoxio@hotmail.com> >> >>The developer is wrong. The way Informix handles transactions is right, = > > the > >>way Oracle handles them is wrong. >> >>-- >>Bye now, >>Obnoxio > > = > > I don't think that's correct. I would prefer to say Informix and Oracle h= > andle > isolation levels differently. And the way Oracle handles them makes life = > easier > for programmers. > When it comes to real transACTIONS (updates/deletes) on the same entities= > > then Informix and Oracle behave similar. > Unfortunately code written for Oracle originally often relays on the fact= > the > readers are never blocked by writers and vice versa. In other words: With= > > Oracle there exists no 'dirty read', only 'committed read'. But that data= > > may be old data and already due to update in an open transaction. So it's a sort of 'dirty committed read' then. It's a record that was once committed, but may have already changed or been deleted. Sounds like a dirty read when you analyse it. Strangely makes OTC sound coherent. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

Really. Oracle are wrong. Trust me on this. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche From: "Andreas.KUTSCHE@spar.at" <andreas.kutsche@spar.at> > > >Datum: 2003.09.12 20:53:46 > > > >The developer is wrong. The way Informix handles transactions is right, >the > >way Oracle handles them is wrong. > >I don't think that's correct. _________________________________________________________________ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger

Just because Informix, DB2, MS SQL Server, Sybase and MySQL all do it the=20 same way, does that make Oracle wrong? fursure! Christine=20 "Obnoxio The...." <obnoxio@hotmail.com> Sent by: forum.subscriber@iiug.org 09/12/2003 05:26 PM =20 To: ids@iiug.org cc:=20 Subject: RE: Re: Informix isolation levels [1863] Really. Oracle are wrong. Trust me on this. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien =E0 dire qu'il faut fermer sa gueule" - Coluche From: "Andreas.KUTSCHE@spar.at" <andreas.kutsche@spar.at> > > >Datum: 2003.09.12 20:53:46 > > > >The developer is wrong. The way Informix handles transactions is right, = >the > >way Oracle handles them is wrong. > >I don't think that's correct. =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F Express yourself with cool emoticons - download MSN Messenger today!=20 http://www.msn.co.uk/messenger

----LNX_Sat_Sep_13_2003_12:00:45_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! = I would like to discuss this a little bit further. As an 'informix man' I= often have problems with applications designed for Oracle originally. And I'm thankfully for = any input to help me in my discussions with the developers. = But I stick to my opinion: I don't think that Oracle is wrong. It's just = a different approach. Oracle delivers a result set as it was committed at the beginning of your= query. Any pending update/delete/insert is just ignored (technically the old committed value= is taken from the rollback segments and not from the data page - that's the reason why Orac= le can provide that feature and Informix not (*)). But that approach is valid for ANSI SQL isolation level COMMITTED READ. C= ommitted read only means that you don't get any uncommitted data - and that's true with the = Oracle approach. And it's the data as it was when you issued your select - in many cases that'sexac= tly what you want. = If you want more you have to use REPEATABLE READ or SERIALIZABLE. Andthen= locking problems will become quite similar in Informix and Oracle. = (*) Maybe Informix could provide such a feature using the logical logcomb= ined with data on disk pages rather than buffer pages, because the information must be avai= lable somewhere to rollback transactions. But I think that would be possible only with a = severe impact on performance. = Regards, Andreas Kutsche = >Datum: 2003.09.13 01:45:25 >Sender: Obnoxio The.... <obnoxio@hotmail.com> >Betreff: RE: Re: Informix isolation levels [1863] > >Really. Oracle are wrong. Trust me on this. > >-- >Bye now, >Obnoxio > >>>Datum: 2003.09.12 20:53:46 >>> >>>The developer is wrong. The way Informix handles transactions is right= , >>>the >>>way Oracle handles them is wrong. = ------------------------------------------------ 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_Sat_Sep_13_2003_12:00:45_V3.33----

----LNX_Sat_Sep_13_2003_14:54:13_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.13 01:45:25 >Sender: Obnoxio The.... <obnoxio@hotmail.com> >Betreff: RE: Re: Informix isolation levels [1863] > >Really. Oracle are wrong. Trust me on this. > >-- >Bye now, >Obnoxio = >>> >>>The developer is wrong. The way Informix handles transactions is right= , >>>the way Oracle handles them is wrong. >>> = >>Andreas Kutsche: >> >>I think that's not correct. It's a different approach. >> = I think I should add that my opionion that Oracle is right (too), only is= valid for COMMITTED READ (the most used isolation level in applications I suppo= se). When it comes to SERIALIZABLE than Oracle is really wrong (or not really serializable under all circumstances) - see http://www.cs.umb.edu/~isotest/snaptest/snaptest.pdf or http://www-dbs.cs.uni-sb.de/papers/tdd99.pdf for examples (keyword: snapshot isolation ). = 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_Sat_Sep_13_2003_14:54:13_V3.33----

If oracle is so perfect (pardon me, I have only DBA'd on it for less than 2 years), why does it error with a "timestamp too old" message? If I am an end user, I want my data... I don't want my transaction to abend because the database is confused... Informix gives me dirty read.. I get my data as of timestamp X... it's all consistent as of timestamp X. If someone changed the data at timestamp X+5 (while my rpt is still running), who cares... .. my report wanted timestamp X, and I got it. Oracle goes poopy on timestamp too old? Pardon my ignorance, I could be totally wrong, but I have seen the 'timestamp too old'. I don't really understand it... and I think any good database should not have that problem. Norma Jean -----Original Message----- From: andreas.kutsche@spar.at [mailto:andreas.kutsche@spar.at] Sent: Saturday, September 13, 2003 7:57 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Antw2: RE: Re: Informix isolation levels [1866] ----LNX_Sat_Sep_13_2003_14:54:13_V3.33-- Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.13 01:45:25 >Sender: Obnoxio The.... <obnoxio@hotmail.com> >Betreff: RE: Re: Informix isolation levels [1863] > >Really. Oracle are wrong. Trust me on this. > >-- >Bye now, >Obnoxio = >>> >>>The developer is wrong. The way Informix handles transactions is right= , >>>the way Oracle handles them is wrong. >>> = >>Andreas Kutsche: >> >>I think that's not correct. It's a different approach. >> = I think I should add that my opionion that Oracle is right (too), only is= valid for COMMITTED READ (the most used isolation level in applications I suppo= se). When it comes to SERIALIZABLE than Oracle is really wrong (or not really serializable under all circumstances) - see http://www.cs.umb.edu/~isotest/snaptest/snaptest.pdf or http://www-dbs.cs.uni-sb.de/papers/tdd99.pdf for examples (keyword: snapshot isolation ). = 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_Sat_Sep_13_2003_14:54:13_V3.33---- --openmail-part-41e19bc9-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Sat, 13 Sep 2003 18:51:42 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 \\par If oracle is so perfect (pardon me, I have only DBA'd on it for less than 2 years), why does it error with a "timestamp too old" message?\\par \\par If I am an end user, I want my data... I don't want my transaction to abend because the database is confused...\\par \\par Informix gives me dirty read.. I get my data as of timestamp X... it's all consistent as of timestamp X. If someone changed the data at timestamp X+5 (while my rpt is still running), who cares... .. my report wanted timestamp X, and I got it. Oracle goes poopy on timestamp too old?\\par \\par Pardon my ignorance, I could be totally wrong, but I have seen the 'timestamp too old'. I don't really understand it... and I think any good database should not have that problem.\\par \\par Norma Jean\\par \\par \\par \\par -----Original Message-----\\par From: andreas.kutsche@spar.at [mailto:andreas.kutsche@spar.at]\\par Sent: Saturday, September 13, 2003 7:57 AM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: Antw2: RE: Re: Informix isolation levels [1866]\\par \\par \\par ----LNX_Sat_Sep_13_2003_14:54:13_V3.33--\\par Content-Type: text/plain; charset="iso-8859-1"\\par Content-Transfer-Encoding: quoted-printable\\par \\par >Datum: 2003.09.13 01:45:25\\par >Sender: Obnoxio The.... <obnoxio@hotmail.com>\\par >Betreff: RE: Re: Informix isolation levels [1863]\\par >\\par >Really. Oracle are wrong. Trust me on this.\\par >\\par >--\\par >Bye now,\\par >Obnoxio\\par =\\par \\par >>>\\par >>>The developer is wrong. The way Informix handles transactions is right=\\par ,\\par >>>the way Oracle handles them is wrong.\\par >>>\\par =\\par \\par >>Andreas Kutsche:\\par >>\\par >>I think that's not correct. It's a different approach.\\par >>\\par =\\par \\par I think I should add that my opionion that Oracle is right (too), only is=\\par valid\\par for COMMITTED READ (the most used isolation level in applications I suppo=\\par se).\\par When it comes to SERIALIZABLE than Oracle is really wrong (or not really\\par serializable under all circumstances) - see\\par http://www.cs.umb.edu/~isotest/snaptest/snaptest.pdf\\par or\\par http://www-dbs.cs.uni-sb.de/papers/tdd99.pdf\\par for examples (keyword: snapshot isolation ).\\par =\\par \\par Regards,\\par Andreas Kutsche\\par \\par ------------------------------------------------\\par SPAR Oesterreichische Warenhandels-AG\\par Hauptzentrale\\par Europastra=DFe 3\\par A-5015 Salzburg\\par =\\par \\par Telefon : +43 662 4470 24423\\par E-Mail : Andreas.KUTSCHE@spar.at\\par Internet: http://www.spar.at\\par ------------------------------------------------\\par \\par =\\par \\par \\par ----LNX_Sat_Sep_13_2003_14:54:13_V3.33----\\par \\par } --openmail-part-41e19bc9-00000002--

----LNX_Sun_Sep_14_2003_10:46:12_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.14 01:52:01 >Sender: NormaJean.Sebastian@tellabs.com >Betreff: RE: Antw2: RE: Re: Informix isolation levels [1866] > >If oracle is so perfect (pardon me, I have only DBA'd on it for less >than 2 years), why does it error with a "timestamp too old" message? > = The "timestamp too old" is a little bit similar to a "long transaction" in Informix, but affects reading sessions and (in most cases) not the updating session (only if that reads data itself). Oracle delivers a consistent result set in respect of the start time of your (select) query - and if that information isn't available any more= you get that error. = >If I am an end user, I want my data... I don't want my transaction to >abend because the database is confused... = The database is not confused. There where only too much changes to data and older information isn't longer available. But it's a reasonable opionion to say that shouldn't happen. As I said I would prefer to say Oracle is correct, TOO, but I never said it's perfect= =2E It's a different approach which leads to different problems. = >Informix gives me dirty read.. I get my data as of timestamp X... it's >all consistent as of timestamp X. If someone changed the data at >timestamp X+5 (while my rpt is still running), who cares... .. my report= >wanted timestamp X, and I got it. Oracle goes poopy on timestamp too o= ld? = Dirty read (as Informix gives us) is not the same as the time based consistent read that Oracle provides. With Dirty read you can get a value changed by a transaction that rolled back later. Logically that value was never entered (valid) into the table/database, but you have selected it. With Oracle that can never happen. = >Pardon my ignorance, I could be totally wrong, but I have seen the >'timestamp too old'. I don't really understand it... and I think any >good database should not have that problem. = You may put it that way. An Oracle man would say in any good database readers should never block writers and vice versa (as Oracle puts it).= Different concepts, different problems, different solutions. And you can say again in a good database a writer (even on other tables) should never kill a reader (snapshot too old). .... = 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_Sun_Sep_14_2003_10:46:12_V3.33----

Hi, You'll never get anywhere in life if you're not flexible in your opinions. Trust me, Oracle is just plain wrong. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche From: "Andreas.KUTSCHE@spar.at" <andreas.kutsche@spar.at> > >I would like to discuss this a little bit further. As an 'informix man' I >often have problems >with applications designed for Oracle originally. And I'm thankfully for >any input to help >me in my discussions with the developers. > >But I stick to my opinion: I don't think that Oracle is wrong. It's just a >different approach. _________________________________________________________________ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger

Well, it's a little inconceivable that I'm defending Oracle, but the snapshot-too-old error is in a strange way equivalent to the long transaction error that you can get with Informix. An Oracle "snapshot-too-old" happens when a read process requires rows from the rollback segments that it can no longer retrieve, since the rollback segments have been over-written by subsequent transactions. This happens because while write operations in Oracle will cause the creation of additional segments within the rollback segment, read operations do not. Therefore, it's possible to need a row for a read that: 1. Has been changed since the beginning of the read operation and 2. is no longer accurately represented in the rollback segment because that segment has been re-used. In general, I think that the snapshot-too-old errors are a little less "harmful" than long transactions (only the offending read session is affected; the rest of the sessions roll merrily along... Not always the case with Informix -- hitting the exclusive high water mark causes all update activity to halt while the offender is rolled back). I'll admit that the dynamic logfile allocation in 9.2 and above does a fair bit to resolve long transactions, but it's still possible to get them, and still possible to do yourself a fair amount of damage with them. Oracle's "snapshot-too-old" error is generally more frequent ('specially if you have really BAD select statements), but isn't as damaging. Regarding the transaction management, I suppose I'm greedy... I'd like to see an isolation level in Informix that would allow me to read the pre-altered images from the logical logs in the same way that Oracle reads from the rollback segments, but I still want the ability to do dirty reads, and use the other isolation levels that Informix has (which are absent in Oracle). Just my two cents. Dan Michaelis 407-758-3395 >From: "NormaJean.S...." <NormaJean.Sebastian@tellabs.com> >To: ids@iiug.org >Subject: RE: Antw2: RE: Re: Informix isolation levels [1867] Date: Sat, >13 Sep 2003 19:54:45 -0400 (EDT) > >If oracle is so perfect (pardon me, I have only DBA'd on it for less >than 2 years), why does it error with a "timestamp too old" message? > >If I am an end user, I want my data... I don't want my transaction to >abend because the database is confused... > >Informix gives me dirty read.. I get my data as of timestamp X... it's >all consistent as of timestamp X. If someone changed the data at >timestamp X+5 (while my rpt is still running), who cares... .. my report >wanted timestamp X, and I got it. Oracle goes poopy on timestamp too >old? > >Pardon my ignorance, I could be totally wrong, but I have seen the >'timestamp too old'. I don't really understand it... and I think any >good database should not have that problem. > >Norma Jean > > > >-----Original Message----- >From: andreas.kutsche@spar.at [mailto:andreas.kutsche@spar.at] >Sent: Saturday, September 13, 2003 7:57 AM >To: ids@iiug.org; forum.subscriber@iiug.org >Subject: Antw2: RE: Re: Informix isolation levels [1866] > > >----LNX_Sat_Sep_13_2003_14:54:13_V3.33-- >Content-Transfer-Encoding: quoted-printable > > >Datum: 2003.09.13 01:45:25 > >Sender: Obnoxio The.... <obnoxio@hotmail.com> > >Betreff: RE: Re: Informix isolation levels [1863] > > > >Really. Oracle are wrong. Trust me on this. > > > >-- > >Bye now, > >Obnoxio > = > > >>> > >>>The developer is wrong. The way Informix handles transactions is >right= >, > >>>the way Oracle handles them is wrong. > >>> > = > > >>Andreas Kutsche: > >> > >>I think that's not correct. It's a different approach. > >> > = > >I think I should add that my opionion that Oracle is right (too), only >is= > valid >for COMMITTED READ (the most used isolation level in applications I >suppo= >se). >When it comes to SERIALIZABLE than Oracle is really wrong (or not really >serializable under all circumstances) - see >http://www.cs.umb.edu/~isotest/snaptest/snaptest.pdf >or >http://www-dbs.cs.uni-sb.de/papers/tdd99.pdf >for examples (keyword: snapshot isolation ). > = > >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_Sat_Sep_13_2003_14:54:13_V3.33---- > > > >--openmail-part-41e19bc9-00000002 >Content-Type: application/rtf >Content-Disposition: attachment; filename="BDY.RTF" > ;Creation-Date="Sat, 13 Sep 2003 18:51:42 -0500" >Content-Transfer-Encoding: base64 > >{\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl >{\\f0\\fswiss Arial;} >{\\f1\\fmodern Courier New;} >{\\f2\\fnil\\fcharset2 Symbol;} >{\\f3\\fmodern\\fcharset0 Courier New;}} >{\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} >\\uc1\\pard\\plain\\deftab360 \\f0\\fs20 \\par >If oracle is so perfect (pardon me, I have only DBA'd on it for less than 2 years), why does it error with a "timestamp too old" message?\\par >\\par >If I am an end user, I want my data... I don't want my transaction to abend because the database is confused...\\par >\\par >Informix gives me dirty read.. I get my data as of timestamp X... it's all consistent as of timestamp X. If someone changed the data at timestamp X+5 (while my rpt is still running), who cares... .. my report wanted timestamp X, and I got it. Oracle goes poopy on timestamp too old?\\par >\\par >Pardon my ignorance, I could be totally wrong, but I have seen the 'timestamp too old'. I don't really understand it... and I think any good database should not have that problem.\\par >\\par >Norma Jean\\par >\\par >\\par >\\par >-----Original Message-----\\par >From: andreas.kutsche@spar.at [mailto:andreas.kutsche@spar.at]\\par >Sent: Saturday, September 13, 2003 7:57 AM\\par >To: ids@iiug.org; forum.subscriber@iiug.org\\par >Subject: Antw2: RE: Re: Informix isolation levels [1866]\\par >\\par >\\par >----LNX_Sat_Sep_13_2003_14:54:13_V3.33--\\par >Content-Type: text/plain; charset="iso-8859-1"\\par >Content-Transfer-Encoding: quoted-printable\\par >\\par >>Datum: 2003.09.13 01:45:25\\par >>Sender: Obnoxio The.... <obnoxio@hotmail.com>\\par >>Betreff: RE: Re: Informix isolation levels [1863]\\par >>\\par >>Really. Oracle are wrong. Trust me on this.\\par >>\\par >>