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.


1 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.

 BEGIN delete top (10000) from xx where RowNumber >1
 set @rowcount = @@rowcount
  • But, where is clustered index getting used here? The delete query plan shows it's doing an index seek on the Non-clustered index. Commented Apr 15, 2015 at 10:05
  • The clustered index is updated when you insert and delete, the non-clustered indexes are used to find the records faster. You will have to lock the row to insert/delete.
    – Spörri
    Commented Apr 15, 2015 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.