SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Multi threading

Multi threading

SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 371
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.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum