This is the scenario....

I'm using SQL Server 6.5 with SP1 on NT Server 3.51 with SP4.
I'm using Visual Basic 4.0 as a front-end using RDO to access the data.
I've got a table with a clustered index (Fill Factor 95%) on the primary
key (ACCNO).

This is the problem....
        On PC 1 -
        SQL statement is "select * from CUSTOMER where ACCNO = 1"
        I create a resultset (Pessemistic Locking) to hold the data.

        On PC 2 -
        SQL statement is "select * from CUSTOMER where ACCNO = 10"
        I create a resultset (Pessemistic Locking) to hold the data.

I get a TimeOut Error on PC 2 because SQL reports a exclusive locks on the
CUSTOMER table.

However, if I change the fill factor on the primary key (ACCNO) to 1% then
both statements work at the same time ????

Can anyone explain what is happening here ?? Using a Fill Factor of 1% is
not logical and causes problems further down the line.

PLEASE HELP !!

Can anyone explain what is happening here ?? Using a Fill Factor of 1%
is
not logical and causes problems further down the line. <<

SQL Server uses page-level locking.  What this means is that when a row
for accno=1 gets accessed (and, since you are using pessimistic locking,
and I assume you are opening an updatable cursor set, the locks on the
page stay until the cursor closes) other rows are also affected.

When the fill factor is 95%, perhaps 30 rows fit on a single page.  So,
if anyone needs to access accno1 through 30, they'll have to wait.

There are two solutions to your problem.  

1. Build the Clustered index on another column to redistribute rows.  To
eliminate hotspots in your tables, build the clustered index on a
distributed column, such as name or zip code or another field that is
not sequentially accessed.  Building a clustered index on an ascending
primary key is rarely a good idea. Remember to build a NC index on accno
to support your lookups.

2. Use optimistic locking.  In the example mentioned here, optimistic
locking should work great, because two users aren't altering the same
row.  When they do alter the same row, you need to add code to handle
the event, so it's a little more work on your end.  

Ben McEwan
Geist, LLC

(518)274-5042

--
Ben McEwan
Geist, LLC

(518)274-5042

As a rule don't create clustered indexes on primary keys (especially
columns like account number which generally are monotonically
increasing).  Place clustered indexes on foreign keys or, if foreign
keys don't exist, on interesting selection columns or interesting
orderings.  Also remember that general probablility that two people will
attempt to access tuples on the same page is then number of pages
occupied divided by the number of users.

Good Luck

--
David Upham
Nan Pao Information Systems, Inc.
Phone:  886-2-816-5511 ext. 35
Fax:    886-2-813-2111