[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL locks

Posted on 2010-03-26
13
Medium Priority
?
301 Views
Last Modified: 2012-06-21
I have a table in an SQL database. It has 100000 records in it so is not huge.
If I run an update on it e.g update table set column1 = 0 it can take ages and the users notice a huge difference i.e. the system slows down. If however I restart SQL server and run it, it only takes a second or 2. This leads me to think that something is locking the records or something like that and that the restart clears theses locks. Does this make sense and is there something I can run to clear locks.

It might be worth noting that the users are using MSAccess front end. I use ADO
0
Comment
Question by:Eamon
13 Comments
 
LVL 2

Expert Comment

by:brian-jg
ID: 28680813
I've seen far too many database apps which don't close connections. Check how many processes are running in the db when you have this issue.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 28680871
>>If I run an update on it e.g update table set column1 = 0 it can take ages and the users notice a huge difference i.e. the system slows down.

Its because it locks the table.  Instead of updating everything, try updating x number of records at a time until you loop through and update them all.  
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 28680907
BTW...this has nothing to do w/ closing connections.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 5

Expert Comment

by:advfinance
ID: 28680976
When it locks, and before you restart run "exec sp_who2" and it'll give you a list of active processes. One of the columns it returns, "BlkBy" indicates which process IDs are blocking other processes, so you might be able to narrow down your options of what's causing the block that way
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 28681116
>>This leads me to think that something is locking the records or something like that and that the restart clears theses lock

Sure, when you update an entire table, it is going to lock it...which prevents access.  If you loop through and update x number of records at a time, you'll get around this. Restarting is NOT going to help.  In fact, it won't matter because the process will need to be rolled back when the instance comes back up.
0
 
LVL 9
ID: 28683327
This might not fit your situation, but it's something to consider.  Is the field you are trying to update also updated by users directly, or is it separate?  If separate, then you could try to take advantage of partial row concurrency checking.  If users are busy updating fields A, B and C, but you need to sweep through and update field D while they are working, then this can help.  You need to make sure that the Access update forms don't use Select * or include field D in their recordsource.  You also can't have a RowVersion (aka TimeStamp) field in your table, because that will force full row concurrency.  With this scenario, you'll be able to zip through and make changes to field D without disturbing the users.
0
 
LVL 1

Author Comment

by:Eamon
ID: 28683441
I dont mean restarting after it is going slow. I mean that if I restart before I run it I know it will run way faster.

I have attached the result from exec sp_who2.
The database I use is PopTables.

Book1.xls
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 28683730
sp_who2 doesn't matter...you already know what is causing the problem.
0
 
LVL 1

Author Comment

by:Eamon
ID: 28691487
Maybe I am confused. I don't know what is causing the problem.
0
 
LVL 1

Author Comment

by:Eamon
ID: 28691602
thanks ArmenStein but there are too many unknows with the access side. lots of different forms use that table.
0
 
LVL 1

Author Comment

by:Eamon
ID: 28691926
I think at this stage I am looking to see are there any 'Maintenance' stored procedures in SQL. Something to clear all locks or whatever. You see if I ask the users to stop for a few minutes and then run the update it is still slow compare to when I restart SQL server. I dont think the problem is that they are updating the table at the same time as me. I have a feeling that access is leaving the table in some way that is causing the problem.

I hope I am making this clear and that you know I am just guessing at the cause.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 28728530
>>I hope I am making this clear and that you know I am just guessing at the cause.<<
Just to be clear you are attempting to UPDATE all 100,000 rows with a single UPDATE statement?  If so, did you try chapmandew suggestion?
0
 
LVL 1

Author Comment

by:Eamon
ID: 28956546
Yes that is what I am trying to do. I dont know how to loop through without writing code and I dont want to have to write code for every update I will ever want to do.

I think what I am looking for is a statement or something to UNLOCK the records. If such a thing does not exist then I think that might be the answer to this question.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question