Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Deadlock - Exchange Event

Author  Topic 

deds99
Starting Member

1 Post
Posted - 2009-01-21 : 04:11:05
Hi,

I am facing a deadlock issue for "Exchange Event" for a stored procedure that is run n multiple threads.

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

This is how the deadlock graph looks like... Can someone please help me figure out where the problem is and how to resolve it?

<deadlock-list>
<deadlock victim="process949f18">
<process-list>
<process id="process8bb798" taskpriority="0" logused="10003" waittime="6125" schedulerid="1" kpid="4236" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-21T09:53:53.107" lastbatchcompleted="2009-01-21T09:53:52.873" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="1360" loginname="EDTCOREDOM\Administrator" isolationlevel="read committed (2)" xactid="438286" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1915153868]
</inputbuf>
</process>
<process id="process8bbd38" taskpriority="0" logused="20010" waittime="3234" schedulerid="1" kpid="4272" status="suspended" spid="59" sbid="0" ecid="1" priority="0" transcount="0" lastbatchstarted="2009-01-21T09:53:53.107" lastbatchcompleted="2009-01-21T09:53:52.873" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="1360" isolationlevel="read committed (2)" xactid="438286" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process8bbe28" taskpriority="0" logused="20006" waittime="3234" schedulerid="1" kpid="5116" status="suspended" spid="59" sbid="0" ecid="5" priority="0" transcount="0" lastbatchstarted="2009-01-21T09:53:53.107" lastbatchcompleted="2009-01-21T09:53:52.873" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="1360" isolationlevel="read committed (2)" xactid="438286" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process948898" taskpriority="0" logused="0" waitresource="PAGE: 7:1:44135" waittime="3875" ownerId="438286" transactionname="UPDATE" lasttranstarted="2009-01-21T09:53:56.060" XDES="0x1e40a040" lockMode="U" schedulerid="2" kpid="2316" status="suspended" spid="59" sbid="0" ecid="4" priority="0" transcount="0" lastbatchstarted="2009-01-21T09:53:53.107" lastbatchcompleted="2009-01-21T09:53:52.873" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="1360" isolationlevel="read committed (2)" xactid="438286" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process948d48" taskpriority="0" logused="20010" waittime="3234" schedulerid="2" kpid="5620" status="suspended" spid="59" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2009-01-21T09:53:53.107" lastbatchcompleted="2009-01-21T09:53:52.873" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="1360" isolationlevel="read committed (2)" xactid="438286" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process949f18" taskpriority="0" logused="0" waitresource="PAGE: 7:1:44135" waittime="4000" ownerId="438492" transactionname="UPDATE" lasttranstarted="2009-01-21T09:53:58.233" XDES="0x1dd11c18" lockMode="U" schedulerid="2" kpid="1592" status="suspended" spid="62" sbid="0" ecid="3" priority="0" transcount="0" lastbatchstarted="2009-01-21T09:53:56.077" lastbatchcompleted="2009-01-21T09:53:55.920" clientapp=".Net SqlClient Data Provider" hostname="SDW2K3ENT" hostpid="3272" isolationlevel="read committed (2)" xactid="438492" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="CSM_Reports.dbo.symc_HDB_GetDetailedAnalysisData" line="55" stmtstart="3672" stmtend="4830" sqlhandle="0x03000700cced26725f240c01969b00000100000000000000">
UPDATE HDB_SMDetailedAnalysis
SET CheckVersion = FACT_Table.CheckVersion,
CIAScore = FACT_Table.ciascore,
outcome = FACT_Table.check_outcome,
Exempt = Fact_Table.Exempt
FROM FACT_Table INNER JOIN HDB_SMDetailedAnalysis ON
FACT_Table.AssetID = HDB_SMDetailedAnalysis.AssetID AND
FACT_Table.CheckID = HDB_SMDetailedAnalysis.CheckID
WHERE
FACT_Table.standardid = @StandardID AND
FACT_Table.standardversion = @StandardVersion AND
HDB_SMDetailedAnalysis.DBID = @DBID AND
HDB_SMDetailedAnalysis.DBNodeID = @DBNodeID AND
FACT_TABLE.CURRENTROW =1
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="44135" dbid="7" objectname="CSM_Reports.dbo.HDB_SMDetailedAnalysis" id="lock6eae940" mode="U" associatedObjectId="72057594055688192">
<owner-list>
<owner id="process8bb798" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process949f18" mode="U" requestType="wait"/>
<waiter id="process948898" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<exchangeEvent id="port3ccc680" nodeId="10">
<owner-list>
<owner event="e_waitNone" type="producer" id="process948898"/>
</owner-list>
<waiter-list>
<waiter event="e_waitPipeGetRow" type="consumer" id="process8bbd38"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="process948d48"/>
</waiter-list>
</exchangeEvent>
<exchangeEvent id="port3cccb80" nodeId="6">
<owner-list>
<owner event="e_waitNone" type="producer" id="process8bbd38"/>
<owner event="e_waitNone" type="producer" id="process948d48"/>
</owner-list>
<waiter-list>
<waiter event="e_waitPipeGetRow" type="consumer" id="process8bbe28"/>
</waiter-list>
</exchangeEvent>
<exchangeEvent id="port3ccc480" nodeId="3">
<owner-list>
<owner event="e_waitNone" type="producer" id="process8bbe28"/>
</owner-list>
<waiter-list>
<waiter event="e_waitPortOpen" type="consumer" id="process8bb798"/>
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
</deadlock-list>

robossliu
Starting Member

1 Post
Posted - 2009-09-10 : 02:42:48
I came across the deadlock problem in SQL Server 2005. Although I couldn't give you the solution, I find this post [url]http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx[/url] is very helpful. And my problem is resolved by using the method described in the post. I strongly recommend you read the post and find the solution yourself. If you still have problem, don't bother emailing to me.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site