Re: Informix vs. Oracle

jna@carmenta.se (Johan Andersson) wrote: :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? This is exactly how I have understood it as well, although your quote doesn't say so. The quote must be wrong I think. :>: 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. Should definitely have left out the parenthesis. It should have said several cases only. :> :>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. You sure do have a very valid point here, and one must be extremely aware of this if one use dirty read. However everything in the world isn't bank accounts. Even if they where and you wanted to know only the number or ammounts of deposits *or* withdrawals you may be able to use dirty reads even in your example. For us there are a lot of similar cases where we do need the transactions for consistency, but there are a lot of reports that can still use dirty read. Also there are a lot of cases where an interactive program can truly use dirty reads. It's not a question of the application beeing transactionless. It's a question of what part of the data you have to rely on. In these cases the Informix solution have an advantage of having to do less reads and writes to complete a transaction. :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! Definitly *not* allways as that would mean you couldn't read the "dirty" data. In some interactive applications that are run concurrently with large batch updates that becomes a problem. We have that type of situation. The ideal situation would have been to have both options: the way it works now, and mulitversioning. That shouldn't be to hard to implement if they first decide to do multiversioning. It may even be necessary to keep the old behaviour arround so as not to break current applications. If that's not the case (for the better written ones it isn't a problem of course) it's still a good idea to keep the performance and other advantages. 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