Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2010-01-27 : 17:19:37
|
| 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. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-01-27 : 17:34:20
|
| 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. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-28 : 00:02:09
|
| In addition to Lamprey's comment, Is your table indexed Properly? |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2010-01-31 : 12:58:49
|
| 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. |
|
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-01-31 : 15:35:06
|
| 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. |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2010-01-31 : 19:25:18
|
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.
|
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2010-02-01 : 18:29:07
|
| bump- thought i would see if anyone has any ideas. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-02 : 05:12:54
|
| 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Locking table why??