SQL Server Concurrent Delete/Insert Application

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:

• Mythbusting Concurrent Update Insert Connections, Michael Swart

• Insert Update Racing Condition, Dan Guzman

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.

`










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

unknown


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




DELETE from dbo.FinanceDetail
where HeaderId = @HeaderI

unknown


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




IF EXI

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.

StackExchange Database Administrators Q#188082, answer score: 13