Forcing serialization of incoming stored procedure calls

  • Hello,

    We have implemented a data import system based on an webservice which receives from an external system the necessary data to be imported as payload in XML format. We have a stored procedure interpreting the XML file received and doing the necessary actions to import data according to the type of data received (product definitions, barcodes, prices etc) recognized from the XML file header.

    The problem is that sometimes (not very rare, unfortunately) the external system sends data like a "machine gun", same type of data (my worst case is prices) in the same time, generating issues in XML processing and import because of parallel processing. As I don't have any power to force a change in the external system to ensure the transmission serialization, I try to find a way to ensure that my stored procedure calls are executed serialized, with a small delay based on the timestamp of the execution start. This is my idea, as we explored without success the options of using a semaphore (as we can't ensure that after the semaphore is released the transmissions will be processed in the receiving order) or table locking (as there other modules using those tables and we can create a mess there).

    Does anyone had a similar issue? And if yes, there is any solution?

    Thanks a lot.

    ___________________________________________________________________
    Is better shut up and look stupid than open your mouth and eliminate any doubt

  • Can you clarify the problem a little, please? Is it

    a) Files are not processed in the correct order, or

    b) Multiple files are processed at the same time (because multiple instances of the same stored proc are running)

    c) Something else?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hello Phil,

    The problem is "b) Multiple files are processed at the same time (because multiple instances of the same stored proc are running)". Because of this the verification for duplicated records fails or - worst case scenario - we face deadlocks in processing data.

    ___________________________________________________________________
    Is better shut up and look stupid than open your mouth and eliminate any doubt

  • OK, so can you change the process which triggers the execution of the proc so that it executes only one instance at a time?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Unfortunately no. The trigger is the webservice call received from the external application. We can't control when they make the calls.

    ___________________________________________________________________
    Is better shut up and look stupid than open your mouth and eliminate any doubt

  • It sounds like you need to introduce a queueing system. The execution of the proc needs to be decoupled from when the web service triggers.

    Your web service adds entries to the queue and the stored procedure takes one row at a time from the queue and processes it.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thank you Phil for the idea, it looks very promising at the first glance. It is so nice and simple to implement that I'm really pissed off now that I wasn't thinking to it before. I'll let you know if you saved my day.

    ___________________________________________________________________
    Is better shut up and look stupid than open your mouth and eliminate any doubt

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply