Re: Informix vs. Oracle

tkyte@us.oracle.com (Thomas Kyte) wrote: :A statement in the form: : :Select ... from Table where .. for update; : :will get the update lock on every row before the first fetch occurrs in Oracle :this is true. This is typically not an issue. How many rows do you normally :fetch with a SELECT ... FOR UPDATE statement? Sometimes very many. Even if it isn't that many this means Oracle will have to find all rows of the cursor (to lock them) before it returns any to the application. Not that good an idea. :It is also true that the row(s) affected by the select ... for update is open :for read by any other transaction (you just cannot select for update those rows :that are locked by the above, nor can you update any of those rows). As with :any read/write environment in Oracle, you can always READ the data, without a :dirty read, even data that is "locked". This does sound like a good idea. There does seem to be some performance issues involved in some cases (having to check the log for several purposes i believe), but that may not be that problematic. :Yes, regular cursors may be held open across commits. a SELECT ... FOR UPDATE :cursor may not be. But a select ... for update cursor might be the important one to hold open. I can understand why they don't when they first lock all rows. That seems to me to be another negative effect of this behaviour. For our purposes, OLTP but a lot of updates of large number of rows, the Informix solution seems to be the best by far overall. 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