Re: Informix vs. Oracle

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. The way I've done this is :by coding a simple PRO C program that creates an update cursor and :running multiple instances of it on overlapping data rows. If :there is ANY overlap in the data rows multiple programs the first :program will be allowed to open its update cursor while the others :will have to wait until the transaction is complete. 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. In Informix this would create an exclusive lock :on the row (assuming row level locking) and even reading will be :denied (unless the isolation level is set to dirty read - in which :case the dirty reader would see the updated value). This may be a problem when you use Informix. I do however prefer the Informix solution in several (most perhaps) cases. 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. 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. 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