Ensuring a deferred procedure has completed before proceeding
If you do not want the next SQL statement in your program to run until a deferred procedure call statement has finished running, write your program in the following way:
1 At the end of the deferred procedure CALL statement (for example, at the end of the stored procedure that is called by the deferred procedure CALL statement), post an event, see Events.
2 Immediately after you commit the transaction that specifies the deferred procedure call, call a stored procedure that waits on the event.
3 After the stored procedure call (to wait on the event), put the next SQL statement that your program wants to execute.
For example, your program might have the following code:
The stored procedure wait_for_sac_completion waits for the event that myproc posts. Therefore, the UPDATE statement does not run until after the deferred procedure call statement finishes.
However, this workaround is slightly risky. Since deferred procedure call statements are not guaranteed to execute until completion, there is a chance that the stored procedure wait_for_sac_completion never gets the event that it is waiting for.
The primary purpose of the START AFTER COMMIT statement is to support "Sync Pull Notify". The Sync Pull Notify feature allows a master database to notify its replica databases that data has been updated and that each replica database can request a refresh to get the new data. If this notification process fails, there is no data corruption; just a longer delay before a replica database refreshes the data. Since a replica database is always given all the data since its last successful refresh operation, a delay in receipt of data does not cause the replica database to permanently miss any data. For more details, see Sync Pull Notify.
Notes
▪ The statement inside the body of the START AFTER COMMIT statement can be any statement, including a SELECT statement. However, as the body of the START AFTER COMMIT does not return its results, a SELECT statement is generally not useful inside a START AFTER COMMIT statement.
▪ If you are in auto-commit mode and execute a START AFTER COMMIT statement, then the given statement is started immediately in the background. Note that immediately means ‘as soon as possible’, because the statement is still executed asynchronously when the server has time to do the execution.