Deadlocks and Conflicting resources
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
May 13, 2009 1:52 PM
UPDATED:
May 13, 2009 5:39 PM
Answer Wiki:
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
----------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
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?
Can you post the code and the error messages ?