Deadlocks and Conflicting resources

45 pts.
Tags:
Microsoft 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

—————————-

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bleeb
    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?
    45 pointsBadges:
    report
  • carlosdl
    Can you post the code and the error messages ?
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following