How to lock the database
We currently have a queue based integration from SQL to CRM which uses the key cross reference table to put a lock on the database when processing messages so duplicate messages cannot be processed at the same time. We have an issue where when a key cross violation occurs it puts the message into retry but still successfully commits all actions before it meaning we are getting 120 duplicates of messages in CRM.
The Key Cross table is only being used to lock the database when processing messages to stop duplicates occurring, and as of such we are only calling the XRef in pre-op to check this. Does anyone have any other suggestions on how we can lock the database to avoid this issue or a solution for the KeyCross going into retry whenever there is a violation?