RE: Isolation levels in Informix vs Oracle

Concider the following situation: An application is trying to analyze bank's balance during the operational day - that is, some records in the 'account' table might change while the query is running. In Informix, DB2, MS SQL the only way to implement the consistent query in such situation is to lock the entire table. Oracle has a special database engine feature - Roll-back segment (RBS) - that allows the query to make the consistent read of the table (that is, view all records to a single point in time) without locking the table. The other cool feature of RBS is that Oracle almost never places locks on the records while it is just reading the data. Other database servers 'by default' try to provide consistent table view by placing 'read' locks on records. To override the 'read' locking, it is necessary to 'set isolation to dirty read'. In most cases, it is enough to see potentially inconsistent data. In my opinion, the 'bank' situation described above is very rare in the real life. With Informix, DB2 and MS SQL one can make database/application design (e.g. online aggregation) to avoid the locking of huge tables. Please, note, that the above mentioned RBS feature of Oracle in a real life requires a lot of system resources to support it. Improperly designed application, working in a highly concurrent environment, might create a HUGE overhead for the database server to deal with RBS's (note, that with Oracle, it's impossible to avoid using RBS) ------------------------------------------ -Alexey > On Behalf Of PK > > 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 sending to informix-list

"Alexey Sonkin" <alexeis@grandvirtual.com> wrote > In Informix, DB2, MS SQL the only way to implement the > consistent query in such situation is to lock the entire > table. No need to lock the entire table. Only selected rows need to be locked. Other sessions can read the same rows, but can't update it till this session completes its transaction. > In my opinion, the 'bank' situation described above is very > rare in the real life. With Informix, DB2 and MS SQL one can > make database/application design (e.g. online aggregation) > to avoid the locking of huge tables. Very true. For every 'bank' situation one can come up with 'reservation' situation in hotels/airlines etc where Oracle's MVRC architecture will turn out to be inefficient.