Re: Informix beats Oracle

Thanks Serge.

My training has been primarily non-Oracle, meaning that I've learned to
program outside the database, whereas it's my impression that Oracle folks
program applications with a high dependence on the database engine, thusly
why I'm convinced that you can't compare most other database products with
Oracle simply because it's a lot different than most. What I'm gathering
is that it looks like Informix got a big boost in features, but to appreciate
these features you really have to understand how a lot of applications work
that are being used by the big database companies, i.e., SAP, etc. So for
me until I see a benchmark comparison of applications and some specific
task that is compared, I think basic I vs O comparisons are not realistic,
and only superficial at best.

Thanks for your insight...

-t-

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


.

Relevant Pages

  • Re: Informix beats Oracle
    ... pessimistic-locking, ORACLE ... is optimistic-locking, Oracle being only one in this category of mainstream DBMS products ... To me optimistic locking is an entirely different beast and Informix had support for it for years and DB2 Viper 2 is introducing it. ... You look for a seat going from A->B and you get a set of possibilities back. ...
    (comp.databases.informix)
  • Re: What so special about PostgreSQL and other RDBMS?
    ... That's exactly the link the licence agreement for the database points to when it ... comes to what wecan expect for paying support. ... > "Oracle may provide additional releases or versions of its programs ... If the requirements are volatile I'd do a long term contract detailing what ...
    (comp.lang.php)
  • A cool DBA job wanted
    ... 7, Oracle 6, Sybase, SQL Server ... Proposed proactive database monitoring through ... strategies to administer remote Oracle databases ... Trained installation and support personnel in basic ...
    (comp.databases.oracle.server)
  • Re: I want to add to myknowledge
    ... 7, Oracle 6, Sybase, SQL Server ... Oracle Database Administrator ... Trained installation and support personnel in basic ... Senior Oracle Database Administrator ...
    (comp.databases.oracle.server)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Before Oracle decided to allow you to write stored procedures in Java, ... Even without Java existing, let alone being hosted in the database, you ... of languages that they developed for the middle tier, ... the .NET developers out there will be faced with this choice. ...
    (comp.lang.java.programmer)