SQL Server Deadlock on delete

Question

I have a query like this:

with xx as
              (
              SELECT [FilePath], ROW_NUMBER() over (partition by [FilePath]  order by [FilePath]) as RowNumber
              FROM [dbo].[foo] where (a = 'a1' and b = 'b1') or (a = 'a2' and b = 'b2')) delete xx where RowNumber>1

(a and b, together constitute composite non-clustered index. Other non-clustered index is on a different single column)

Which is executing parallel from different instances, but the where clause will separate the data on which it runs without overlaps. Other than that, inserts are also running on it, which may or may not affect the result of this query.

Inserts are typical, like

insert into [dbo].[foo] values('a1', 'b1', 'c1', 'd1')

Now I've started receiving the following error message:

Transaction (Process ID 199) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What can be the possible cause of deadlock here? Is the order of taking the lock on the indexes while insert and while delete the reason? Thanks in advance.

Answer 1

There is the possibility that you are getting lock escalations onto the table level, or page locks on the clustered index page. A deadlock graph will show that, try starting a SQL Server trace grabbing just the Deadlock graphs from the server and check those

You can also delete in batches which will improve concurrency and if the delete is handling large number of rows might run faster and allow you to backup and truncate the log and lower the changes of deadlocking.

DECLARE @ROWCOUNT int = 1
WHILE @ROWCOUNT > 0
 BEGIN delete top (10000) from xx where RowNumber >1
 set @rowcount = @@rowcount
END