If an Intelligent Transaction fails because of a fatal error, then the execution of the message is stopped in the master database and the transaction is rolled back. The error code of the failed operation is returned to the replica database as the error code of the synchronization messaging statement that was supposed to return the reply message to the replica database.

The error code is returned to the replica as a return code from either of these statements:

In the master database, the system table SYS_SYNC_MASTER_MSGINFO contains information about all messages that currently reside in the master database, see SYS_SYNC_MASTER_MSGINFO.

If the execution of a message has failed because of an error, that message has a value of 1 in the STATE column. The ERROR_CODE and ERROR_TEXT columns contain information about the error that caused the message execution to halt. To identify the originating replica database and the statement that caused the message to halt in the master database, you can query the SYNC_FAILED_MESSAGES view in the master database, see SYNC_FAILED_MESSAGES.

The best way to recover from a failed message in the master database is to fix the error in the master database. For example, the reason for the error could be a unique constraint violation. To fix this error, the existing data in the master database must be modified to allow the new row to be inserted. (It is not possible to modify the contents of the transaction that resides in the transaction queue.) Alternatively, there could be a programming error in a stored procedure that needs to be corrected by re-creating the stored procedure in the master database.

After the error is corrected, restart the message in the master database by using the MESSAGE EXECUTE statement, see MESSAGE EXECUTE.

After the message is successfully executed in the master database, you can request a reply message to go to the replica database by using the MESSAGE GET REPLY statement, see MESSAGE GET REPLY.

As a last resort, if there is no other means for resolving the error, you can delete either the entire failed message from the master database or just the current transaction in the message, see Recovering from errors by deleting a message.

In general, transactions should be written to avoid concurrency conflicts and deadlocks. However, because conflicts can still occur when transactions update or delete rows, it is recommended that you use the SYS_TRAN_MAXRETRY bulletin board parameter in the master database to set a value for the maximum number of retries that should occur if a propagated transaction fails, see SYS_TRAN_MAXRETRY.