I have this scenario where I have multiple IS instances with a service checking whether a record exists or not in a table with unique constraint and performs insert if not found, say the unique column is billNo.
The problem occurs when the partner is sending duplicate documents with billNo, and the document published from Broker have to be concurrent as part of an existing framework shared with other partners. so when multiple documents from Broker containing the same billNo is received whether at the same time or different times, the IS service (in multiple instances) are expected to check the Table A whether or not billNo exists and then only inserts into Table A if not found. Unique constraint error happens when same billNo is received at the exact same time.
Is there a way to prevent the unique constraint error without having to change the Broker document concurrency?