Quote:
>> 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. <<
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