February 23, 2021 at 10:05 am
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
February 23, 2021 at 10:18 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2021 at 10:27 am
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
February 23, 2021 at 10:36 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2021 at 10:51 am
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
February 23, 2021 at 11:18 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2021 at 12:25 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply