DeadLock in Update Statement and Usage of UPDLOCK Hint
For the following transaction occasionally I am receiving Dead Lock error when this transaction is concurrently being called for the same order.
BEGIN TRANSACTION
IF EXISTS (SELECT orderID FROM [Orders]WHERE orderID=@OrderId)
BEGIN
UPDATE [Orders] SET
[orderXml] =@orderXml
,[updatedDateTime] = @updatedDateTime
WHERE
[orderId] = @OrderId AND @updatedDateTime > updatedDateTime
END
if @@ROWCOUNT = 0
BEGIN
DELETE OrderLine
WHERE
orderID=@OrderId
END
COMMIT TRANSACTION
DeadLock Graph:
<resource-list>
<keylock hobtid="72057594039042048" dbid="13" objectname="OrderDB.dbo.Orders" indexname="PK_Order" id="lockac2e8d80" mode="U" associatedObjectId="72057594039042048">
<owner-list>
<owner id="process80736748" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process80739b88" mode="U" requestType="convert"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594039042048" dbid="13" objectname=" OrderDB.dbo.Orders" indexname="PK_Order" id="lockac2e8d80" mode="U" associatedObjectId="72057594039042048">
<owner-list>
<owner id="process80739b88" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process80736748" mode="X" requestType="convert"/>
</waiter-list>
</keylock>
</resource-list>
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.
Question: 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.
This is a classic case of deadlock. The access pattern is read-then-write. Both trans read, then both fail to write.
You solution of taking a "write" lock ( UPDLOCK ) is good. Use UPDLOCK, ROWLOCK, HOLDLOCK which I consider to be best-practice for cases like this one.
SERIALIZABLE does not help because the pattern would still be read-then-write.
The DELETE does not need additional locking because the transaction has already acquired exclusive access to the row in question.