▪ When the body of the deferred procedure call (START AFTER COMMIT) is executed, it runs asynchronously in the background.
This allows the server to immediately start executing the next SQL statement in your program without waiting for the deferred procedure call statement to finish. It also means that you do not have to wait for completion before disconnecting from the server. In most situations, this is an advantage. However, in a few situations this might be a disadvantage. For example, if the body of the deferred procedure call locks records that are needed by subsequent SQL statements in your program, you might not want the body of the deferred procedure call to run in the background while your next SQL statement runs in the foreground and has to wait to access those same records.
▪ The statement to be executed is executed only if the transaction is completed with a COMMIT, not a ROLLBACK.
If the entire transaction is explicitly rolled back, or if the transaction is aborted and thus implicitly rolled back (due to a failed connection, for example), then the body of the START AFTER COMMIT is not executed.
▪ Although the transaction in which the deferred procedure call occurs can be rolled back (thus preventing the body of the deferred procedure call from running), the body of the deferred procedure call cannot itself be rolled back if it has executed. Because it runs asynchronously in the background, there is no mechanism for canceling or rolling back the body after it starts executing.
▪ The statement in the deferred procedure call is not guaranteed to run until completion or to be run as an "atomic" transaction.
For example, if your server crashes, then the statement will not resume executing the next time that the server starts, and any actions that were completed before the server crashed might be kept. To prevent inconsistent data in this type of situation, you must program carefully and make proper use of features like referential constraints to ensure data integrity.
▪ If you execute a START AFTER COMMIT statement in autocommit mode, then the body of the START AFTER COMMIT is executed "immediately" (that is, as soon as the START AFTER COMMIT is executed and automatically committed).
There are a few subtle differences between this situation and executing the body of the START AFTER COMMIT directly. A direct call to my_proc is synchronous; the server does not return control to you until the stored procedure has finished executing. If you call my_proc as the body of a START AFTER COMMIT, however, then the call is asynchronous; the server does not wait for the end of my_proc before allowing you to execute the next SQL statement. In addition, because START AFTER COMMIT statements are not truly executed "immediately" (that is, at the time that the transaction is committed) but might instead be delayed briefly if the server is busy, you might or might not actually start running your next SQL statement before my_proc even starts executing. It is rare for this to be desirable behavior. However, if you truly want to launch an asynchronous stored procedure that will run in the background while you continue onward with your program, it is valid to do START AFTER COMMIT in autocommit mode.
▪ If more than one deferred procedure call was executed in the same transaction, then the bodies of all the START AFTER COMMIT statements will run asynchronously. This means that they will not necessarily run in the same order as you executed the START AFTER COMMIT statements within the transaction.
▪ The body of a START AFTER COMMIT must contain only one SQL statement. That one statement can be a procedure call, however, and the procedure can contain multiple SQL statements, including other procedure calls.
▪ The START AFTER COMMIT statement applies only to the transaction in which it is defined. If you execute START AFTER COMMIT in the current transaction, the body of the deferred procedure call is executed only when the current transaction is committed; it is not executed in subsequent transactions, nor is it executed for transactions executed in any other connections. START AFTER COMMIT statements do not create "persistent" behavior. If you want the same body to be called at the end of multiple transactions, then you must execute a "START AFTER COMMIT ... CALL my_proc" statement in each of those transactions.
▪ The "result" of the execution of the body of the deferred procedure call (START AFTER COMMIT) statement is not returned in any way to the connection that ran the deferred procedure call. For example, if the body of the deferred procedure call returns a value that indicates whether an error occurred, that value is discarded.
▪ Almost any SQL statement can be used as the body of a START AFTER COMMIT statement. Although calls to stored procedures are typical, you can also use UPDATE, CREATE TABLE, or almost anything else (however, it is not recommended to have a START AFTER COMMIT statement inside a START AFTER COMMIT). Note that a statement like SELECT is generally useless inside an deferred procedure call because the result is not returned.
▪ Because the body is not executed at the time that the START AFTER COMMIT statement is executed inside the transaction, START AFTER COMMIT statements rarely fail unless the deferred procedure call itself or the body contains a syntax error or some other error that can be detected without actually executing the body.