How to resolve deadlock for MSSQL 2008

Question

Post reply

How to resolve deadlock for MSSQL 2008

Viewing 8 posts - 16 through 22 (of 22 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

You do not need to wait for another deadlock. In 2008 you can pull the graphs of deadlock events that already occurred:

http://www.sqlservercentral.com/articles/deadlock/65658/[/url]

Pull some of the graphs and post them to the thread.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

senfriends2008 SSC-Addicted Points: 450 More actions · Answer 2

Thanks for response. I agreed! We are going to look on missing and unused index

nhphuong76 SSC Eights! Points: 986 More actions · Answer 3

I found the object causing daedlock. See attachment image at

But How to resolve it ?

thanks

nhphuong76 SSC Eights! Points: 986 More actions · Answer 4

See attachment image at

But How to resolve it ?

thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

The picture is nice, but the XML content of the deadlock graph is preferable. Save it with a .txt extension and attach it to the thread please.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

nhphuong76 SSC Eights! Points: 986 More actions · Answer 6

I sent deadlock txt file and xml file

Please review it

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

I am no expert at reading these but you have one of the more interesting ones I have seen....lots of players 😀

It appears that CSTool.dbo.RequestContents is a popular object. This is far too much for a forum setting. If this is causing a lot of pain in your database consider bringing in a consultant to help you.

Here is a bit to help get you started though:

Procs CSTool.dbo.usp_RequestContentProcessing and CSTool.dbo.usp_GetListAccountWaitingForSyncPassword were two objects engaged in a deadlock.

Review both procs and make sure that if you're accessing the same series of objects that you;re accessing them in the same order. For example, if usp_RequestContentProcessing is updating TableA then updating TableB in a transaction, and usp_GetListAccountWaitingForSyncPassword is updating TableB then updating TableA in a transaction then you are susceptible to deadlocks. That is a simple example. Here is an article (it's a series so keep going to article 2 & 3) I have referred to many times when troubleshooting deadlocks. http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx It was written in 2006 with SQL Server 2005 in mind but the underlying principles have not changed and the guidance is still relevant.

I also notice that you have procs using Dynamic SQL :sick:

The code "WHERE 1=1" is a dead giveaway when tracing, and in the deadlock graph it's clear to see that proc CSTool.dbo.usp_GetListAccountWaitingForSyncPassword is the one making the call...

SELECT RequestContentId,RequestCode,Account,RequestTypeId,ProductId,CreatedBy,Phone,SourceId,EmailAccount,CS,SUP

From dbo.RequestContents

Where 1=1

And RequestCode like '%DBMK%'

And (CS = -3 OR SUP = -3)

<soapbox>it may be an aside to this deadlock issue, and I cannot see the code, but do you really need to use dynamic SQL here? If you decide to make changes to the system to reduce deadlocks try to remove it. If you want help with that effort post the proc code and we'll help you with it, or if you're not comfortable posting the whole object publicly send it to me in a private message.</soapbox>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

nhphuong76 SSC Eights! Points: 986 More actions · Answer 8

nhphuong76

SSC Eights!

Points: 986

September 26, 2011 at 9:06 pm

#1386903

thanks for support