Re: RE: Re: Informix isolation levels

Hi All, This thread seems to be an interesting one. My opinion on this is(For only COMMITTED READ ISOLATION) : Oracle supports multi-version data concurrency with a cost of Rollback segment handling, whereas Informix, DB2, SYBASE, SQL SERVER and many others handle with the Lock tree(growing and shrinking) approach. Even though Oracle tries to give "committed data" always, it may present different versions of "committed data" for different readers, when there is a heavy update activity going on a particular table data. For e.g., User X will read version 1 of data if he starts his query at time say 10 AM. User Y will read version 2 of data if he starts his query at time say 10:05 AM, provided User Z(batch program in the background posting results in the background) updates the same data continuously(If he committed a new data value at 10:01 AM). User X will never see the "10:01 AM" committed data, until he closes his cursor and re-issues a read at say 10:10 AM. At 10:10 AM of course he will probably get the version 2 of data, if User Z has stopped committing new data to the same table. Otherwise he might version 3 of data, which User Y who started his query at 10:05 AM will never see until he closes his cursor and re-issues his read. Overall Oracle achieves this functionality with the help of SCN(System Commit Number) concept. This is equivalent of the timestamping concept used by Informix or any other similar database like DB2 etc. The Lock tree approach followed by Informix, simply prevents presenting any user trying to read rows in COMMITTED READ isolation, to see a new data that is "UNDER UPDATE or DELETE" mode, until that row is committed/rolled back and moved on. It just gives a Physical row locked error and asks you to retry the action. You can set lockmode timeout values and re-try, provided the updating program is frequently committing data in batches. If the updating/deleting program takes a long time to commit/rollback, the timeout values concept do not work so well. I generally prefer the ORACLE approach, since it is a lot easier to handle large BATCH UPDATES(i.e. do not need to commit frequently as you need to do in Informix) even during heavy user activity periods and still can read the old "COMMITTED data" from the Rollback segments. But ofcourse, you may run into the most familiar "SNAPSHOT TOO OLD" error, since the reader might be too slow to read rows(or the read query might be very POOR that it takes a long time to get to the actual row that the user is interested), compared to the speed in which the batch process that is updating the data faster. The SNAPSHOT TOO OLD error occurs when the updating program does not use a HUGE PRIVATE ROLLBACK SEGMENT to help the slow readers read its old data. The Rollback segment goes into reuse mode, once the program that is updating/ deleting commits or rollbacks the transaction. Oracle seems to have come up with some new approaches for fixing this in Oracle 9i and later. So what is the difference between Oracle and Informix in this crucial functionality in the example: 1. If the batch program(User Z) frequently commits, then the User X and User Y who would have encountered Lock errors at various points of their cursor reads, can retry their operation(with set lockmode timeout concept) without closing the cursor and fetch the same version of data. 2. Whereas in Oracle User X and User Y will never see the same data for atleast one row which had undergone updates at various points in time. User X(10 AM start time) will only see the latest version data that was committed prior to 10:01 AM). User Y(start time 10:05 AM) will only see the data that was committed at 10:01 AM. 3. In Informix, User X and User Y will wait until after 10:01 AM to see the new data, since at 10 AM when User X started his query the User Z did not perform a commit. After 10:01 AM, User X who started his query at 10 AM and User Y who started his query at 10:05 AM will see the same version of data, since User Z has already moved onto processing of other rows after 10:01 AM. Basically in Oracle(FOR COMMITTED READ ISOLATION ONLY) User X starts query at 10 AM sees committed data prior to 10 AM, i.e. may be the data that was committed at say 9 AM for the same row. User Y starts query at 10:05 AM sees committed data prior to 10:05AM, which is the 10:01 AM data. In Informix(FOR COMMITTED READ ISOLATION) User X starts query at 10 AM and gets a lock error since the row in question is still locked by User Z. He keeps retrying in lockmode timeout intervals until he succeeds. User Z commits the row at 10:01 AM and moves onto next row. User X sees the new row that was committed at 10:01 AM. User Y starts quert at 10:05 AM and see the same committed data(10:01 AM data) as User X does. He does not get a lock error on this row, since User Z has moved onto other rows for further processing. In Informix, if one wants to see this old data that was committed at 9 AM, you need to be in DIRTY READ ISOLATION, where the COMMITTED READ ISOLATION guarantee that Oracle provides is not available. But it is also possible in Oracle, that one may get SNAPSHOT TOO OLD error , if the read query is poor and runs for a very long time and by the time it gets to the actual row that the user is interested, the rollback segment might have gotten overwritten and it throws this error. Basically there are pro's and con's to this whole ACID property approaches taken by Oracle and Informix DBMSs. Oracle does not use the Redo logs for getting the older versions(similar to Logical logs of Informix), but uses another kind of log called the Rollback segments(Both old and new version of the data is kept here) for this purpose. Multiple versions of old and new data can be scattered in multiple rollback segments and this adds a lot of overhead for supporting the multi-version concurrency approach. Hope my answer details some issues that I had learnt over a period of time working with Informix and Oracle DBMSs. I thank the readers for bringing up an interesting topic of discussion. Thanks Sukumar ALAGAPPAN Disclaimer: These are my personal opinions and do not reflect the opinions of my company in which I am currently employed.