Use the START AFTER COMMIT statement to specify a single SQL statement (such as a call to a stored procedure) that is executed when the current transaction commits. If the transaction is rolled back, the specified SQL statement is not executed.
If the body of your START AFTER COMMIT is a call to a stored procedure, that procedure can be local or it can be on another replica or master database.
The START AFTER COMMIT statement can occur anywhere inside the transaction, and the body of the statement is executed only when the transaction is committed.
This means that there are two different phases to the START AFTER COMMIT statement: the definition phase and the execution phase.
▪ In the definition phase of START AFTER COMMIT, you specify the body but do not execute it. The definition phase can occur anywhere inside a transaction; in other words, the statement "START AFTER COMMIT ..." can be placed in any order relative to other SQL statements in the same transaction.
▪ In the execution phase, the body of the START AFTER COMMIT statement is actually executed. The execution phase occurs when the COMMIT WORK statement for the transaction is executed. It is also possible to execute a START AFTER COMMIT in autocommit mode, but there is rarely a reason to do this.
The START AFTER COMMIT statement applies only to the current transaction, that is, the transaction that included the START AFTER COMMIT statement. It does not apply to subsequent transactions, or to any other transactions that are currently open in other connections.
A statement that is started in the background by using START AFTER COMMIT is executed in a separate transaction. That transaction is executed in autocommit mode, that is, it cannot be rolled back as soon as it has started.
You can have more than one START AFTER COMMIT statement per transaction. The body of each of these START AFTER COMMIT statements is executed when the transaction is committed. However, the bodies run independently and asynchronously; they do not necessarily execute in the same order as their corresponding START AFTER COMMIT statements, and they might have overlapping execution (there is no guarantee that one will finish before the next one starts).
Parameters, clauses, keywords, and variables
▪ UNIQUE: Statement is executed only if there is not already an identical statement executing or pending on the same replica server. Statements are compared by using simple string compare. For example call foo(1) is different from call foo(2).
Important When duplicate statements are discarded by using the UNIQUE keyword, the most recent statements are the ones thrown out, and the oldest one is the one that keeps running. It is quite possible to create a situation where you do multiple updates, for example, and you trigger multiple START AFTER COMMIT operations, but only the oldest one executes and thus the newest updated data might not get sent to the replica databases immediately.
▪ NONUNIQUE: Duplicate statements can be executed simultaneously in the background.
▪ FOR EACH REPLICA: Statement is executed for each replica database that fulfills the property conditions given in the search‑condition part of the WHERE clause. Before executing the statement, a connection to the replica database is established. If a procedure call is started, the procedure can get the "current" replica name using the keyword DEFAULT.
▪ RETRY: Statement is re-executed if the replica is not reached on the first attempt.
– seconds: Specifies how long to wait before re-executing statement.
– count: Specifies how many more times the operation is attempted.
Context of background statements
Statements started in the background are executed in the context of the user who executed the START AFTER COMMIT statement, and are executed in the catalog and schema in which the START AFTER COMMIT statement executed.
In the following example, "CALL FOO" is executed in the catalog "katmandu" and the schema "steinbeck".
SET CATALOG katmandu; SET SCHEMA steinbeck; START AFTER COMMIT UNIQUE CALL FOO; COMMIT WORK; SET CATALOG irrelevant_catalog; SET SCHEMA irrelevant_schema
Durability
Background statements are not durable. In other words, the execution of statements started with START AFTER COMMIT is not guaranteed.
Rollback
Background statements cannot be rolled back after they have been started. After a statement that has been started with START AFTER COMMIT has executed successfully, there is no way to roll it back.
The START AFTER COMMIT statement itself can be rolled back, and this will prevent the specified statement from executing. For example,
START AFTER COMMIT UNIQUE INSERT INTO MyTable VALUES (1); ROLLBACK;
In the example above, the transaction rolls back and thus INSERT INTO MyTable VALUES (1) will not be executed.
Order of execution
Background statements are executed asynchronously and they do not have any guaranteed order even inside a transaction.
Returns
The START AFTER COMMIT statement returns a result set with one INTEGER column. This integer is a unique "job" id and can be used to query the status of a statement that failed to start due to an invalid SQL statement, insufficient access rights, replica not available, and so on.
Examples
Start local procedure in the background.
START AFTER COMMIT NONUNIQUE CALL myproc;
Start the call if "CALL myproc" is not running in the background already.
START AFTER COMMIT UNIQUE call myproc;
Start procedure in the background using replicas which have property "color" = "blue".
START AFTER COMMIT FOR EACH REPLICA WHERE color='blue' UNIQUE CALL myproc;
The following statements are all considered different and therefore each is executed, despite the presence of the keyword UNIQUE. name is a unique property of each replica.
START AFTER COMMIT UNIQUE call myproc;
START AFTER COMMIT FOR EACH REPLICA WHERE name='R1' UNIQUE call myproc;
START AFTER COMMIT FOR EACH REPLICA WHERE name='R2' UNIQUE call myproc;
START AFTER COMMIT FOR EACH REPLICA WHERE name='R3' UNIQUE call myproc;
But if the following statement is executed in the same transaction as the previous ones and the condition color='blue' matches some of the replicas R1, R2 or R3, the call is not executed for those replicas again.
START AFTER COMMIT FOR EACH REPLICA WHERE color='blue' UNIQUE call myproc;