Re: Isolation levels in Informix vs Oracle

PK wrote: > Hi, > > As you know Informix offers 'set isolation to dirty read' - a facility > to read dirty buffers. I believe DB2 UDB too offers it but not Oracle. > > What is the neccessity or justification for an RDBMS to offer such a > feature and do applications really need it ? I was told by an Oracle > guy that Informix and DB2 are 'forced' to offer this because of their > architecture and this is not a 'feature' as such ! He says Oracle can > offer it in a 'jiffy' by pointing to its undo tablespace (where before > images of a buffer are kept before modifications) but they will not as > this is not justified ! > > I myself (worked with Informix for 9 yrs but now into Oracle for past > 1 yr ) feel its quite cool. However I would like to get expert > technical opinion. I don't intend to start a flame at all ! > > Many thanks > Prashant When all you have is a hammer everything looks like a nail.... Here is my take: Dirty read shows data that potentially gets rolled back and hence there is a potential to mess up data integrity in case of rollback. Read consistency shows data that may be stale and hence there us a potential to mess up data integrity in case of commit. Both have their uses, both have their faults. For transaction processing there is only one isolation level that is correct: Repeatable Read/Serializable. Anything else has various degrees of buyer beware. Sidenote: SEQUENCE is "dirty read" by definition and it is quite popular in Oracle :-) Cheers Serge

Serge Rielau wrote: > Sidenote: SEQUENCE is "dirty read" by definition and it is quite popular > in Oracle :-) > > Cheers > Serge By whose definition? Certainly none I have ever heard. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

DA Morgan wrote: > Serge Rielau wrote: > >> Sidenote: SEQUENCE is "dirty read" by definition and it is quite >> popular in Oracle :-) >> >> Cheers >> Serge > > > By whose definition? Certainly none I have ever heard. > The freedom of double-quotes Daniel. Open your mind: If you open two connections to Oracle and perform a few NEXTVALs in each without cout committing. Then you roll one connections transaction back the values are "lost". But the second connection was very well affected by the lost values: "Dirty read". SEQUENCEs don't no squat about transactions and isolation levels. Note that I don't blame anyone. Sequences do exactly what they are designed to do, just like a regular dirty read (no double quotes) does exactly what it's designed to do. Cheers Serge

Serge Rielau wrote: > DA Morgan wrote: > >> Serge Rielau wrote: >> >>> Sidenote: SEQUENCE is "dirty read" by definition and it is quite >>> popular in Oracle :-) >>> >>> Cheers >>> Serge >> >> >> >> By whose definition? Certainly none I have ever heard. >> > The freedom of double-quotes Daniel. Open your mind: > If you open two connections to Oracle and perform a few NEXTVALs in each > without cout committing. Then you roll one connections transaction back > the values are "lost". But the second connection was very well affected > by the lost values: "Dirty read". > SEQUENCEs don't no squat about transactions and isolation levels. > Note that I don't blame anyone. Sequences do exactly what they are > designed to do, just like a regular dirty read (no double quotes) does > exactly what it's designed to do. > > Cheers > Serge I can't wrap my mind around your thought that sequence NEXTVAL in any way relates to a dirty read. Here's why: CREATE TABLE mytable (mycol NUMBER); CREATE TABLE t (seqno NUMBER); INSERT INTO t VALUES (1);COMMIT; INSERT INTO mytable SELECT seqno FROM t; ROLLBACK; UPDATE t SET seqno = seqno+1; COMMIT; Do you see a dirty read? I don't. The fact that a transaction didn't take place but the sequence number was lost is not a dirty read. So I just don't get your thinking. Note: No double quotes. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

DA Morgan wrote: > Serge Rielau wrote: > >> DA Morgan wrote: >> >>> Serge Rielau wrote: >>> >>>> Sidenote: SEQUENCE is "dirty read" by definition and it is quite >>>> popular in Oracle :-) >>>> >>>> Cheers >>>> Serge >>> >>> >>> >>> >>> By whose definition? Certainly none I have ever heard. >>> >> The freedom of double-quotes Daniel. Open your mind: >> If you open two connections to Oracle and perform a few NEXTVALs in >> each without cout committing. Then you roll one connections >> transaction back the values are "lost". But the second connection was >> very well affected by the lost values: "Dirty read". >> SEQUENCEs don't no squat about transactions and isolation levels. >> Note that I don't blame anyone. Sequences do exactly what they are >> designed to do, just like a regular dirty read (no double quotes) does >> exactly what it's designed to do. >> >> Cheers >> Serge > > > I can't wrap my mind around your thought that sequence NEXTVAL in any > way relates to a dirty read. Here's why: > > CREATE TABLE mytable (> mycol NUMBER); > > CREATE TABLE t (> seqno NUMBER); > > INSERT INTO t VALUES (1);> COMMIT; > > INSERT INTO mytable > SELECT seqno > FROM t;> > ROLLBACK; > > UPDATE t > SET seqno = seqno+1; > > COMMIT; > > Do you see a dirty read? I don't. The fact that a transaction didn't > take place but the sequence number was lost is not a dirty read. So > I just don't get your thinking. > > Note: No double quotes. That's OK, you don't have to.