Re: Tricky SQL Problem: Top "n" of queried records

From: <bmuller_at_kenan.com>
Date: 1998/03/25
Message-ID: <6fbevu$i1a$1@nnrp1.dejanews.com>#1/1

In article <6fakme$rrd$1_at_nnrp1.dejanews.com>,   Sherilyn <Sherilyn_at_sidaway.demon.co.uk> wrote:
>
> In article <6f8qki$9fu$1_at_nnrp1.dejanews.com>,
> bmuller_at_kenan.com wrote:

 <snip>
> > ...it's not possible that you would wait "forever" to access a
> > row in Sybase which is on page that is exclusively locked by another
> > process...I'll not expound on that...
> [ad hominem snipped]
>
> Strike "forever", insert "an indeterminate period, until the other
> user releases the lock on the page."
>
> Now we're all happy. No?

OK, I'll take back part of my original statement. It is theoretically possible that an application could be written in such a way as to hold on to an exclusive lock in Sybase until some action was performed by an end-user, such as defining and opening a cursor with isolation level 3. In such a situation which depends on human interaction, it's possible that the human will never do what he needs to do to release the lock. That would be a poorly designed application.

However, a well-designed application which is aware of generic DBMS

locking strategies and lock promotions should never be in that situation.
Oracle has what Gray and Reuter would call an "Exotic" locking mechanism.
It's a sort of modified "field-call" approach which works extremely well.

Almost everybody else out there has a more standard locking approach which is similar to Sybase's approach. The differences really have almost nothing to do with row-level locking versus page-level locking. Sybase, with their current locking mechanisms, are right not to support row-level locking. It would introduce a tremendous amount of overhead in lock object management. Oracle has no explicit "lock objects" to manage in the general case.

Anyway, a well-designed application running against a Sybase database will never hold an exclusive lock beyond the duration of an explicit and discrete transaction. Therefore, the lock will always be released when the transaction completes, and the transaction will always complete because it will have been coded as an atomic, discrete transaction. The only problem which may occur is a deadlock - which is a possibility because of the whole "lock promotion" thing. However, Sybase always detects deadlocks when they occur and chooses a victim to kill. Therefore, a decent application will never hang forever against a Sybase database.

There is definitely more lock contention in Sybase than there is in Oracle, and definitely more instances of deadlocks, which are practically non-existant in Oracle. Oracle's (7 and 8) locking strategies are brilliant, and in my opinion a lot of Oracle Server's success can be attributed to them.

However, that doesn't mean that Oracle kicks ass over Sybase in every respect. Sybase has strengths over Oracle as well. It irritates me when people blindly side with one server or the other without understanding the technical infrastructure, and by relying on other people's statements regarding "row-level locking", etc.

Anyway, I'm really not interested in participating in a war of any kind, and will refrain from doing so.

Brenda Muller

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 25 1998 - 00:00:00 CST