SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Locking table why??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrtweaver
Yak Posting Veteran

USA
66 Posts

Posted - 01/27/2010 :  17:19:37  Show Profile  Reply with Quote
I am still fairly new to SQL but I have a question about tables and locks.

I have this one table and it feeds about 3 or 4 views. This table is updated periodically thru a VB interface. Today we had an issue where the tables somehow got locked. We could not delete it, we could not edit it, we could open and view it but that is about all we could do.
First thing we tried was to shutdown the SQL service and bring it back. This did not help. So then we shut down the server and brought it back up, now we could get in, edit, modify ect the table in question.

So my question is how does a table lock itself and how can one unlock it without having to reboot the server? Also is there someplace where Ican look to see what caused the lock?

And please be as specific as possible as I said am still new. Thank you.

I realized I forgot to mention this application has been running along fine for 6 months. It just choose to do this and I need to know why.

Edited by - mrtweaver on 01/27/2010 17:21:16

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 01/27/2010 :  17:34:20  Show Profile  Reply with Quote
There are lots of articles and books and such on the topic of locking. In general a table will be locked when an insert or update is happening (it's possible to lock it for reads too) and possibly a trasaction is open. So, if a VB app started a transaction and never completed the transaction it could block things up. Again there are tons of ways to approach this subject, but one quick thing is to check to executing processes via a built-in stored procedure called SP_WHO2. This can show you want SPID is blocking the others and give you some information about that SPID that might help you identify the source. If you know which SPID is the blocking SPID you could KILL that SPID. But, you should try to figure out what is causing the blocking. Rebooting the server or stopping and starting the service is a risky way to solve that kind of problem.

Edited by - Lamprey on 01/27/2010 17:37:01
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/28/2010 :  00:02:09  Show Profile  Reply with Quote
In addition to Lamprey's comment, Is your table indexed Properly?
Go to Top of Page

mrtweaver
Yak Posting Veteran

USA
66 Posts

Posted - 01/31/2010 :  12:58:49  Show Profile  Reply with Quote
Ok this pertains to this locking issue as well but it also is more indepth. As you have read here this table is locking. What i dont know is how SQL treats commands sent to it. Does it treat them in a sequential type method and places them in a stack and processes one at a time or does it do more of a parallel type where it can execute more than one command at a time? I ask becuase the application that is accessing the SQL database instance when this table in question locks, it ends up faulting and dropping the connection between the application and the database instance. I dont know why and need more information as to how transact commands are processed.

Or if there is someone here who knows a lot about SQL who is in or near the town of Bloomsburg Pa and would be willing to share knowledge face to face I would appreciate that as well. Thanks and have a great day.
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 01/31/2010 :  15:35:06  Show Profile  Reply with Quote
i think you have problem in your application. especially the part, where you mention that connection was lost a couple of times. this is either the case of inconsistent VB writing, not closing the connection after reading the database etc. etc.
T-SQL tables don't get locked, just like that. Lock is a "security prevention". Again, either your application was running sequentially two queries against same table that needed some sort of lock, or maybe you have two users simultaneously accessing|altering data from same table.

Check what's going on in front-end part of application as well.
Go to Top of Page

mrtweaver
Yak Posting Veteran

USA
66 Posts

Posted - 01/31/2010 :  19:25:18  Show Profile  Reply with Quote
Ok is lock and deadlock mean the same thing? What I cant figure out is why the application ran for 6 plus months with no issues now all of a sudden it is occuring. I still dont know how TSQL does its thing, does it do it in a sequential type or a parallel type connection? if you do have a table that deadlocks why would it block all the connections causing a read timeout error which then shuts down the connection then reopens it. Maybe I dont have enough concurrent connections available and the que gets filled up and all the qued items want the same table. Dont know still trying to figure out especially the second sentence here. Why did it work for so long now all of a sudden we get issues?

quote:
Originally posted by slimt_slimt

i think you have problem in your application. especially the part, where you mention that connection was lost a couple of times. this is either the case of inconsistent VB writing, not closing the connection after reading the database etc. etc.
T-SQL tables don't get locked, just like that. Lock is a "security prevention". Again, either your application was running sequentially two queries against same table that needed some sort of lock, or maybe you have two users simultaneously accessing|altering data from same table.

Check what's going on in front-end part of application as well.

Go to Top of Page

mrtweaver
Yak Posting Veteran

USA
66 Posts

Posted - 02/01/2010 :  18:29:07  Show Profile  Reply with Quote
bump- thought i would see if anyone has any ideas.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/02/2010 :  05:12:54  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
First steps.

Fire up profiler. and set up a trace that tracks sp:start, sp:completed, tsql:start, tsql:completed. You won't need the other information (probably) so don't include those metrics in the trace. DON'T START THE TRACE YET.

Get your application to the stage where you are just about to update the table / whatever it is you are doing.

Start the trace in profiler. Wait till you see some results start to appear. Switch back to your app and click whatever button / whatever you do to trigger this problem. Wait a few moments (even if this time it all works).

Stop the trace.

You will now have a great deal of information. It's time to get digging.

I'm guessing that you may have two processes trying to read or write from the same data at the same time. You'll be able to see this as two different SPID's that both reference that table. Depending on the amount of traffic / how chatty your application is you will have a lot of info to wade through.

Sodeep and Lamprey were asking whether your table is indexed appropriately because it could also be that a long running transaction is having to table scan to do something and another request gets put in. This causes blocking.

Blocking and DEADLOCKING are NOT THE SAME THING.

a block is when a process has to wait for another process to finish. These are actually very common. Good indices and queries eliminate blocking.

DEADLOCKING is where sql server can't decide which process has to wait for the other and therefore it will choose the LOWER COST process to terminate.

Ideally you want to do all of this on a test environment unless you can't replicate the problem in that environment.

You can also try changing the isolation level of the database to READ COMMITTED SHAPSHOT. This should remove blocking between reads and writes. However, if that works you should STILL try and do the steps above because your app is obviously doing something it shouldn't be doing.

Good luck.




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 02/02/2010 05:14:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000