I recently discovered that our ERP software is causing a lot of deadlock in our MS SQL database. For me, we should always avoid deadlock. If we have one, we should trace it to understand the cause and make change to eliminate them.
But the software provider is saying that it is like that by design:
1- New record are inserted in a table A, in order to be processed.
2- We have 2 JBoss servers, monitoring the same table for new record. As soon as one server is available, it will process the record and then delete/modifiy the record as processed.
3- Of course, if the 2 servers are available it will cause a deadlock.
4- At the end, not a issue for the application since one of the JBoss server will have complete the process successfully. So based on that, the software vendor is saying that deadlock are not an issue.
That is causing about 50 to 100 deadlock everyday. It is fine to let all of those dealock occur? (since the cause is identified) If not, I would like from you guys, good reasons to ask them to modify their software process.
Thanks
I have seed deadlocks if the logic for polling is something like this:
select top 1 @ID = ID from QUEUE where STATUS = 0
update QUEUE set STATUS = 1 where ID = @ID
This causes deadlocks because sometimes both jobs fetch the same ID before the other is able to update it.
This can be fixed quite easily using either query hints or doing the update + select in one statement. If you don't know what kind of query actually causes the deadlocks then this probably doesn't help.