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 |
|
nixhex7332
Starting Member
4 Posts |
 Posted - 2009-01-29 : 11:03:52
|
| Hello!Using SQL Server 2005, placing data into a database from a classic ASP page.I have a stored procedure that inserts data into a table. The next line of code after the SP is a select statement that pulls a coulpe of bits of data from the recently (the line of code previous to it) inserted item to validate some things about the data received.The problem I am having is that the table (even logged in locally to the server) becomes completely locked. As in if I open the table, even right on the server in the Database Management, I cannot edit records. I click on a record and get a "timeout period elapsed" error and that the "row was not committed" and the change is not committed. About ten minutes later the table is fine. Am I potentially causing a deadlock with my coding as specified above? In that the data is trying to be retrieved while the stored procedure is still executing? Will simply adding a NOLOCK clause to my select statement (after the SP) fix this problem? Is a deadlock what is truly happening??I have not been able to recreate, it happens randomly, and has only started to happen in the past week... any help is appreciated. Thanks!Eric Dimbleby |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-29 : 11:29:30
|
There are many questions to solve the problem...But it is not a deadlock!Deadlock means:process1 is locking tableA and is going to read tableB while locking tableA.process2 is locking tableB and is going to read tableA while locking tableB.So process1 is not unlocking tableA while waiting for tableB to readand process2 is not unlocking tableB while waiting for tableA to read.Normally SQL Server identifies the problem and decides who is the winner and who is the victim.I would like to say: Do not use the ssms to edit data in tables...Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-29 : 11:51:08
|
| What you are seeing is blocking/Locking Issue? How long does Insert run? You can use NOLOCK but it leads to dirty reads. |
|
|
|
nixhex7332
Starting Member
4 Posts |
Posted - 2009-01-29 : 12:08:11
|
I definitely never ever manually edit data in a table with the Console. I was only doing that as a test to eliminate the possibility of a timeout in my coding/network occurring. When the timeout error started happening from the browser clients, I hopped right on the server to see if I could do anything in the table. And so that's when I got the same timeout error right inside the database itself. So this proves, in my eyes, it is local to the server. And in fact, the other databases were fine, I could go into their tables and manually edit. So it is local to the database itself, to go one step further. Glad to know it is not a deadlock- so I'm wondering what else it could be??? HMmm... what kind of activity on the server could make my tables timeout when directly on top of the database? quote:
Originally posted by webfred
There are many questions to solve the problem...But it is not a deadlock!Deadlock means:process1 is locking tableA and is going to read tableB while locking tableA.process2 is locking tableB and is going to read tableA while locking tableB.So process1 is not unlocking tableA while waiting for tableB to readand process2 is not unlocking tableB while waiting for tableA to read.Normally SQL Server identifies the problem and decides who is the winner and who is the victim.I would like to say: Do not use the ssms to edit data in tables...Webfred
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
nixhex7332
Starting Member
4 Posts |
Posted - 2009-01-29 : 12:11:11
|
quote:
Originally posted by sodeep
What you are seeing is blocking/Locking Issue? How long does Insert run? You can use NOLOCK but it leads to dirty reads.
If I'm to understand dirty reads correctly, it does not affect me because that data being inserted is not looked at actively. In fact it would not be viewed or updated for days after that initial insert. If I'm not having a deadlock, though, I will turn it off. |
|
|
|
|
|
|
|
|
Am I deadlocking?