Avoiding a race condition in "find or create" logic with db triggers + bulk API

Here’s the setup I have:

  1. I use the Bulk Create Data API to dump a certain number of new records into my db
  2. I have a database trigger set up to watch for those new records
  3. For each new record that comes in, I process it. One of the steps is to look at a field, say “location”, and either find the Location thing that has the same name OR, if it doesn’t exist yet in my db, create it. This is essentially “find or create” logic.
  4. After I’ve found or created the Location thing, I want to attach it to a field of the processed record

The issue I’m having is with race conditions happening in step 3. If I’m dumping in 10 records all with the same location, and the location does not exist in my db prior, multiple instances of the “find or create” will run at the same time and result in multiple records of the same location being created.

Any tips on solving this situation?

You can create a Q but there’s unfort a decent amount of admin for setting that up in Bubble. I can give you the bullet points for that if necessary but it doesn’t seem that’s necessary here.

I think just a recursive workflow that goes thru each record would do the trick. You can trigger this recursive workflow from the API or an admin, or even a trigger (would need a workaround).

You can also build into the location “function” to only call the create conditional if there are no records created before current record with location (so that only the first one goes thru create logic). Then adding a API WF on a list post create can update all records with the same location that aren’t yet linked to the location record.

Thanks for the suggestions. After thinking about it and getting some advice, I concluded that I could circumvent the problem a bit by doing the following:

  • The data type of the record being created has a field called “processed”, defaulting to no
  • I stopped trying to use a db trigger; having them all fire at the same time is problematic because of the race condition
  • Instead, I created a Workflow API endpoint to find the first record with “processed = no”, process it (which includes the find-or-create logic for some of its fields), mark the item “processed = yes”, then schedule itself recursively with the next item with “processed = no”.
  • Since I’m already using code to call the bulk create Data API, at the end of that code I make the call to the new Workflow API endpoint to kick off all the processing

Another suggestion given to me was to change the code I have that’s making the bulk create Data API calls to figure out what the unique values are for the fields I do a find-or-create on, send those into the app to be processed with find-or-create logic (the list of such records could be unique-ified by the code), then proceed with my original db trigger logic firing upon the bulk create. This way, it’s no longer a “find or create”, just a “find”.

Basically what I said? No need for process boolean unless you anticipate non findable locations.