Re: How to make lock escalation look good...

> Does anyone on this NG have experience of systems using page locking and
> lock escalation (I know there must but does anyone have any comments to
> make) especially on DB2 ? I'd be interested to learn more.
>

We use Sybase ASE 12.5. We have about 180 concurrent users during business hours and then drop to batch processing call detail records (CDR's) at night. Hardware is a Sun 420r with 4 cpu and 4G Ram. The DB server is OLTP and runs at about 65% capacity and frequently spikes at 90%. The DB is about 20G in size and growing fast. You can set the server up to utilize 2k,4k and 8k pages. We use 2K pages and it's a serverwide setting:(

ASE has 3 levels of locking allpages(index and data),datapages only and row level. These are applied on each table. We run allpages on most. When a table starts showing contention numbers we move it down to datapages locking. So far we have about 10 hot tables we moved to datapages and 0 row lock tables. We have about 250 plus tables total.  ASE escalates locks. There are about 10 configuration parameters to help tune locking and escalation and maintain effecient operation. Sybase does not have much market share but they are strong in several vertical markets that support high transaction rates. These environments probably require greater tuning effort. Ours maybe small system and it's not IBM buts it's real world and for us page level locking has worked well.

jab Received on Wed May 21 2003 - 11:39:10 CDT