I have 3 tables (general_log, queue_log, temporary_processed) and 2 stored procedure that process the rows. In the 3rd table (temporary_processed) the events are assembled in order be further processed.
The *_log tables has 2 column: event_name, event_id, and I have 2 trigger procedures that are triggered after INTO into the *log tables.We have the following flow:
A row is inserted into general_log, and the trigger is executed creating a new row in table 3(temporary_processed)
A row is inserted into queue_log table that triggers an update on 3rd table for the line inserted in the step 1.
Everything is ok, and works as expected with some exceptions. But, it seems that id the DB server is on high load and these 2 events (step1 and 2) are created with less than 1 seconds between then, the update at step 2 return 0 affected rows as the line at step 1 was not inserted yet.
How can I make sure that the trigger update runs only after the line from step 1 was inserted ?
As a factor of scale general_log = queue_log *4 as number of rows.
I was thinking that a nice try would be to put a while where I perform the update and to repeat the update until it produces affected rows = 1.