Database Tables Attempting to Update Before Creation

(For clarity, the data that I am referencing for this is in the form of an array of objects, each containing an array of objects. Both the outer and inner arrays have an unknown number of elements, so this must be done dynamically.)

I currently have a workflow that iterates through an array of objects, and for each object a new empty row is inserted into a database table. From here, the array is iterated through again, and this time, an array within each object is iterated through, using the inner object's data to update the row columns one at a time.
The trouble is that the database often doesn't fully update on the first pass, and requires the workflow to run twice. My assumption is that this happens because the code to update the row attributes is being called before the row is totally inserted in the database.
If this is the case, does anyone have a method for forcing the code to pause until the database row is definitely inserted. Or to at least a way to continue trying to update the row until it exists?

Sounds like a case for an async process that awaits the insert of the outer rows before proceeding to insert the inner rows....but how do you have this set up? Is it two separate query blocks that run one after the other using the same source object, or is it all in one block?

to add to @jg80's question list, what kind of database is it? does this happen only when multiple people are using it or when just one is?

the solution might need to be implemented on the DB side as this could be a race-condition problem

1 Like

It is currently two sequential code blocks calling function queries to handle the database. After the first block runs to create the necessary rows in the tables, the second block runs to update the rows with the data.

Do you know if an async process can monitor, or check for, the existence of a table row?

It's just me using the workflow right now. The issue is that the rows will be created, but then the data will not be updated. Could race conditions be referenced in the workflow to hold off on attempting an update until the row exists?

Brute force approach might be to add a block that queries the table storing the outer records in a loop until it finds a record for all in the DB, then proceed to the inner inserts. @bobthebear is probably on to the correct long term solution.

1 Like