how to analyse deadlock graph and deadlock xml ?

Question

Post reply

how to analyse deadlock graph and deadlock xml ?

anjali.vishwakarma

Ten Centuries

Points: 1112
More actions
January 23, 2009 at 2:08 am

#127580

I am attaching deadlock graph and deadlock xml. How to analyse it and what'll be the solution to avoid deadlock in future ?[/img]

Viewing 6 posts - 1 through 6 (of 6 total)

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

anjali.vishwakarma Ten Centuries Points: 1112 More actions · Answer 1

Sorry the image could not be loaded properly earlier. Also xml contents are as below.

Deadlock Chain SPID = 69

(2b012878be62)

Deadlock Chain SPID = 84

(a201e4ec37d0)

<deadlock-list>

<process-list>

SELECT DISTINCT IMSQD.client_id

FROM dbo.IMS_Quote_Details IMSQD

INNER JOIN dbo.coverages CVGS WITH(NOLOCK) ON CVGS.coverage_id = IMSQD.coverage_id WHERE

client_id IN

(SELECT DISTINCT PCLNT.client_id FROM dbo.policy_clients PCLNT INNER JOIN

dbo.policies PLCY WITH(NOLOCK) ON PCLNT.policy_id= PLCY.policy_Id

INNER JOIN dbo.party_types PTYP

WITH(NOLOCK) ON PTYP.type_id = PCLNT.type_id

WHERE PLCY.policy_id = @policyID AND PTYP.type_id = @CLNTTYPID)

GROUP BY IMSQD.client_id, IMSQD.option_id, IMSQD.revi_lumpsum, IMSQD.coverage_id, CVGS.description

ORDER BY IMSQD.client_id

--Query to return benefit-type FOR member </frame>

exec dbo.procGenerateIMSResult @policyID </frame>

</executionStack>

Proc [Database Id = 6 Object Id = 1234207547] </inputbuf>

</process>

SELECT DISTINCT IMSQD.client_id

FROM dbo.IMS_Quote_Details IMSQD

INNER JOIN dbo.coverages CVGS WITH(NOLOCK) ON CVGS.coverage_id = IMSQD.coverage_id WHERE

client_id IN

(SELECT DISTINCT PCLNT.client_id FROM dbo.policy_clients PCLNT INNER JOIN

dbo.policies PLCY WITH(NOLOCK) ON PCLNT.policy_id= PLCY.policy_Id

INNER JOIN dbo.party_types PTYP

WITH(NOLOCK) ON PTYP.type_id = PCLNT.type_id

WHERE PLCY.policy_id = @policyID AND PTYP.type_id = @CLNTTYPID)

GROUP BY IMSQD.client_id, IMSQD.option_id, IMSQD.revi_lumpsum, IMSQD.coverage_id, CVGS.description

ORDER BY IMSQD.client_id

--Query to return benefit-type FOR member </frame>

exec dbo.procGenerateIMSResult @policyID </frame>

EXEC@return_value = [dbo].[procGenerateIMS_Msg]

@policyID = 6312,

@userType = N'B',

@loginID = N'B',

@isSampleIMS = True,

@ReturnCode = @ReturnCode OUTPUT,

@BusErrorMsg = @BusErrorMsg OUTPUT,

@TecErrorMsg = @TecErrorMsg OUTPUT </frame>

</executionStack>

DECLARE@return_value int,

@ReturnCode varchar(20),

@BusErrorMsg varchar(500),

@TecErrorMsg varchar(4000)

EXEC@return_value = [dbo].[procGenerateIMS_Msg]

@policyID = 6312,

@userType = N'B',

@loginID = N'B',

@isSampleIMS = True,

@ReturnCode = @ReturnCode OUTPUT,

@BusErrorMsg = @BusErrorMsg OUTPUT,

@TecErrorMsg = @TecErrorMsg OUTPUT

SELECT@ReturnCode as N'@ReturnCode',

@BusErrorMsg as N'@BusErrorMsg',

@TecErrorMsg as N'@TecErrorMsg'

SELECT'Return Value' = @return_value

</inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

I don't know how to interpret what you have, I use the blocker checker provided MS http://support.microsoft.com/kb/271509 and then analyze using sherlock as described in http://blogs.msdn.com/debuggingtoolbox/archive/2007/07/09/sherlock-tool-for-graphical-blocker-script-analysis-sql-server-2000-and-7-0.aspx

I know this doesn't help you current situation, but I have found this the easiest way to track blocking.

Andrew

tnbarkhouse Valued Member Points: 69 More actions · Answer 3

Anjali-

For instruction on troubleshooting deadlocks, I highly recommend blog posts from Bart Duncan (starting with http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx) and Jonathan Kehayias (http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx and http://jmkehayias.blogspot.com/search/label/Deadlocking).

In this case, the point of contention is the [testbroker].[dbo].[IMS_Quote_Details] table (specifically the [PK_IMS_Quote_Details_1] index). The [testbroker].[dbo].[procGenerateIMSResult] stored procedure was called from two processes, SPID 69 and SPID 84, and they are conflicting with each other. Not having access to the system, I can't answer your question conclusively, but here are some observations and suggestions:

1. Running the stored procedure through the Database Engine Tuning Advisor (http://msdn.microsoft.com/en-us/library/ms173494(SQL.90).aspx) may result in index suggestions that improve or resolve the problem

2. It appears that the stored procedure from the first process (SPID 69) is running from a .NET application (".Net SqlClient Data Provider" from the PDCDT078 machine) while the second process (SPID 84) is running from SQL Server Management Studio (on the HP1206D-217 machine).

a. If the routine is not designed to run more than once concurrently, then care should be taken not to overlap

b. Also, it is concerning that SSMS is being used in a production system for non-administrative activity

3. Both executions are acting upon the same logical information (Policy ID 6312, User Type "B", Login ID "B")

4. It appears that the process is pretty long running/complex*. It might help to break it up into smaller units of work that are more easily tuned

5. The query in the first frame of each execution stack could probably be optimized to a great degree. Without knowing the schema, I can't give more specific suggestions, but the following aspects seem odd to me:

a. I don't see the purpose of the GROUP BY clause

b. SELECT DISTINCT is used in both the inner and outer queries

As for preventing deadlocks, I suggest reading "Minimizing Deadlocks" http://msdn.microsoft.com/en-us/library/ms191242(SQL.90).aspx.

I hope this helps,

Trevor

* Based on the following attributes of the "process" nodes:

"lastbatchcompleted"

"lastbatchstarted"

"lasttranstarted"

"logused"

"waittime"

And the attributes of the "frame" nodes:

"line"

"stmtend"

"stmtstart"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

check this out

http://www.sqlservercentral.com/articles/deadlock/64315/

"Keep Trying"

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

anjali.vishwakarma (1/23/2009)
I am attaching deadlock graph and deadlock xml. How to analyse it and what'll be the solution to avoid deadlock in future ?[/img]

Anjali, please search through this forum before posting. You can find many forum posts by me and others, and articles regarding the same.

Folks who replied has a very good resource.