How to detect table lock
-
Hi all, I have a database on SQL Server 2005, serving several clients. Some of my queries need more than 20 seconds to finish. Problem occurs when ClientA runs the query, and one second later ClientB runs it as well. Since the query involves some editing, it will lock the table for the duration of the query. So what usually happens is that ClientB will have "query timeout expired" error message because the request cannot be completed within the default 30 second query timeout. a. Is there any way to know whether a table/record is being locked, and by which computer? b. Can I change the default 30 seconds query timeout by code? Thanks a lot for any pointer.
-
Hi all, I have a database on SQL Server 2005, serving several clients. Some of my queries need more than 20 seconds to finish. Problem occurs when ClientA runs the query, and one second later ClientB runs it as well. Since the query involves some editing, it will lock the table for the duration of the query. So what usually happens is that ClientB will have "query timeout expired" error message because the request cannot be completed within the default 30 second query timeout. a. Is there any way to know whether a table/record is being locked, and by which computer? b. Can I change the default 30 seconds query timeout by code? Thanks a lot for any pointer.
A. not easily - I would look at your workflow and query and try to isolate the part where the updates to the tables are done and split them into a seperate process from the rest of the query. B. Change the command timeout or the connection timeout.
Never underestimate the power of human stupidity RAH
-
Hi all, I have a database on SQL Server 2005, serving several clients. Some of my queries need more than 20 seconds to finish. Problem occurs when ClientA runs the query, and one second later ClientB runs it as well. Since the query involves some editing, it will lock the table for the duration of the query. So what usually happens is that ClientB will have "query timeout expired" error message because the request cannot be completed within the default 30 second query timeout. a. Is there any way to know whether a table/record is being locked, and by which computer? b. Can I change the default 30 seconds query timeout by code? Thanks a lot for any pointer.
You probably need to split your query down to isolate the actual updates, then the problem will be reduced. Take locks late and release early is the best advice I can give. You can increase the timeout on the command or connection.
Bob Ashfield Consultants Ltd
-
Hi all, I have a database on SQL Server 2005, serving several clients. Some of my queries need more than 20 seconds to finish. Problem occurs when ClientA runs the query, and one second later ClientB runs it as well. Since the query involves some editing, it will lock the table for the duration of the query. So what usually happens is that ClientB will have "query timeout expired" error message because the request cannot be completed within the default 30 second query timeout. a. Is there any way to know whether a table/record is being locked, and by which computer? b. Can I change the default 30 seconds query timeout by code? Thanks a lot for any pointer.
I have also faced the same problem when i used multithreading with very complex query that takes 25 sec to execute. and there are multiple threads so they hit the sql server with the same query at a time which cause Time Out Expired... I think following might help you. You can view the Locks by executing**
sp_lock
Also try these undocumented system stored procedures to get the info about blocking.
sp_who
sp_who2**Read this MSDN KB Article and Try to divide the update/delete statement by block of rows like this [^]