The MESSAGE DELETE CURRENT TRANSACTION statement deletes the current transaction from a given message in the master database.
The MESSAGE DELETE CURRENT TRANSACTION statement is applicable only in advanced replication configurations. It can only be issued on the master database.
To use this statement, you must have SYS_SYNC_ADMIN_ROLE privilege.
If a DBMS level error such as a duplicate insert occurs during the execution, the execution of a message stops. This kind of error can be resolved by deleting the offending transaction from the message. Once deleted with the MESSAGE FROM REPLICA DELETE CURRENT TRANSACTION, an administrator can proceed with the synchronization process.
When deleting the current transaction, the autocommit mode must be switched off.
This statement is used only when the message is in an error state; if used otherwise, an error message is returned. This statement is a transactional operation and must be committed before message execution can continue. To restart the message after the deletion is committed, use the following statement: MESSAGE msgname FROM REPLICA replicaname EXECUTE
The deletion is completed before the MESSAGE FROM REPLICA EXECUTE statement is executed; that is, the statement starts the message from replica, but waits until the active statement is completed before actually executing the message. Thus the statement performs asynchronous message execution.
Note Delete a transaction only as a last resort; normally transactions should be written to prevent unresolved conflicts in a master database. MESSAGE FROM REPLICA DELETE CURRENT TRANSACTION is intended for use in the development phase, when unresolved conflicts occur more frequently.
Use caution when deleting a transaction. Because subsequent transactions can be dependent on the results of a deleted transaction, the result might be more transaction errors.
Return values
Error code
Description
13047
No privilege for operation
25009
Replica replica_name not found
25013
Message name message_name not found
25018
Illegal message state.
An attempt was made to delete a transaction from a message that is not in error.
25056
Autocommit not allowed
Examples
MESSAGE somefailures FROM REPLICA laptop1 DELETE
CURRENT TRANSACTION;
COMMIT WORK;
MESSAGE somefailures FROM REPLICA laptop1 EXECUTE;