RE: Isolation levels in Informix vs Oracle
This is a multi-part message in MIME format. ------_=_NextPart_001_01C4E3F2.999D6FC1 Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01C4E3F2.999D6FC1" ------_=_NextPart_002_01C4E3F2.999D6FC1 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable hey lady, =20 i have forgotten most of the details. I get a headache when i try to make sense of it. i have seen the snapshot too old and i didn't think it was as much of a "special feature" as alexey thought. =20 i think oracle added another special feature in oracle 9i just to deal with the snapshot too old problem in the rollback segments. =20 isn't that just special?..... =20 happy holidays, nj =20 =20 ________________________________ From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Christine Normile Sent: Wednesday, December 15, 2004 11:12 AM To: PK Cc: informix-list@iiug.org; owner-informix-list@iiug.org Subject: Re: Isolation levels in Informix vs Oracle Oracle works differently from all other databases. Informix, DB2 UDB, MS SQL Server, Sybase, MySQL all use Isolation Levels to determine how data is locked as it is read. The naming of the isolation levels is not consistent among various vendors, however, most support the same basic functionality.=20 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 work 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 complete and release locks. The way Oracle works is that when a record is locked, a reader gets an old copy of the data from before the lock 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 Oracle Error - Snapshot too old is returned.=20 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) =20 =09 pk26au@yahoo.com (PK) Sent by: owner-informix-list@iiug.org=20 12/15/2004 03:33 AM Please respond to pk26au To: informix-list@iiug.org cc:=20 Subject: Isolation levels in Informix vs Oracle=09 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 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ------_=_NextPart_002_01C4E3F2.999D6FC1 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"> <META content=3D"MSHTML 6.00.2743.600" name=3DGENERATOR></HEAD> <BODY> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>hey lady,</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>i have forgotten most of the details. I get = a=20 headache when i try to make sense of it.</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>i have seen the snapshot too old and i didn't thin= k it was=20 as much of a "special feature" as alexey thought. =20 </FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>i think oracle added another special feature in or= acle 9i=20 just to deal with the snapshot too old problem in the rollback=20 segments.</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004></SPAN><SPAN=20 class=3D957445703-17122004></SPAN><SPAN class=3D957445703-17122004></SPAN><= SPAN=20 class=3D957445703-17122004><FONT face=3DArial color=3D#0000ff size=3D2>isn'= t that just=20 special?.....</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>happy holidays,</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004><FONT face=3DA= rial=20 color=3D#0000ff size=3D2>nj</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D957445703-17122004></SPAN> <= /DIV> <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV> <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT><FONT face=3DArial = color=3D#0000ff=20 size=3D2></FONT><FONT face=3DArial color=3D#0000ff size=3D2></FONT><FONT fa= ce=3DArial=20 color=3D#0000ff size=3D2></FONT><FONT face=3DArial color=3D#0000ff size=3D2= ></FONT><FONT=20 face=3DArial color=3D#0000ff size=3D2></FONT><FONT f