CODEWITHSUNDEEP
sql-servertransactionsdeadlock
user2520246
user2520246

Reputation: 27

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.

Upvotes: 1

Views: 3036

Answers (1)

usr
usr

Reputation: 171216

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.

Upvotes: 2

Related Questions