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?
Oct 31, 2022 02:54 PM - edited Oct 31, 2022 02:57 PM
Oct 31, 2022 10:38 PM
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.
Nov 01, 2022 11:38 AM