|
|
|
Valued Member
 
Group: General Forum Members
Last Login: Friday, September 16, 2011 3:37 AM
Points: 54,
Visits: 148
|
|
I am attaching deadlock graph and deadlock xml. How to analyse it and what'll be the solution to avoid deadlock in future ? [/img]
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Friday, September 16, 2011 3:37 AM
Points: 54,
Visits: 148
|
|
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>
<deadlock victim="process638d48">
<process-list>
<process id="process638d48" taskpriority="0" logused="431668" waitresource="KEY: 6:72057594145472512 (2b012878be62)" waittime="1876609" ownerId="2323392" transactionname="IMSQuote" lasttranstarted="2009-01-23T05:23:27.140" XDES="0x73e7d140" lockMode="S" schedulerid="1" kpid="2028" status="suspended" spid="69" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-01-23T05:23:26.497" lastbatchcompleted="2009-01-23T05:23:26.467" clientapp=".Net SqlClient Data Provider" hostname="PDCDT078" hostpid="5952" loginname="sa" isolationlevel="read committed (2)" xactid="2323392" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="testbroker.dbo.procGenerateIMSResult" line="74" stmtstart="4962" stmtend="6318" sqlhandle="0x030006007de49f231c9457009a9b00000100000000000000">
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[s] </frame>
<frame procname="testbroker.dbo.procGenerateIMS" line="1675" stmtstart="145708" stmtend="145798" sqlhandle="0x030006003b83904923d158009a9b00000100000000000000">
exec dbo.procGenerateIMSResult @policyID </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1234207547] </inputbuf>
</process>
<process id="process6ad3d8" taskpriority="0" logused="482300" waitresource="KEY: 6:72057594145472512 (a201e4ec37d0)" waittime="4187" ownerId="2407706" transactionname="IMSQuote" lasttranstarted="2009-01-23T05:37:54.560" XDES="0x74302258" lockMode="S" schedulerid="2" kpid="2232" status="suspended" spid="84" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-23T06:18:44.200" lastbatchcompleted="2009-01-23T06:18:44.200" lastattention="2009-01-23T05:48:22.657" clientapp="Microsoft SQL Server Management Studio - Query" hostname="HP1206D-217" hostpid="372" loginname="sa" isolationlevel="read committed (2)" xactid="2407706" currentdb="6" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
<executionStack>
<frame procname="testbroker.dbo.procGenerateIMSResult" line="74" stmtstart="4962" stmtend="6318" sqlhandle="0x030006007de49f231c9457009a9b00000100000000000000">
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[s] </frame>
<frame procname="testbroker.dbo.procGenerateIMS_Msg" line="1886" stmtstart="170930" stmtend="171020" sqlhandle="0x030006006b31457d95d8c000999b00000100000000000000">
exec dbo.procGenerateIMSResult @policyID </frame>
<frame procname="adhoc" line="7" stmtstart="240" stmtend="742" sqlhandle="0x01000600b2e9553658d9a934000000000000000000000000">
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>
<inputbuf>
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>
<keylock hobtid="72057594145472512" dbid="6" objectname="testbroker.dbo.IMS_Quote_Details" indexname="PK_IMS_Quote_Details_1" id="lock213d6d00" mode="X" associatedObjectId="72057594145472512">
<owner-list>
<owner id="process6ad3d8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process638d48" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594145472512" dbid="6" objectname="testbroker.dbo.IMS_Quote_Details" indexname="PK_IMS_Quote_Details_1" id="lock3a74edc0" mode="X" associatedObjectId="72057594145472512">
<owner-list>
<owner id="process638d48" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process6ad3d8" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 3:50 PM
Points: 412,
Visits: 2,401
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, December 11, 2011 2:54 PM
Points: 1,
Visits: 169
|
|
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"
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Monday, January 30, 2012 2:55 AM
Points: 2,363,
Visits: 1,817
|
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, February 07, 2012 9:58 AM
Points: 1,097,
Visits: 2,137
|
|
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.
|
|
|
|
