March 19, 2019 at 1:54 am
Getting 100+ deadlock by same process and on sp_executesql. Can experts shed some light on why this is happening.
<deadlock>
<victim-list />
<process-list>
<process id="process56579c4e8" taskpriority="0" logused="10000" waittime="57" schedulerid="2" kpid="12720" status="suspended" spid="213" sbid="0" ecid="37" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
<process id="process4943c28" taskpriority="0" logused="10000" waittime="54" schedulerid="5" kpid="14832" status="suspended" spid="213" sbid="0" ecid="31" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
<process id="process4064108" taskpriority="0" logused="10000" waittime="51" schedulerid="5" kpid="10052" status="suspended" spid="213" sbid="0" ecid="28" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
<process id="process7c2eae108" taskpriority="0" logused="10000" waittime="50" schedulerid="6" kpid="15388" status="suspended" spid="213" sbid="0" ecid="30" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
<process id="process3f0d0fc28" taskpriority="0" logused="10000" waittime="51" schedulerid="3" kpid="208" status="suspended" spid="213" sbid="0" ecid="35" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
<process id="process3925f5088" taskpriority="0" logused="10000" waittime="141" schedulerid="6" kpid="9700" status="suspended" spid="213" sbid="0" ecid="34" priority="0" trancount="0" lastbatchstarted="2019-03-19T11:33:27.570" lastbatchcompleted="2019-03-19T11:33:27.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="APPSERVER003-COM" hostpid="39584" isolationlevel="read committed (2)" xactid="19150243569" currentdb="14" currentdbname="SUBSCRIPTION" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="26944" sqlhandle="0x0200000032a14f2a055ce67c155f9c7eeeed87071a214c200000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="TravelAidABC.RetrieveList" line="297" stmtstart="42350" sqlhandle="0x03000e007b00102f72ea740007aa000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql @FullStatement </frame>
<frame procname="adhoc" line="1" stmtstart="100" sqlhandle="0x01000e006de07e00501e87030a00000000000000000000000000000000000000000000000000000000000000">
ABC.RetrieveList @UserName, @WhereClause </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@UserName nvarchar(14),@WhereClause nvarchar(31))ABC.RetrieveList @UserName, @WhereClause </inputbuf>
</process>
</process-list>
<resource-list>
<exchangeEvent id="Pipe562b07040" WaitType="e_waitPipeNewRow" nodeId="47">
<owner-list>
<owner id="process3925f5088" />
</owner-list>
<waiter-list>
<waiter id="process56579c4e8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipeda4eaa210" WaitType="e_waitPipeNewRow" nodeId="77">
<owner-list>
<owner id="process56579c4e8" />
</owner-list>
<waiter-list>
<waiter id="process4943c28" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipec83d77240" WaitType="e_waitPipeNewRow" nodeId="87">
<owner-list>
<owner id="process4943c28" />
</owner-list>
<waiter-list>
<waiter id="process4064108" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe19be79b40" WaitType="e_waitPipeGetRow" nodeId="87">
<owner-list>
<owner id="process4064108" />
</owner-list>
<waiter-list>
<waiter id="process7c2eae108" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe3c31a6d70" WaitType="e_waitPipeGetRow" nodeId="77">
<owner-list>
<owner id="process7c2eae108" />
</owner-list>
<waiter-list>
<waiter id="process3f0d0fc28" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe326a89f80" WaitType="e_waitPipeGetRow" nodeId="47">
<owner-list>
<owner id="process3f0d0fc28" />
</owner-list>
<waiter-list>
<waiter id="process3925f5088" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
March 19, 2019 at 2:10 am
March 31, 2019 at 7:53 am
Can anyone help me on this? Getting 300+ of the same ..How can I avoid this?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply