Isolation levels in Informix vs Oracle
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
"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. Similarly there are circumstances where committed read, repeatable read and serializable read are also useful. . Each has different concurrency level and must be used appropriately. The problem with most the developers is that they know jackshit about isolation level and concurrency issues and they go with default with whatever JDBC offers. On that count Oracle's versioning feature is cool. It is really an idiot-proof feature. But beyond that it is nothing to crow about. SQL Server 2005 is offering the same versioning by calling itself READ_CONSISTENCY. But unlike Oracle, it also offers other isolation modes of Informix/Db2. Use them where they are appropriate.
to add more, because of Oracle's versioning feature, I feel that its FOR UPDATE technique is less efficient. For e.g if there is a work flow table where competing sessions try to grab the first available row in the queue. May be that's why Oracle recommends using AQ tables for such a task. "rkusenet" <rkusenet@sympatico.ca> wrote in message news:32aifdF3j06m1U1@individual.net... > > "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. > > Similarly there are circumstances where committed read, repeatable read > and serializable read are also useful. . Each has different concurrency > level > and must be used appropriately. > > The problem with most the developers is that they know jackshit about > isolation level and concurrency issues and they go with default with > whatever JDBC offers. On that count Oracle's versioning feature is > cool. It is really an idiot-proof feature. But beyond that it is nothing > to crow about. > > SQL Server 2005 is offering the same versioning by calling itself > READ_CONSISTENCY. But unlike Oracle, it also offers other > isolation modes of Informix/Db2. Use them where they are appropriate. > > > > >