June 4, 2008 at 11:03 am
Is there a way either thru sql or using ADO Properties to tell whether or not a table is locked. Basically my problem is we write most of our intranet in asp and sometimes we have pages that timeout because a table is locked. Also is there a way if it is locked by another user i can force ably unlock it? Hope this make sense, any help will be appreciated.
June 4, 2008 at 12:53 pm
With the stored procedure sp_who2 you can see which connection is blocked by another connection. Then you could kill the offending connection (beware of the rollback).
Current locks: sp_lock
June 4, 2008 at 1:43 pm
is that a default stored procedure in sql 2000?
June 4, 2008 at 2:03 pm
I guess so. You can find sp_who2 in the master database and it is marked as system.
June 4, 2008 at 2:09 pm
mbender (6/4/2008)
Is there a way either thru sql or using ADO Properties to tell whether or not a table is locked. Basically my problem is we write most of our intranet in asp and sometimes we have pages that timeout because a table is locked. Also is there a way if it is locked by another user i can force ably unlock it? Hope this make sense, any help will be appreciated.
Are you sure the timeout is due to a lock and not due to poor performing code? How have you determined that locking\blocking is the problem?
The SQL Server has locked the resource for a reason, if you kill the other process you will lose the modifications that process was making. What you need to do is use sp_who2, DBCC InputBuffer, and sp_lock to determine what is causing the blocking to occur and fix the code. It sounds like you either have some long running transactions that are causing problems and you could break them down into smaller transactions. It may could be that the order in which you are accessing tables is inconsistent and this can cause problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply