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.