How to prevent unique constraint error when multiple IS nodes receiving unique docId at the same tim

, ,
1

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.

Table A:
billNo
date_received

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?

2

This is a classic race condition. You either need to change the processing to serial, or you need to specifically handle this error.

In your processing, after checking whether or not to do an insert or update, if the insert fails, you check to see if it is because unique constraint violation. If so, then you determine how you want to handle the situation.

Two records with the same ID presumably are not the same. If they are the same, the failed insert can be ignored – the DB already has the data.

If they are not the same do you have a way to determine which is newer? Then you can decide whether to continue with the “failed” record and do an update, or ignore it as “superceded.”