I’m trying to get around a problem I’m having with a concurrent trigger. Here is a simplified scenario: assume we have records with an ID, a name and a score. eg, (1, John, 100).
The trigger receives documents in very quick succession constantly updating the scores of multiple people. On the first instance of seeing their name, I need to insert them into the DB, or otherwise update their score.
My flow’s pseudocode is like this:
pub.art.transaction:startTransaction personExists = JDBC adapter service select where name=john if !personExists then insert the incoming record into the database else update john's score in the database end if pub.art.transaction:commitTransaction
Essentially this works, except sometimes two johns end up inserted into the database. The obvious cause is since the trigger is concurrent, the select executed twice before the first insert occurred.
The select is done with a JDBC adapter service, insert and update likewise, and they all use the same JDBC connection (SQL server) set to LOCAL_TRANSACTION. I have not set the name on the startTransaction, but have used its output for the commit.
I’m sure theres some gap in my understanding here. I thought this would have been quite straightforward (ie, by wrapping the process in a transaction I was under the impression I’d be protected from a scenario like this)