SQL Server 6.5 Locking 
Author Message
 SQL Server 6.5 Locking

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 !!



Mon, 10 May 1999 03:00:00 GMT  
 SQL Server 6.5 Locking

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



Tue, 11 May 1999 03:00:00 GMT  
 SQL Server 6.5 Locking

Quote:

> 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 !!

Assume that the tuple length is 100 bytes.  Then about 19 tuples will
fit on one data page.  This would mean that tuples with ACCNO=1 and 10
would likely be on the same page.  Since pages level locking is being
used, the first client has an exclusive lock on the page and the second
client waits.  With a fill factor of 1%, only one tuple is on each page
and no blocking occurs.

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



Wed, 12 May 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Locking in VB5 and SQL Server 6.5 using stored procs

2. Record Locking in MS SQL Server 6.5

3. Record Locking, SQL Server 6.5

4. Get Username or Computername after lock (or deadlock) errors from MS SQL server (7 or 6.5)

5. ADO's Connection.beginTran will lock SQL Server 6.5

6. New Transact-SQL Debugger for Microsoft SQL Server 6.5/7.0

7. Import text file in SQL Server 6.5 via SQL in Visual Basic

8. SQL Debugger for Microsoft SQL Server 7.0 and 6.5

9. Import text file in SQL Server 6.5 via SQL in Visual Basic

10. Exclusive table locking using ODBC and Access97 or SQL 6.5

11. select Count(*) with date with sql server 6.5 and 7

12. Question regarding Access97 linking to SQL Server 6.5

 

 
Powered by phpBB® Forum Software