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