The START AFTER COMMIT statement specifies an SQL statement (such as a call to a stored procedure) that will be executed when he current transaction commits. If the transaction is rolled back, the specified SQL statement is not executed.
A statement started in the background 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.
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.
If you use the UNIQUE keyword before the <stmt>, that statement will be executed only if there is not already an identical statement executing or pending. Statementsare compared using simple string compare. For example call foo(1) is different from call foo(2). The server also takes into account whether the statement already being executed (or pending for execution) is on the same replica or a different replica; only identical statements on the same replica are discarded.
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 may not get sent to the replicas immediately.
NONUNIQUE means that duplicate statements can be executed simultaneously in the background.
FOR EACH REPLICA specifies that the statement is executed for each replica that fulfills the property conditions given in the search_condition part of the WHERE clause. Before executing the statement, a connection to the replica is established. If a procedure call is started, the procedure can get the “current” replica name using the keyword DEFAULT.
If RETRY is specified, the operation is re-executed after N seconds (defined by seconds in the retry_spec) if the replica is not reached on the first attempt. The count specifies how many times a retry is attempted.
Context of background statements
Statements started in the background are executed in the context of the user who issued the START AFTER COMMIT statement, and are executed in the catalog and schema in which the START AFTER COMMIT statement executed.
In the example below, “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.
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;