CODEWITHSUNDEEP
sql-servert-sqlsql-updatedatabase-deadlocks
Kamil
Kamil

Reputation: 149

MSSQL Deadlock when update withc (updlock)

I got deadlock while updating. The transaction level is set to Read Committed. How to avoid deadlock in such situation?

In other cases WITH (NOLOCK) and WITH (UPDLOCK) helped.
I got the following T-SQL query: 

IF EXISTS (SELECT 1 FROM DEBTORS_CUSTOMERS WITH (NOLOCK) WHERE DebtorId = @DebtorId AND ClientFCCustomerNumber = @CustomerNumber)
        UPDATE DEBTORS_CUSTOMERS WITH (UPDLOCK) SET StatusId = @StatusId WHERE DebtorId = @DebtorId AND ClientFCCustomerNumber = @CustomerNumber
    ELSE
        INSERT INTO DEBTORS_CUSTOMERS (DebtorId, ClientFCCustomerNumber, StatusId, DocId) SELECT @DebtorId, @CustomerNumber, @StatusId, @DocId

And here is the deadlock I got:

   <resource-list>
   <keylock hobtid="72057594105692160" dbid="63" objectname="EOTestDataGenerator.dbo.DEBTORS_CUSTOMERS" indexname="PK_DEBTORS_CUSTOMERS" id="lockdf8abb00" mode="X" associatedObjectId="72057594105692160">
    <owner-list>
     <owner id="process3f59048" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processbdbfa088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594105692160" dbid="63" objectname="EOTestDataGenerator.dbo.DEBTORS_CUSTOMERS" indexname="PK_DEBTORS_CUSTOMERS" id="lockdf5ab200" mode="X" associatedObjectId="72057594105692160">
    <owner-list>
     <owner id="processbdbfa088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process3f59048" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>

Upvotes: 2

Views: 4196

Answers (1)

usr
usr

Reputation: 171178

You are processing multiple rows per transaction, right? This should not deadlock for one row

You might get double-inserts, though, which is a bug. Two sessions might conclude that there is no row and then both will insert.

There are two ways to make this safe:

  1. Issue the select WITH (ROWLOCK, UPDLOCK, HOLDLOCK) which is a well-known lock hint sequence. It takes a lock that stabilized the data you are operating on. After this statement has run you have the data for yourself. You can then insert or update. You can also collapse all three statements into one MERGE but you still need the lock hints. Also, you must have some kind of global order in which you issue the writes. Right now no matter how you lock there can always be a deadlock if one session writes A, B and the other writes in order B, A. An easy way to get a global order is to issue all writes in a single MERGE statement. The query processor usually picks a plan that enforces order.
  2. Use SERIALIZABLE isolation with retry on deadlock.

Upvotes: 4

Related Questions