I have a database trigger which has couple of steps in it. It gets triggered when a wallet top-up entry is created. But if two entries are created very close to each other, the two triggers also happen nearly at the same time.
Now since the trigger has couple of steps in it, it may so happen that Step 1 of each trigger is executed first and then step 2 of both are executed. This causes some data inconsistencies as in step 1 I update one data and in step 2 I update another data but using the data updated in step 1.
Here’s pictorial description of this.
So, say balance before the top-up was x and the two top-ups that are supposed to happen are a and b. So total balance at the end should be x+a+b, but it is being either x+a or x+b as both the operations are taking ‘x’ as the balance. This is because the database trigger has a few steps for calculations and in first step they note down the balance. Now if both triggers are running in parallel, both the triggers take ‘x’ as the initial balance and update it.
I have worked around it for now by making the second step another API call and adding a delay in invoking it (as I can’t add delay in backend workflows), but is there a better solution here that assures atomicity of operations or if I can lock it such that only one trigger can run at a time?