Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Db2, Oracle, SQL Server

Re: Db2, Oracle, SQL Server

From: Mark A <>
Date: Sun, 6 Feb 2005 15:19:13 -0700
Message-ID: <>

Noons" <> wrote in message
> >
> > In DB2 the most memory is consumed by
> > the bufferpool. Locklist size is not an issue in reality.
> > If it ain't a size issue it belongs in memory.
> Yeah? So why the lock escalation in the first place?

I think what he means is that there is usually plenty of memory available on servers to use for the locklist to be increased. But the default locklist size is low, to make sure that DB2 starts properly on a new installation. The escalation is just in case it does fill up, either because it was not changed from the default, or if there are a huge number of locks being held at one time.

In some cases, leaving the locklist small and encouraging lock escalation is good, because row locks are more expensive (time consuming) than table locks, especially if concurrency is not an issue. This would be mostly for decision support databases were the data was only loaded when the table is not available to users, or when the table is available to users and only selects are being executed.

A load utility (or any other utility) used in a decision support system usually doesn't need to lock each row, since there may no concurrency requirement (depending of course on the operational requirements). When the users execute Select statements in a decision support application they result in share locks, which can coexist with each other just fine, so there is no need for row locking in this situation either.

Obviously, for OLTP applications, the locklist size must be increased to an appropriate level. Received on Sun Feb 06 2005 - 16:19:13 CST

Original text of this message