Home > IT Answers > SQL Server > Deadlocks and Conflicting resources
Bleeb
45 pts.
0
Q:
Deadlocks and Conflicting resources
SQL Server 2000, SQL stored procedures
I have a program that loops through a temp table, and for each row looks up a value in another table, and depending on the result returned inserts a record into one of two tables. Some days this takes 10 minutes to run and others it takes 2 hours to run. There are no other processes running against the tables used so the problems seems to be within the program itself as far as deadlocking tables, etc. The entire loop is within a transaction. The question is how do I determine where the locking is occuring since the run time isn't always consistent?
ASKED: May 13 2009  1:52 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Bleeb
45 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Hi,

Can you give us some more details - what sort of platform, programming language, database, etc?

Regards,

Martin Gilbert.

----------------------------

The first thing I would do is add some logging to the program, to determine what is the instruction/statement that is taking too long to complete.

If all operations are part of the same transaction, and no other processes are touching the involved tables, I don't think the problem is being caused by a database lock.

-CarlosDL

----------------------------
Last Answered: May 13 2009  3:45 PM GMT by Bleeb   45 pts.
Latest Contributors: Carlosdl   29340 pts., Gilly400   23625 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Bleeb   45 pts.  |   May 13 2009  2:10PM GMT

It’s SQL 2000 and the code runs in a stored procedure. It actually runs from a DTS job so I can see the errors that occur and they are due to deadlocking. The insert statements are what are taking so long - I’ve looked at the indexes on the tables to make sure there weren’t excessive indexes and there weren’t. I’m just curious how it could be locking against itself? Would it be because the transactions aren’t committed until the very end of the loop?

 

Carlosdl   29340 pts.  |   May 13 2009  5:39PM GMT

Can you post the code and the error messages ?

 
0