Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In article <yut90ubc70r.fsf_at_mew.corp.sgi.com>, pablo_at_sgi.com says...
>
>>>>>> "Paul" == Paul Brown <pbrown_at_triplerock.Berkeley.EDU> writes:
>
>Paul> But the practical reality is
>Paul> that people don't always do 'the right thing'. This is the
>Paul> 'hard lesson' Gray refers to.
>
>Unfortunately what you say is true... this is why Oracle's
>versionings works so well... they appeal to the general
>masses who don't do things right. I guess therein lies opportunity.
>

A reflection from a member of the 'general masses'...

Why is Oracle's versionings bad?
Being able to get the response I would have gotten had my query been instantaneous, without needing to fear read locks or other clients changes during the time my query is running, is something I consider a 'good thing'. It is the feature highest on my Wish-list for Informix.

With regards to row/page locks.
This all boils down to concurrency, the finer granularity the better concurrency. If we had value locks we would have the potential for even better concurrency than today. This all comes at a price of course, but I would like the option to choose, not be restricted by the technical shortcomings of the database system.

There is a related issue here, long transactions. I believe Mr Bergmann put his finger on it when he wrote:

In article <347B239E.CF0485C6_at_strengur.is>, snorri_at_strengur.is says...
>
>Page lock workarounds could be:
>
>[....]
>
>2) Use optimistic locking. (So users who have been typing in for hours
>get the message: Somebody else has modified this rec while you were
>working. Please try again).
>
If long transactions are allowed, then the database system can be used to support a pessimistic locking scheme.

This relates to row level locking because a common solution today is to map 'objects' of some sort into one or more rows in a database(1). When a user changes an object in an interactive process taking everything from seconds to hours, the user must be guaranteed that the changes can be made (This is Snorris point I believe). The easiest way to guarantee this is to use pessimistic locking.

My point here is that in a modern system, the user is all to often a part of the transaction, we can not ignore that. When making the choice, as an application developer, of being nice to the user or the database, I know who comes out on top :-)

/Johan Andersson

(1) Object Oriented modeling and design / James Rumbaugh, ISBN 0-13-630054-4

-- 
 ________________________________________________________________________
| >>> The opinions herein are mine and not neccessarily my employers <<< |
| Johan Andersson, Msc CSE                               jna_at_carmenta.se |
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~