title: DeadLock in Update Statement and Usage of UPDLOCK Hint
body: <p>For the following transaction occasionally I am receiving Dead Lock error when this transaction is concurrently being called for the same order.</p>
  
  <pre><code>BEGIN TRANSACTION
          IF EXISTS (SELECT orderID FROM [Orders]WHERE orderID=@OrderId)
          BEGIN   
          UPDATE [Orders]  SET 
  [orderXml] =@orderXml  
          ,[updatedDateTime] = @updatedDateTime  
  WHERE 
  [orderId] = @OrderId  AND @updatedDateTime &gt; updatedDateTime
          END
  
  if @@ROWCOUNT = 0
          BEGIN
              DELETE OrderLine 
              WHERE
      orderID=@OrderId
          END
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p><strong>DeadLock Graph:</strong></p>
  
  <pre><code>&lt;resource-list&gt;
        &lt;keylock hobtid="72057594039042048" dbid="13" objectname="OrderDB.dbo.Orders" indexname="PK_Order" id="lockac2e8d80" mode="U" associatedObjectId="72057594039042048"&gt;
          &lt;owner-list&gt;
            &lt;owner id="process80736748" mode="U"/&gt;
          &lt;/owner-list&gt;
          &lt;waiter-list&gt;
            &lt;waiter id="process80739b88" mode="U" requestType="convert"/&gt;
          &lt;/waiter-list&gt;
        &lt;/keylock&gt;
        &lt;keylock hobtid="72057594039042048" dbid="13" objectname=" OrderDB.dbo.Orders" indexname="PK_Order" id="lockac2e8d80" mode="U" associatedObjectId="72057594039042048"&gt;
          &lt;owner-list&gt;
            &lt;owner id="process80739b88" mode="S"/&gt;
          &lt;/owner-list&gt;
          &lt;waiter-list&gt;
            &lt;waiter id="process80736748" mode="X" requestType="convert"/&gt;
          &lt;/waiter-list&gt;
        &lt;/keylock&gt;
      &lt;/resource-list&gt;
  </code></pre>
  
  <p>PK_Order  is the primary key on OrderId(varchar) column of Orders Table. The table also has got a Non clustered index on updatedDateTime(datetime2) column.</p>
  
  <p><strong>Question:</strong> When I use WITH(UPDLOCK) hint in above update statement the deadlock seems to go away.
  Is it advisable to use UPDLOCK hint or should i set the transaction Isolation level to Serializable.
  Also would it be advisable to use UPDLOCK hint in above delete statement as well.</p>