MS SQL Server ODBC - locking on accessing tables on SQLServer through ODBC

Author

locking on accessing tables on SQLServer through ODBC

P. Scaram

2006-10-24, 6:40 pm

Dear Sir,
Thank you in advance for your help.
I am using SQL Server and a view I have created between two fairly large
tables. I have put indexes but because the records between the two main
tables are many to many with the query analyser it uses hashing to find the
entries.

I am using the view with a odbc connection to an access form that performs
searches. This search seems to create locks and if you move to another form
to update the record you get ODBC time out. I looked at the activity and the
view seems to create lots of PAG and a TAB lock on the table. I did several
tests by changing the application. Also using an Access Query rather than a
SQL view. The same problem.
This does not happen all the time , only when the system is busy.

I think that the locking takes place while the records are fetched. I
expected once they are fetched to release the locks but it does not always
happen.

How can I make sure that once the form is open the locks are released.

My last idea is on the open event to add a GOLAST GOFIRST command to scan
all the recordset in the hope it will release the lock.

Any other Ideas

Sue Hoegemeier

2006-10-24, 6:40 pm

You are going in the right direction for the current
implementation of how the form is populated. The problem you
are seeing is that you will have the locks until you consume
the entire recordset. I'm guessing you are seeing IS locks
on the table and S lock on the pages you are on until you
move to the end of the result set. You can reproduce this in
Enterprise Manager and watch the locking behavior just by
opening a large table. The locks won't release until you
move to the last row.
The other thing you'd ideally want to think about is the
size of the recordset you are retrieving in the first place.
A user can't really work with hundreds of thousands of rows
return which populate a form. Even if displayed in a grid,
there are only so many rows they will be able to view and
work with. In that case, you want to retrieve data in
"chunks" rather than everything all at once. You can find
several different ways to approach this, ways to page
through a recordset in the following article:
http://databases.aspfaq.com/databas...-recordset.html

-Sue

On Wed, 4 Oct 2006 06:41:02 -0700, P. Scaram
<PScaram@discussions.microsoft.com> wrote:

>Dear Sir,
>Thank you in advance for your help.
>I am using SQL Server and a view I have created between two fairly large
>tables. I have put indexes but because the records between the two main
>tables are many to many with the query analyser it uses hashing to find the
>entries.
>
>I am using the view with a odbc connection to an access form that performs
>searches. This search seems to create locks and if you move to another form
>to update the record you get ODBC time out. I looked at the activity and the
>view seems to create lots of PAG and a TAB lock on the table. I did several
>tests by changing the application. Also using an Access Query rather than a
>SQL view. The same problem.
>This does not happen all the time , only when the system is busy.
>
>I think that the locking takes place while the records are fetched. I
>expected once they are fetched to release the locks but it does not always
>happen.
>
>How can I make sure that once the form is open the locks are released.
>
>My last idea is on the open event to add a GOLAST GOFIRST command to scan
>all the recordset in the hope it will release the lock.
>
>Any other Ideas

Sponsored Links