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

From: SaTriGuy <satriguy_at_aol.com>
Date: 1997/12/02
Message-ID: <19971202155701.KAA25514_at_ladder02.news.aol.com>#1/1


My turn....... ;)

Yes - there is a cost associated with doing a lot of row level locking. Even though the lock are organized by a hash algorthem, by having row level locks does mean that the hash chains can be longer.

Also - If the applications are designed to do "localized updates" such as a group of rows which will tend to be closer together, then page level locks will tend to be quicker because there the resource will already be locked for subsequent updates.

However, if the application is designed such that there are a lot of updates done by all of the transactions and those rows tend to cluster on single pages, then page locking will tend to single-thread all transactions - so then row locking makes more sense.

Also, if there is a tendency for the application to do a lot of inserts on a small section of the table (i.e. on the last page) then again page locks will single-thread the application.

Basically - "know your application and your data". You will never be able to "force" application developers to develop systems to be most optimal. (i.e. deadlocks would never occur if data was always retrieved in the same order - yet they do occur). So, the best that you can do is to application tends to work, how the data tends to grow and be accessed, and then set up the database so that the best possible performance can be obtained. Madison Pruet Received on Tue Dec 02 1997 - 00:00:00 CET

Original text of this message