2

I have the following deadlock xml

<deadlock>
  <victim-list>
    <victimProcess id="process9b7ade24e8" />
  </victim-list>
  <process-list>
    <process id="process9b7ade24e8" taskpriority="0" logused="3740" waitresource="PAGE: 5:1:95428 " waittime="2694" ownerId="19062098" transactionname="user_transaction" lasttranstarted="2018-05-29T16:08:08.210" XDES="0x9ba3d19a80" lockMode="U" schedulerid="1" kpid="22848" status="suspended" spid="234" sbid="2" ecid="5" priority="0" trancount="0" lastbatchstarted="2018-05-29T16:08:08.233" lastbatchcompleted="2018-05-29T16:08:08.227" lastattention="1900-01-01T00:00:00.227" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" isolationlevel="read committed (2)" xactid="19062098" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue_DeleteByAttributeValueIds" queryhash="0xcca77ed1e85d78e6" queryplanhash="0x37c02f878b0885e4" line="16" stmtstart="864" stmtend="1138" sqlhandle="0x030005006cae724f418fff00efa8000001000000000000000000000000000000000000000000000000000000">
DELETE
        AV
    FROM
        [model].[AttributeValue] AV
    INNER JOIN
        @AttributeValueIds AVT
    ON
        [AVT].EntityId = [AV].[AttributeValueId    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="13" stmtstart="732" stmtend="918" sqlhandle="0x03000500def65a51498fff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC model.AttributeValue_DeleteByAttributeValueIds
        @AttributeValueIds = @AttributeValueId    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.ModelItem_Generic_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="41" stmtstart="2062" stmtend="2208" sqlhandle="0x0300050096f1cb436e91ff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValue_DeleteByModelItemIds]      
            @ModelItemIdTabl    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.ModelItem_Relationship_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="23" stmtstart="1078" stmtend="1302" sqlhandle="0x030005000d989e706992ff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Generic_Delete]
        @RelationshipIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1889441805]   </inputbuf>
    </process>
    <process id="process9b79fb24e8" taskpriority="0" logused="30476" waitresource="PAGE: 5:1:95427 " waittime="3651" ownerId="19171385" transactionname="user_transaction" lasttranstarted="2018-05-29T16:08:24.113" XDES="0x9b6d88c458" lockMode="IX" schedulerid="1" kpid="91748" status="suspended" spid="195" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-29T16:08:24.680" lastbatchcompleted="2018-05-29T16:08:24.680" lastattention="2018-05-29T15:55:19.547" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF430FC8" hostpid="12344" loginname="officearchitect" isolationlevel="read committed (2)" xactid="19171385" currentdb="5" currentdbname="OfficeArchitect_Performance_Test" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue_DeleteByAttributeValueIds" queryhash="0xcca77ed1e85d78e6" queryplanhash="0x37c02f878b0885e4" line="16" stmtstart="864" stmtend="1138" sqlhandle="0x030005006cae724f418fff00efa8000001000000000000000000000000000000000000000000000000000000">
DELETE
        AV
    FROM
        [model].[AttributeValue] AV
    INNER JOIN
        @AttributeValueIds AVT
    ON
        [AVT].EntityId = [AV].[AttributeValueId    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue_DeleteByModelItemIds" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="13" stmtstart="732" stmtend="918" sqlhandle="0x03000500def65a51498fff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC model.AttributeValue_DeleteByAttributeValueIds
        @AttributeValueIds = @AttributeValueId    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.ModelItem_Generic_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="41" stmtstart="2062" stmtend="2208" sqlhandle="0x0300050096f1cb436e91ff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[AttributeValue_DeleteByModelItemIds]      
            @ModelItemIdTabl    </frame>
        <frame procname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.ModelItem_Object_Delete" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="25" stmtstart="1088" stmtend="1302" sqlhandle="0x0300050061e52c651c92ff00efa8000001000000000000000000000000000000000000000000000000000000">
EXEC [model].[ModelItem_Generic_Delete] 
        @ObjectIdTable,
        @MarkAsDeleted,
        @DeletedBy, 
        @DeletedO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1697441121]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="95428" dbid="5" subresource="FULL" objectname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue" id="lock9bc906e400" mode="IX" associatedObjectId="72057594082557952">
      <owner-list>
        <owner id="process9b79fb24e8" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process9b7ade24e8" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="95427" dbid="5" subresource="FULL" objectname="818c56f2-1b25-4d98-b19d-cf2cafbf450d.model.AttributeValue" id="lock9bc9511900" mode="U" associatedObjectId="72057594082557952">
      <owner-list>
        <owner id="process9b7ade24e8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process9b79fb24e8" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

A simple and classic example of a deadlock. Now, from the excellent and detailed answer to my previous question by Paul White (here), I think I have a better understanding (stressing I think!) what is going on.

Firstly this is a very simple delete statement on the model.AttributeValue table using a table-valued parameter to delete multiple rows at once.

So, we have a page-level lock on the table (or more precisely the clustered index on the table). The first process IX has an an exclusive lock on the table and also wants a U lock. The other, deadlocked process, already has a U lock but wants an IX lock. The U lock is needed because rows now need to be read from the table, and since my database is using read-committed snapshot isolation (RCSI) level, this is what the database engine does.

Now, here is the execution plan: execution plan

Questions:

  1. Is the U lock needed because the delete statement first needs to find (read) the rows from the AttributeValue table, and join with the TVP (the first nested loop)?
  2. Why are the IX and U locks not taken in order? I.e. why does one process acquire a U lock first and then an IX and the other process the other way around? I would expect the U lock to be needed first, then the IX lock when deleting from the clustered index.
  3. This is a page level lock, many rows could fall under that page. I am still not sure as to how to prevent the deadlock here. Paul in his answer above mentioned that rows in each set are processed in strictly the same order (sorted the same way, and processed in the same sequence by the execution plan). I'm a bit unsure as to how this needs to be enforced. Or if it applies here.
  4. Are some deadlocks just unavoidable? Is this where something like a TABLOCK needs to be employed? I realise this hint naturally results in an performance hit. Under what (general) circumstances can I ascertain that I need a TABLOCK hint? Also, when is the TABLOCK released? At the end of the statement or the transaction?
  5. Are TVP partly to blame here? The fact that we're using TVP means the optimizer cannot know how many rows to delete (could be 1, could be 100). A more simpler delete statement could just use row-level locks?

One of the issues we have with our system is long-running transactions. Some of our transactions are quite large and due to the nature of the system (and to keep data integrity) we need to run a few sp's within a transaction to ensure this. The delete statement above is an example of a transaction with a lot of calls to other sp's to restructure the data during the delete.

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.