Re: Isolation levels in Informix vs Oracle

rkusenet wrote: > "PK" <pk26au@yahoo.com> wrote in message > news:667bf0c6.0412150133.7e4ce123@posting.google.com... > >>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 ! > > > dirty read is very useful when the application gurantees that the data being > read is at that instant read-only. For e.g. in a transaction table where you > are generating report for yesterday. You know for sure that all new records > in that table will be for today only. Why bother locking. Dirty read will > do the job just as fine with minimum resource contention. The problem with your analogy is that if by definition it is yesterday's (or read-only) data and no changes are taking place you don't have anything dirty to read. With read-only data any database, with any transaction type, will give a valid answer. Can you address the dirty read issue in a situation where there truly is dirty data. Thanks. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

"DA Morgan" <damorgan@x.washington.edu> wrote > The problem with your analogy is that if by definition it is yesterday's > (or read-only) data and no changes are taking place you don't have > anything dirty to read. With read-only data any database, with any > transaction type, will give a valid answer. > Can you address the dirty read issue in a situation where there truly is > dirty data. This is red herring. The 'dirty read' I mentioned in my example refers to the isolation level provided by Informix/DB2/SS. I am not here to debate whether the nomenclature of dirty read is technically correct, English usage wise. If this pleases you, we can call it READ_UNCOMMITTED as it is known in ANSI lingo. It was in response to the original poster's question about where different isolation level is required. To quote him:- "What is the neccessity or justification for an RDBMS to offer such a feature and do applications really need it ?" I gave one example, where if I were the developer, I would use only dirty read for maximum performance. To claim that 'dirty read' isolation level is useless in real world (which you have done in the past) is a bogus claim. For the same example, Oracle will also give a valid answer, but not without ascertaining for every block whether it has been updated since the query began, as part of its process for maintaining read consistency. Now this is something which has overhead. Nothing comes free.