title: Deadlock between delete and update statement in SQL Server 2012
body: <p>I'm facing a problem in a database between 2 stored procedures, one trying to update and the other wants to delete some huge amount of data.</p>
  
  <p>My <code>Offer</code> table contains 50 million rows (I know not a good practice, I'm purging the data).</p>
  
  <p>The table contains an incremental ID (not offer_id) which is the primary key with a clustered index on it.</p>
  
  <p>xml_deadlock_report :</p>
  
  <pre><code>  &lt;event name="xml_deadlock_report" package="sqlserver" timestamp="2018-01-02T00:56:16.360Z"&gt;
      &lt;data name="xml_report"&gt;
          &lt;type name="xml" package="package0" /&gt;
          &lt;value&gt;
              &lt;deadlock&gt;
                  &lt;victim-list&gt;
                      &lt;victimProcess id="process3697498" /&gt;
                  &lt;/victim-list&gt;
                  &lt;process-list&gt;
                      &lt;process id="process3697498" taskpriority="0" logused="127362200" waitresource="PAGE: 9:1:99977592 " waittime="6212" ownerId="32514985656" transactionname="DELETE" lasttranstarted="2018-01-02T01:55:56.853" XDES="0x902e53ed28" lockMode="IX" schedulerid="5" kpid="10104" status="suspended" spid="155" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-02T00:00:02.427" lastbatchcompleted="2018-01-02T00:00:02.427" lastattention="1900-01-01T00:00:00.427" clientapp="SQLAgent - TSQL JobStep (Job 0xAF5CC7B9127279438B52607063734954 : Step 1)" hostname="HB01-BOSQL-CL02" hostpid="5008" loginname="RUEDUCOMMERCE\hicham.boutaleb" isolationlevel="read committed (2)" xactid="32514985656" currentdb="9" lockTimeout="4294967295" clientoption1="671088928" clientoption2="128056"&gt;
                          &lt;executionStack&gt;
                              &lt;frame procname="EchangesDb.dbo.PurgeGM2" line="433" stmtstart="57518" stmtend="57750" sqlhandle="0x03000900c9c6d33469f20e0158a8000001000000000000000000000000000000000000000000000000000000"&gt;
  DELETE 
                  TOP (100000)
                  OFFER
              WHERE
                  ID IN (SELECT ID FROM #OFFERSTODELETE)
               OPTION(MAXDOP 1)    
                              &lt;/frame&gt;
                              &lt;frame procname="adhoc" line="1" sqlhandle="0x010009008b23cd0690ffbc006500000000000000000000000000000000000000000000000000000000000000"&gt;
  Exec PurgeGM2    &lt;/frame&gt;
                          &lt;/executionStack&gt;
                          &lt;inputbuf&gt;
  Exec PurgeGM2   &lt;/inputbuf&gt;
                      &lt;/process&gt;
                      &lt;process id="process5b230c8" taskpriority="0" logused="183626528" waitresource="PAGE: 9:1:99056248 " waittime="2010" ownerId="32514934129" transactionname="user_transaction" lasttranstarted="2018-01-02T01:55:46.243" XDES="0xd0e49f16c0" lockMode="U" schedulerid="15" kpid="10684" status="suspended" spid="128" sbid="0" ecid="22" priority="0" trancount="0" lastbatchstarted="2018-01-02T01:55:46.240" lastbatchcompleted="2018-01-02T01:55:46.240" lastattention="1900-01-01T00:00:00.240" clientapp=".Net SqlClient Data Provider" hostname="HB01-BIZTALK01" hostpid="2620" isolationlevel="read committed (2)" xactid="32514934129" currentdb="9" lockTimeout="4294967295" clientoption1="673317152" clientoption2="128056"&gt;
                          &lt;executionStack&gt;
                              &lt;frame procname="EchangesDb.dbo.offer_insert_diff" line="183" stmtstart="8450" stmtend="8780" sqlhandle="0x0300090048642c329947700146a8000001000000000000000000000000000000000000000000000000000000"&gt;
  UPDATE o
              SET tc_process_status = 0
              FROM [dbo].[offer] AS o WITH(NOLOCK)
              INNER JOIN Temp_OffersToMove AS t WITH(NOLOCK) ON (o.offer_id = t.offer_id)    &lt;/frame&gt;
                          &lt;/executionStack&gt;
                          &lt;inputbuf&gt;
  Proc [Database Id = 9 Object Id = 841770056]   
                          &lt;/inputbuf&gt;
                      &lt;/process&gt;
                  &lt;/process-list&gt;
                  &lt;resource-list&gt;
                      &lt;pagelock fileid="1" pageid="99977592" dbid="9" subresource="FULL" objectname="EchangesDb.dbo.offer" id="lock3d70957380" mode="U" associatedObjectId="72057595568062464"&gt;
                          &lt;owner-list&gt;
                              &lt;owner id="process5b230c8" mode="U" /&gt;
                          &lt;/owner-list&gt;
                          &lt;waiter-list&gt;
                              &lt;waiter id="process3697498" mode="IX" requestType="wait" /&gt;
                          &lt;/waiter-list&gt;
                      &lt;/pagelock&gt;
                      &lt;pagelock fileid="1" pageid="99056248" dbid="9" subresource="FULL" objectname="EchangesDb.dbo.offer" id="lock6d7712ab80" mode="IX" associatedObjectId="72057595568062464"&gt;
                          &lt;owner-list&gt;
                              &lt;owner id="process3697498" mode="IX" /&gt;
                          &lt;/owner-list&gt;
                          &lt;waiter-list&gt;
                              &lt;waiter id="process5b230c8" mode="U" requestType="wait" /&gt;
                          &lt;/waiter-list&gt;
                      &lt;/pagelock&gt;
                  &lt;/resource-list&gt;
              &lt;/deadlock&gt;
          &lt;/value&gt;
      &lt;/data&gt;
  &lt;/event&gt;
  </code></pre>