Transaction deadlocked on lock resources.

[sqlturbo]

My query hits hits 7 tables via joins but lately it has started giving me a deadlock error and this problem came overnight. I have run the same query many times before but it never ran into a deadlock problem.
I ran DBCC TRACEON (1204) to get more relevant information but it does not tell me the other process that is involved in the deadlock. The error that I get is...

"Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

How do I find out the other process that is involved in the deadlock?

In a normalised form of frustration !! Help needed badly.

Much thanks.

 

[fluteplr]

You can't unfortunately unless you can look in enterprise manager for lock problems while the deadlock is still in place. Once it had been cured by killing one of the processes ther is no way to know that I am aware of.

 

[rdroske]

Since it is overnight I would check for a scheduled task in the Jobs list or a backup scheduled from another machine that occurs at the same time you are deadlocking (backups would create entries in the log).

A backup would be a good guess since it just stated happening. It's possible that backups have started taking longer due to increased size and they used to finish before your process.

 

[SemperFiDownUnda]

Find out what objects your process locks.
use sp_lock to see what has locks on objects you need to lock. Look at these processes and work it out so that the processes (both yours and the one that is grabbing the objects first thus having your process fail) use resources in the same order so that the 2 processes will serialise instead of deadlock.

Backups should not cause locks on tables that cause deadlocks.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[rdroske]

You are right that backups won't cause deadlock, I was just thinking about scheduled processes and typed a little faster than I was thinking.

But his first problem is figuring out which other process is involved, can't fix anything until he does that.

 

[sqlturbo]

Thanks for the relpies guys.

rdroske, since I posted the query I have not run into the deadlock problem. I wish I had checked for the other process when I got the deadlock. Will try and catch it next time.

On a related note, the query, nevertheless, took long to run. And I am sure there'll be times when it'll run into a deadlock again. Eventually, I got rid of one of the the views. Instead, I prepopulate a staging table and do the joins on that. The sucker runs like a horse. Maybe this is not the thread to go into the details but thought I'd let you know.

Thanks again!