I have a scenario where I am pushing messages containing upsert information on an object based on a primary key. these messages are being published in order.
I am looking to find a pattern for consuming messages off the UM topic. I would like the trigger to have Concurrent processing enabled but only to start a new processing thread if the object id is not already being processed.
the problem i am having to solve is that messages are currently trying to update and create at the same time creating a race condition.
You are right. Serial processing does solve this issue, however it also adds an overhead as messages are then backed up. ideally I would like concurrently but not the same one at the same time
If it does not occur too often you could try to live with that error: In case of an error implement a proper retry and hope that the next run will work.
Multiple ways to skin this cat. I tend to keep things as simple as possible until no longer possible. For that reason, I’m also of the opinion that a serial trigger should be your first option. If you’re not certain that a serial trigger can’t keep up with the volume, give it a shot first before engineering a solution to a problem that may not exist.
Having said this, if you’re set on concurrency and possibly even if you’re not, my advice to you is not to deal with the base/production table directly. Instead, consider doing INSERTs only but into a staging table, and then allow a separate database process to move the records from the staging table to the target base/production table. This enables your integration to be fast (and concurrent) and it moves the heavy lifting of in-order processing to the database, who is well equipped to do that.
I have also used @engin_arlak’s approach in the past and its sound. You’d have to ensure that the SELECT against the updateDate and the INSERT/UPDATE are synchronized though so you don’t run into race conditions. If you go down this route, I’d recommend implementing this logic in a stored procedure on the database side for maximum efficiency, and then from webMethods, you would simply make a single call to the procedure who would handle the rest.
when using serial processing is not an option for you, you can try with concurrent processing while using only 1 thread for the trigger.
But this will not work in clustered environments as this will result in at least 2 threads then, one on each node of the cluster.
In this case using serial processing is required.