title: Deadlock between Delete and Update
body: <p>First process:</p>
  
  <pre><code>&lt;deadlock-list&gt;
   &lt;deadlock victim="process8d9798"&gt;
    &lt;process-list&gt;
     &lt;process id="process8d9798" taskpriority="0" logused="0" waitresource="PAGE:
      5:1:190354" waittime="3203" ownerId="53807810" transactionname="DELETE" 
      lasttranstarted="11:29:29.153" XDES="0x3dbb518" lockMode="U" 
      schedulerid="2" kpid="1792" status="suspended" spid="57" sbid="0" ecid="1" 
      priority="0" transcount="0" lastbatchstarted="2012-09-28T11:29:29.120" 
      lastbatchcompleted="11:29:29.120" clientapp=".Net SqlClient Data Provider" 
      hostname="xxx" hostpid="4460" isolationlevel="read uncommitted (1)" 
      xactid="53807810" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" 
      clientoption2="128056"&gt;
      &lt;executionStack&gt;
       &lt;frame procname="Chooser2.dbo.DeleteUserSelections" line="15" stmtstart="360"
       stmtend="464" sqlhandle="0x030005008839117bf599a500099800000100000000000000"&gt;
     DELETE UserPlanOption
      WHERE UserID = @userId     &lt;/frame&gt;
      &lt;/executionStack&gt;
      &lt;inputbuf&gt;
      &lt;/inputbuf&gt;
     &lt;/process&gt;
  </code></pre>
  
  <p>Second process:</p>
  
  <pre><code>   &lt;process id="processb84988" taskpriority="0" logused="1744" waitresource="PAGE:
      5:1:190487" waittime="3203" ownerId="53807415" transactionname="user_transaction" 
     lasttranstarted="11:29:13.513" XDES="0x2fc4e6e0" lockMode="IU" 
     schedulerid="4" kpid="4628" status="suspended" spid="52" sbid="0" ecid="0" 
     priority="0" transcount="2" lastbatchstarted="11:29:13.513" 
     lastbatchcompleted="11:29:13.513" 
     clientapp=".Net SqlClient Data Provider" hostname="xxx" 
     hostpid="4460" loginname="chooserpd" isolationlevel="read uncommitted (1)" 
     xactid="53807415" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" 
     clientoption2="128056"&gt;
      &lt;executionStack&gt;
       &lt;frame procname="Eligibility" line="149" stmtstart="10566" 
     stmtend="11604" sqlhandle="0x03000500171b4f52c1a6e200ada000000100000000000000"&gt;
  UPDATE  UserPlanOption 
      SET     RateID = r.ID
      FROM    [User] u WITH (NOLOCK)
          LEFT JOIN Rate r ON r.FamilyTierID = u.FamilyTierID 
      WHERE   UserPlanOption.PlanOptionID NOT IN (SELECT ppo.PlanOptionID FROM 
                      @PORACPlanOptions ppo) AND
          u.ID = @userID AND u.ID = UserPlanOption.UserID AND
          r.PlanOptionID = UserPlanOption.PlanOptionID AND
          r.Criterion1 = dbo.GetPlanOptionAreaID_36()
        &lt;/frame&gt;
      &lt;/executionStack&gt;
      &lt;inputbuf&gt;
  Proc [Database Id = 5 Object Id = 1380915991]    &lt;/inputbuf&gt;
     &lt;/process&gt;
    &lt;/process-list&gt;
  </code></pre>
  
  <p>Resource list:</p>
  
  <pre><code>  &lt;resource-list&gt;
     &lt;pagelock fileid="1" pageid="190354" dbid="5" objectname="UserPlanOption" 
     id="lock1e482d80" mode="IX" associatedObjectId="72057594060996608"&gt;
      &lt;owner-list&gt;
       &lt;owner id="processb84988" mode="IX"/&gt;
      &lt;/owner-list&gt;
      &lt;waiter-list&gt;
       &lt;waiter id="process8d9798" mode="U" requestType="wait"/&gt;
      &lt;/waiter-list&gt;
     &lt;/pagelock&gt;
     &lt;pagelock fileid="1" pageid="190487" dbid="5" objectname="UserPlanOption" 
     id="lock25b32a80" mode="U" associatedObjectId="72057594060996608"&gt;
      &lt;owner-list&gt;
       &lt;owner id="process8d9798" mode="U"/&gt;
      &lt;/owner-list&gt;
      &lt;waiter-list&gt;
       &lt;waiter id="processb84988" mode="IU" requestType="wait"/&gt;
      &lt;/waiter-list&gt;
     &lt;/pagelock&gt;
    &lt;/resource-list&gt;
   &lt;/deadlock&gt;
  &lt;/deadlock-list&gt;
  </code></pre>
  
  <p>The table "UserPlanOption has a composite PK (UserId and PlanOptionId). Why does the delete cause a page lock? Can someone help me understand what is going on? What confused me for a while was that I thought the deadlock would be caused by subsequent queries from the same client, but that's not possible. These must be different clients hitting the same web page.</p>
  
  <p>Actually, I guess I know the answer to the first question - deleting a range will require a page lock. But how can I get around this?</p>
  
  <p>Results from index query:</p>
  
  <pre><code>name                type type_desc is_unique data_space_id ignore_dup_key 
  ------------------- ---- --------- --------- ------------- -------------- 
  PK_UserPlanOption_1 1    CLUSTERED 1         1             0              
  
  
  is_primary_key is_unique_constraint fill_factor is_padded is_disabled 
  -------------- -------------------- ----------- --------- ----------- 
  1              0                    0           0         0           
  
  is_hypothetical allow_row_locks allow_page_locks
  --------------- --------------- ----------------
  0               1               1
  </code></pre>
  
  <p>Execution plans for the <a href="http://heeroz.com/update.sqlplan" rel="nofollow">update</a> and the <a href="http://heeroz.com/delete.sqlplan" rel="nofollow">delete</a>.</p>