1

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock.

I am still not sure how come Select Statement acquires exclusive lock!!

enter image description here

DeadLock XML ::

<deadlock>
  <victim-list>
    <victimProcess id="processcfb7c48108" />
  </victim-list>
  <process-list>
    <process id="processcfb7c48108" taskpriority="0" logused="9916" waitresource="KEY: 8:72057622872653824 (a18bb75a63b4)" waittime="2255" ownerId="34534941558" transactionname="user_transaction" lasttranstarted="2018-11-23T20:33:11.730" XDES="0x105204209f0" lockMode="S" schedulerid="22" kpid="11192" status="suspended" spid="145" sbid="4" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-11-23T20:33:11.730" lastbatchcompleted="2018-11-23T20:33:11.727" lastattention="1900-01-01T00:00:00.727" clientapp=".Net SqlClient Data Provider" hostname="Host1" hostpid="8916" loginname="User1" isolationlevel="read committed (2)" xactid="34534941558" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname=".dbo.UpdateBatch" line="39" stmtstart="3958" stmtend="4692" sqlhandle="0x030008002e480353103ba4008fa4000001000000000000000000000000000000000000000000000000000000" />
        <frame procname="dbo.ProcessRequest" line="1110" stmtstart="129114" stmtend="129204" sqlhandle="0x03000800a5eed57fbf40180090a9000001000000000000000000000000000000000000000000000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process160dec204e8" taskpriority="0" logused="11924" waitresource="KEY: 8:72057622872653824 (09d0c04e1297)" waittime="977" ownerId="34534939320" transactionname="user_transaction" lasttranstarted="2018-11-23T20:33:11.640" XDES="0x5d8982a3b0" lockMode="S" schedulerid="3" kpid="16588" status="suspended" spid="355" sbid="4" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-11-23T20:33:11.640" lastbatchcompleted="2018-11-23T20:33:11.640" lastattention="1900-01-01T00:00:00.640" clientapp=".Net SqlClient Data Provider" hostname="Host1" hostpid="8828" loginname="User1" isolationlevel="read committed (2)" xactid="34534939320" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname=".dbo.UpdateBatch" line="39" stmtstart="3958" stmtend="4692" sqlhandle="0x030008002e480353103ba4008fa4000001000000000000000000000000000000000000000000000000000000" />
        <frame procname="dbo.ProcessRequest" line="1110" stmtstart="129114" stmtend="129204" sqlhandle="0x03000800a5eed57fbf40180090a9000001000000000000000000000000000000000000000000000000000000" />
      </executionStack>
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057622872653824" dbid="8" objectname="dbo.Batch" indexname="IX_Batch" id="lock84224bdc80" mode="X" associatedObjectId="72057622872653824">
      <owner-list>
        <owner id="process160dec204e8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processcfb7c48108" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057622872653824" dbid="8" objectname="dbo.Batch" indexname="IX_Batch" id="lock1328943c100" mode="X" associatedObjectId="72057622872653824">
      <owner-list>
        <owner id="processcfb7c48108" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process160dec204e8" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Deadlock Graph for the Reference

enter image description here

Additional Code Block

IF (SELECT COUNT(1) FROM (
    IF (SELECT COUNT(1) FROM (
    SELECT b.Shop FROM [Batch] b
        INNER JOIN [Process] o ON o.procNo = b.procNo           
        WHERE b.Ship = N'P'             
    ) B)
    UPDATE b4 SET StringVal3 = (
            SELECT TOP 1 Shop FROM (
                        SELECT COUNT(1) FROM (
                        SELECT b.Shop FROM [Batch] b
                            INNER JOIN [Process] o ON o.procNo = b.procNo           
                            WHERE b.Ship = N'P' 
                ) B)
        FROM [Batch] b4 WHERE b4.Ship = N'P' AND b4.Unique = 0 AND b4.Flag = 4
  • Why don't you include the complete deadlock graph as xml here? We want to see also the RESOURCES section of the graph, not only PROCESSES – sepupic Nov 26 '18 at 11:05
  • this is the XML i have . I am not sure what exactly you are looking for ! – Parthi Nov 26 '18 at 11:08
  • You need to include the actual XML - not a screenshot. – George.Palacios Nov 26 '18 at 11:10
  • Can you post it please as a text and not as a picture? And the complete text of your proc also, we cannot see any modification in your code, but it is presented – sepupic Nov 26 '18 at 11:12
  • Screenshot has the whole xml content! – Parthi Nov 26 '18 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.