0

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

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
  • But, where is clustered index getting used here? The delete query plan shows it's doing an index seek on the Non-clustered index. – ptntialunrlsd Apr 15 '15 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 Apr 15 '15 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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