DeadLock its own Process

  • 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>

  • 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