Re: Isolation levels in Informix vs Oracle

Posted in 2004

Christine Normile — Wed, 15 Dec 2004 11:12:24 -0600 — source: Usenet: comp.databases.informix

--0__=09BBE5F8DFCEEE0B8f9e8a93df938690918c09BBE5F8DFCEEE0B Content-type: multipart/alternative; Boundary="1__=09BBE5F8DFCEEE0B8f9e8a93df938690918c09BBE5F8DFCEEE0B" --1__=09BBE5F8DFCEEE0B8f9e8a93df938690918c09BBE5F8DFCEEE0B Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Oracle works differently from all other databases. Informix, DB2 UDB, = MS SQL Server, Sybase, MySQL all use Isolation Levels to determine how dat= a is locked as it is read. The naming of the isolation levels is not consis= tent among various vendors, however, most support the same basic functionali= ty. Oracle uses different mechanisms to achieve similar functionality. It = is important for developers to understand the differences between the way = most databases behave and Oracle so that they can write their programs to wo= rk as desired. Dirty Read allows readers to read data that has not been committed to the database. So a reader may get data that does not end = up in the database. This allows readers to access the most current anticipated version of data without having to wait for updates to compl= ete and release locks. The way Oracle works is that when a record is locke= d, a reader gets an old copy of the data from before the lock as long as the= re is still a before image in the rollback log (Norma - did I remember tha= t right?). If no before image is available, a Oracle Error - Snapshot to= o old is returned. Christine Normile Informix Market Manager IBM Software Group Data Management Solutions Phone/Fax 877.252.5399 Email cnormile@us.ibm.com |---------+----------------------------> | | pk26au@yahoo.com | | | (PK) | | | Sent by: | | | owner-informix-li| | | st@iiug.org | | | | | | | | | 12/15/2004 03:33 | | | AM | | | Please respond to| | | pk26au | |---------+----------------------------> >--------------------------------------------------------------------= --------------------------------------------------| | = | | To: informix-list@iiug.org = | | cc: = | | Subject: 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 = --1__=09BBE5F8DFCEEE0B8f9e8a93df938690918c09BBE5F8DFCEEE0B Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> Oracle works differently from all other databases. Informix, DB2 UD= B, MS SQL Server, Sybase, MySQL all use Isolation Levels to determine h= ow data is locked as it is read. The naming of the isolation levels is= not consistent among various vendors, however, most support the same b= asic functionality. Oracle uses different mechanisms to achieve similar functionality. It = is important for developers to understand the differences between the w= ay most databases behave and Oracle so that they can write their progra= ms to work as desired. Dirty Read allows readers to read data that ha= s not been committed to the database. So a reader may get data that do= es not end up in the database. This allows readers to access the most = current anticipated version of data without having to wait for updates = to complete and release locks. The way Oracle works is that when a rec= ord is locked, a reader gets an old copy of the data from before the lo= ck as long as there is still a before image in the rollback log (Norma = - did I remember that right?). If no before image is available, a Orac= le Error - Snapshot too old is returned. Christine Normile Informix Market Manager IBM Software Group Data Management Solutions Phone/Fax 877.252.5399 Email cnormile@us.ibm.com <img src=3D"cid:10__=3D09BBE5F8DFCEEE0B8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for pk26au@yahoo.com (P= K)">pk26au@yahoo.com (PK) <table V5DOTBL=3Dtrue width=3D"100%" border=3D"0" cellspacing=3D"0" cel= lpadding=3D"0"> <tr valign=3D"top"><td width=3D"1%"><img src=3D"cid:20__=3D09BBE5F8DFCE= EE0B8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"72" al= t=3D""> </td><td style=3D"background-image:url(cid:30__=3D09BBE5F8DFCEEE0B8f9e8= a93df938@us.ibm.com); background-repeat: no-repeat; " width=3D"1%"><img= src=3D"cid:20__=3D09BBE5F8DFCEEE0B8f9e8a93df938@us.ibm.com" border=3D"= 0" height=3D"1" width=3D"225" alt=3D""> <ul> <ul> <ul> <ul>pk26au@yahoo.com (PK) Sent by: owner-informix-list@iiug.org 12/15/2004 03:33 AM Please respond to pk26au</ul> </ul> </ul> </ul> </td><td width=3D"100%"><img src=3D"cid:20__=3D09BBE5F8DFCEEE0B8f9e8a93= df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> To: informix-list@iiug.org</f= ont> cc: Subject: Isolation levels in = Informix vs Oracle</td></tr> </table> <tt>Hi, As you know I