Re: Informix vs. Oracle

Som reflections on Oracles multiversioning vs Informix read locks. In article <3401c8cb.42861771@gate.idg.no>, Nils.Myklebust@idg.no says... > >ennis@ssax.com (Bill Ennis) wrote: >:I've believe I have fact that Oracle lock all rows associated with >:update cursors at the open of the cursor. [....] > >This is confirm by Thomas Kyte from Oracle. >:Someone posted the fact that others can still read the data while >:the update cursor is in progress. This is in fact true. Even if a >:row gets updated others will still be able to see the uncommitted >:version of the row. >: I always thought you got to see the 'old version' of the data, ie non-committed data is never visible to any other session in Oracle than the one doing the update. 'Dirty reads' is not a concept in Oracle, am I wrong here? >: In Informix this would create an exclusive lock >This may be a problem when you use Informix. I do however prefer the >Informix solution in several (most perhaps) cases. > >It's a common misunderstanding that it's a bad idea to use dirty read. >Remember virtually every transaction is going to be committed so >"dirty" data will usually become the "real" data in a short time and >most often this is the data we want. > I use Informix, I havent touched Oracle for a couple of years now. However, multiversioning is very high up on my wishlist for Informix features. I do not agree that 'dirty reads' are harmless. This can only be true in very special contexts. In the general case, we update multiple rows together in an atomic transaction. Keeping this transaction atomic is the key to consistency. For example, if we have a banking application that in a single transaction needs to decrease the value in one row and increase the value in another row. A typical move of an amount of money from one account to the other. What happens if another client happens to access these to accounts in the middle of the transaction, using 'dirty reads'? Rollback is not the issue here. The problem is that the viewed data is inconsistent because only half of the transaction has been performed. My conclusion must be: "Never, ever use dirty reads!" Unless, your application is in essence transactionless, ie every updated row is in itself considered a transaction and leaves the database in a consistent state. IMHO, 'multiversioning' solves this issue without imposing read locks. It does this by a semantic that essentially says: 1 - 'Nothing is written to the database until commit is done!' 2 - 'Your queries return the result that you would have gotten if the query was instantaneous.' The implementation of (1) is of course not done so as to delay writing until commit, instead old versions of data are kept around to ensure the semantic. The implementation of (2) is rather interesting for time consuming queries, there might be times here when committed data are disregarded in favor of old versions of data! If you think about transaction consistency, this is actually desirable! /Johan Andersson >An example (which is very typical for us at least): >Say process A do updates using an update cursor. >Process B use dirty read and get the updated data (typically an >interactive application). >Then process B want to update this. >Process A uses releatively short transactions so they are finished >fast. The locks are released so process B can now do the updates. >Process B do the updates with a where clause that includes the >originally read columns and their values. This is standard procedure >for optimistic locking. If the values wheren't changed since process B >read them the update should be successfull. If they where changed the >update should be rejected. The where clause has exactly this effect. > >This works fine with Informix for the rows that where updated (but not >yet committed). If there where rows in the transaction of process A >that wheren't yet updated when they where read by process B the update >of process B would be recjected. That's fine and as it should be. >Process B will have to take whatever action is appropriate. > >Using Oracle this senario doesn't work as well. The only option for >process B is to read the old data (before the updates inside the >transaction of process A). This means all such rows will contain old >data when process B is able to update them (when process A has >finished it's transaction). A typical interactive application will >have to present the data to the user again, showing what was updated >and ask what to do about it. We would like to avoide that as much as >possible. > >Also if a transaction does go to rollback it's very likely that the >whole database is about to crash (unless you are among those who >missuse transactions to roll back things the user or program desided >shouldn't be updated anyway). At least the table in question is very >unlikely to be available for any further operation by anyone if a >rollback happens. Dirty reads are a nice feature. > >I believe that these locking issues are part of the reason why you >generally get better performance from Informix. > > >Nils.Myklebust@idg.no >NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway >My opinions are those of my company >The Informix FAQ is at http://www.iiug.org -- ________________________________________________________________________ | >>> The opinions herein are mine and not neccessarily my employers <<< | | Johan Andersson, Msc CSE jna@carmenta.se | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~