September 14, 2013 at 11:34 am
The application team has 4 app servers each running a RabbitMQ service that receives messages and calls a stored proc passing the messages.
I am working on that stored proc. Basically it receives an XML message that it shreds to perform an insert ( saving as raw data for rerun/restart) then it begins to do post processing ( mapping message content to database content and updating one or more tables after the reconciliation).
Needless to say we can as as many instances of the sproc. However due to the nature of the business rules ( for example one that requires to always store the message but ignore post processing for subsequent messages that are determined to be duplicates), I am experiencing deadlocks because my sproc updates the rows...
I have cleaned up the sproc code as to where I do only 1 update instead of multiple mini updates per business rule.
But the aim is to avoid deadlocks.
This means I will have to do workload distribution. In brief, when the service call my sproc, I will simply do an insert to store the message raw and unprocessed. Then a SQL server Agent job that runs every minute will pick up the messages for post processing distributing the messages using the modulus function. I plan to have 4 threads at this time.
Using SSIS I can use the modulus function to do workload distribution but I don't know how to do that with a single SQL server Agent Job.
I really do not want to set up 4 SQL Server Agent jobs that call the same sproc, each job identifying itself with a number to get assigned its modulus message number.
Any suggestions will be much appreciated.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply