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

First, please excuse me if some of my arguments have already been covered in this thread, our newsserver was out for a week and I haven't seen all of it.

Anthony Mandic <no_sp.am_at_agd.nsw.gov.au> wrote:
>Joel Garry wrote:
>>
>> That is the whole problem with
>> page locking, you can get locked by something that you should not
>> care about.
 

> Only if that lock was held for longer than it ought to have been.

Regardless, I think one should NOT be troubled by things being done to records that are of no concern to that user. With PLL you can (and will) be troubled in such a way.
I think, even in a set-based thinking environment, it should not be the database that causes these problems. I strongly support the availability of RLL (even if sometimes PLL would be a performance advantage, I still want the option of RLL without cumbersome tricks or fake identifiers).

>> The transaction may need to be long. What you are calling a "real" transaction
>> may be an artificial construct solely to deal with the page-locking problem.
 

> No, I've been implying that "read for update" or "select with lock"
> or whatever its called is the wrong approach. The read/select
> should not be part of the real transaction. They could be held
> indefinitely, in theory. The real tansaction is the
>insert/update/delete.

I totally agree. But this is not a valid argument IMHO, it just reduces the timespan in which locking occurs, it doesn't alter the 'theoretical' premises.

> These should be fast. If there are a lot of them in one hit, I'd
> consider rethinking the approach used to design the app that
> does this. The implication here is that the app may be far too
> complex, thus being more vulnerable to problems. Keeping it
> simple never hurts.

Just 'blaming' the apps complexity is no real solution too, I think we should see the apps complexity and way of operating as a given. Just because a RDBMS acts on sets and likes set-like operations, that doesn't mean the customer does the same or should be trained to do so (that sets us back some 30 years, when we still wanted to adapt the users to the programs instead of the other way around).

IMHO a RDBMS should let the user operate on rows, completely independent of the status and concurrent use of 'nearby' rows by other users.
That implies RLL in my opinion.

Also, in a set-based thinking environment: why is some element 'closer' to another element than another element, and why does modifying one element influence the success of modifications to SOME other elements but not to ALL other elements? Elements in a set should IMO be independent objects.

I look at PLL as if the operating system would not let me change foo.txt in directory c:\tmp, just because someone else is currently updating foo1.txt in c:\tmp. That's absurd.

I think RDBMS suppliers should work hard to make RLL or even VLL as efficient as 'easy' PLL schemes that currently outperform RLL schemes. Just because the suppliers do it don't make it right!

Jean-Marc.

+------------------------------------------------------------+
|Jean-Marc.van.Leerdam_at_| All opinions expressed are just ... |
|ingbank.com           | opinions (and my personal ones!).   |