cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Database deadlock

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?

(1) 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 😞

View solution in original post

(3) Replies
ChrisG
By Community Manager Community Manager
Community Manager
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?
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
CharlesW
By Level 12 Flexeran
Level 12 Flexeran

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. 

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 😞