SQL Server – Concurrent Delete/Insert Application

concurrencydeleteinsertsql server

How do we delete and then insert rows in a parallel, multithreading environment while avoiding deadlocks? I am still receiving deadlocks after applying UPDLOCK and SERIALIZABLE. See deadlock error below.

Resources:

I have a C#/ASP application running two different connections in a concurrent environment, trying to delete and later insert data for one person. I hear applications act differently.

I tried this with:

connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))

and

connection.BeginTransaction(System.Data.IsolationLevel.Serializable))

A nonclustered index exists on HeaderId, same Headerid can appear multiple times in the table.

<event name="xml_deadlock_report" package="sqlserver" timestamp="2017-10-10T07:10:41.524Z">
  <data name="xml_report">
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process3e7eb2188" />
        </victim-list>
        <process-list>
          <process id="process3e7eb2188" taskpriority="0" logused="26140" waitresource="KEY: 11:72057594039304192 (32fb2d1e9782)" waittime="3176" ownerId="2548574" transactionname="user_transaction" lasttranstarted="2017-10-10T00:10:38.313" XDES="0x3e7f223a8" lockMode="RangeS-U" schedulerid="8" kpid="18256" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-10-10T00:10:38.323" lastbatchcompleted="2017-10-10T00:10:38.320" lastattention="1900-01-01T00:00:00.320" clientapp=".Net SqlClient Data Provider" hostname="JOHN-SMITH" hostpid="12976" loginname="johnsmith" isolationlevel="serializable (4)" xactid="2548574" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="2" stmtstart="78" stmtend="496" sqlhandle="0x020000008f7940276a944bab809d15d2d911582332671d9f0000000000000000000000000000000000000000">
IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@HeaderId bigint)   
                IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )
                DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderId   </inputbuf>
          </process>
          <process id="process3f4792188" taskpriority="0" logused="33252" waitresource="KEY: 11:72057594039304192 (ffff51c3d6d0)" waittime="3176" ownerId="2548594" transactionname="user_transaction" lasttranstarted="2017-10-10T00:10:38.340" XDES="0x3e50a4d08" lockMode="RangeS-U" schedulerid="2" kpid="12624" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-10-10T00:10:38.340" lastbatchcompleted="2017-10-10T00:10:38.340" lastattention="1900-01-01T00:00:00.340" clientapp=".Net SqlClient Data Provider" hostname="JOHN-SMITH" hostpid="12976" loginname="johnsmith" isolationlevel="serializable (4)" xactid="2548594" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="7" stmtstart="498" stmtend="646" sqlhandle="0x020000008f7940276a944bab809d15d2d911582332671d9f0000000000000000000000000000000000000000">
DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderI    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@HeaderId bigint)   
                IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )
                DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderId   </inputbuf>
          </process>
          <process id="process3e7eb2558" taskpriority="0" logused="35336" waitresource="KEY: 11:72057594039304192 (ffff51c3d6d0)" waittime="3199" ownerId="2548590" transactionname="user_transaction" lasttranstarted="2017-10-10T00:10:38.320" XDES="0x3e7f22d28" lockMode="RangeS-U" schedulerid="8" kpid="12480" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-10-10T00:10:38.323" lastbatchcompleted="2017-10-10T00:10:38.323" lastattention="1900-01-01T00:00:00.323" clientapp=".Net SqlClient Data Provider" hostname="JOHN-SMITH" hostpid="12976" loginname="johnsmith" isolationlevel="serializable (4)" xactid="2548590" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="2" stmtstart="78" stmtend="496" sqlhandle="0x020000008f7940276a944bab809d15d2d911582332671d9f0000000000000000000000000000000000000000">
IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@HeaderId bigint)   
                IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )
                DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderId   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594039304192" dbid="11" objectname="TaxReturn.dbo.FinanceDetail" indexname="AK_FinanceDetail_HeaderId" id="lock3e4c12e80" mode="RangeX-X" associatedObjectId="72057594039304192">
            <owner-list>
              <owner id="process3f4792188" mode="RangeX-X" />
            </owner-list>
            <waiter-list>
              <waiter id="process3e7eb2188" mode="RangeS-U" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594039304192" dbid="11" objectname="TaxReturn.dbo.FinanceDetail" indexname="AK_FinanceDetail_HeaderId" id="lock3e4c57700" mode="RangeS-U" associatedObjectId="72057594039304192">
            <owner-list>
              <owner id="process3e7eb2558" mode="RangeS-U" requestType="wait" />
            </owner-list>
            <waiter-list>
              <waiter id="process3f4792188" mode="RangeS-U" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594039304192" dbid="11" objectname="TaxReturn.dbo.FinanceDetail" indexname="AK_FinanceDetail_HeaderId" id="lock3e4c57700" mode="RangeS-U" associatedObjectId="72057594039304192">
            <owner-list>
              <owner id="process3e7eb2188" mode="RangeS-U" />
            </owner-list>
            <waiter-list>
              <waiter id="process3e7eb2558" mode="RangeS-U" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

@HeaderId is bigint in SQL, long in C#. Execution plan has seek, no implicit conversion, FinanceDetail.HeaderId = Scalar Operator(@HeaderId).

Best Answer

The only query in what you're showing above appears to be this one, repeated a few times:

IF EXISTS 
                (
                    select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
                    where trd.HeaderId = @HeaderId
                )
                DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderId

To avoid deadlocks, simplify your query to this:

                DELETE from dbo.FinanceDetail
                where HeaderId = @HeaderId

You don't need to check for existence of rows if you're just going to delete them, and you don't need any locking hints in order to run a delete statement by itself.

You will not get an error if you try to delete a record that does not exist. It will just report 0 rows affected, which you can check for with @@ROWCOUNT.

Related Question