Deadlocks with Object Locks - NO objectids

Question

Deadlocks with Object Locks - NO objectids

harv_sidhu

SSC Rookie

Points: 30
More actions
June 28, 2016 at 11:18 am

#323653

Getting deadlocks between 2 or more SPIds. It gives you the spids, but does not specify what resource is involved. The conflict is on Object Locks. But no description. No ObjectIDs. Does provide OwnerId and Transaction Descriptor - but that doesnt tell me much. Curiously, Log Used is zero for all involved processes

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the deadlock graph?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

harv_sidhu SSC Rookie Points: 30 More actions · Answer 2

Thx for the offer Gail.

Collecting these from Profiler trace. Not easy to extract and post the Graphs.

I tried getting screen shots - but I cant seem to copy and paste into this post.

Would you mind sharing your e-mail? I'll send you a few screenshots

harv_sidhu SSC Rookie Points: 30 More actions · Answer 3

harv_sidhu

SSC Rookie

Points: 30

June 29, 2016 at 10:16 am

#1887675

mine is harvinder.sidhu@cvshealth.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Screen shots are useless, please post the XML deadlock graph here. Profiler does capture the XML.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

harv_sidhu SSC Rookie Points: 30 More actions · Answer 5

You are right. The graph actually masks the real stuff. So eyeballing thru the trace, you never see the actual Text - only the graph. If I select against the trace file though and get the TextData for eventclass 148 now I can see the data. I can now see the waitresources etc and make sense of the mess. But weird though - normally the graph visually gives you the objectids etc....On some of these it did not. Very generic Object Locks. Now that I see the xml data - I can see what's going on that does not show up in the Graphs.

Thx for forcing me to get to where I needed to

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The graph's pretty, but far from adequate for solving deadlocks, I never use it, when I teach deadlocks I show the XML only.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Do you have something like

DECLARE @SomeVar = dbo.SomeFunction(@param1, ...)

in the code of the procedure?

_____________
Code for TallyGenerator