Database deadlock

[TeriStevenson]

This morning my production database was deadlocked.  When the sql operation team reviewed database logs they saw a blocking query

(@RequestID int,@Cleaned int,@RunMachine int,@RunMachineSuccess int,@RunMachineError int,@CleanedOn datetime)UPDATE WD_PackageRequests SET Cleaned = @Cleaned, CleanedOn = @CleanedOn, RunMachine = @RunMachine, RunMachineSuccess = @RunMachineSuccess, RunMachineError = @RunMachineError WHERE RequestID = @RequestID

Any suggestions on why this might have caused a deadlock on the database?

[ChrisG]

I think what you're describing here is queries being "blocked", not "deadlocked" (which is something quite different).

A simple UPDATE of the WD_PackageRequests table seems like a strange thing to be the root of a blocking chain. Are you sure this was the root blocker, or could it been that this query in turn was blocked by some other query that was running?

[CharlesW]

I have to admit, I don't really have the expertise to diagnose the actual deadlock (or why it was Blocked). The query itself is part of the collection clean process. I know that you have multiple instances of App Broker installed. Can you confirm that only one instance of the ESD Service is running? Also, can you check CollectionClean_<server>.log to see if you have a large "clean count". I seem to remember that the code is a little flakey in this area, where multiple collection clean processes may be running at the same time, if there is a backlog of cleans that need to be processed. 

[TeriStevenson · Accepted solution]

I think we found the culprit.  One of my colleagues ran a query in the production database that caused the block, so the query that does the clean was blocked 😞