Re: Informix beats Oracle

Serge Rielau wrote: > Tool wrote: >> Serge, >> >> Got me to want to actually ask you a question. :-) > You are aware that this thread may suddenly turn educational now...? ;-) > >> >> It's my understanding that all other DBMS products are >> pessimistic-locking, ORACLE >> is optimistic-locking, Oracle being only one in this category of >> mainstream DBMS products >> to have optimistic locking. > > Does this mean Informix has both pessimistic _and_ optimistic locking? > The language here is very confusing and I'm not sure whether there are > true definitions. > To me optimistic locking is an entirely different beast and Informix had > support for it for years and DB2 Viper 2 is introducing it. > Optimistic locking (as I understand it) is commonly used in three-tier > architectures where the business transaction is much longer than the > database transaction. Note that in that space all products are on an > equal footing since no vendor can handle isolation across transactions. > In fact I think SQL Server is the most aggressive user of optimistic > locking for many years with deep support in .NET. Perhaps this is fueled > by the default auto-commit behavior (??) > > Ticket purchase at an airline is a typical example for optimistic locking. > You look for a seat going from A->B and you get a set of possibilities > back. Having that information stashed away you check with your > significant other if she agrees and eventually pick one of the option > and hit "book". Two things can happen (actually make that three): > 1. All is well you get the seat "confirmed!" > 2. You get the seat but the price has changes "Confirmed?" > 3. Oops, that seat is gone, try again. > Why? There never was a reservation for any of the seats! > > IDS "natively" supports this sort of logic by supplying change > timestamps. The app keeps these around and when update time comes around > the change time stamp is compared to what's in the database. > This approach is "optimistic" in the sense that it assumes that the > stamps will match and you get that seat. > > Now, within a transaction a similar scheme can (but not must) be used in > a multi version concurrency control situation. This would imply that a > cursor for UPDATE does not acquire an intend-update lock and the UPDATE > itself may fail if the row has changed between the FETCH and the UPDATE. > To do this reliably a change time stamp must be available for each row > in each table. To the best of my knowledge Oracle records these on a > page level however (i.e. an undosegments have a page granularity, not a > row granularity). But I'm sure Daniel or Mark T. would be more qualified > to comment than I am. > > In IDS Cheetah READ COMMITTED is a variation on cursor stability and I > do not think that it uses optimistic locking either. I'm sure Madison or > Jonathan can confirm or dispel my assumption that an intent-update lock > will be used since a very fast transaction could otherwise come in > between the FETCH and UPDATE and COMMIT with no way of the cursor being > notified. > > I can't say I'm an expert in locking and isolation level. But hey I > tried :-) > > Cheers > Serge > I couldn't follow you completely, but I tend to agree that LAST COMMITTED READ is not optimistic locking. In my opinion it's a very nice feature (although, surprisingly, I don't think it's one of the customers favorites by now...). But it's pointed to one of IDS pain points: readers were too many times blocked by writers. And it caused great anxiety to anyone porting an application from Oracle. When we have access to the code it's relatively easy to solve these issues, but in some applications it causes several problems. And the way it was implemented is (I'm biased...) brilliant. By this I mean the control we can have on how to use it. Optimistic concurrency is by definition, and as far as I could search, what you described, although I think I never saw any reference to support in any RDBMS. We can implement it in the application code, but on the RDBMS i don't know... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...