Specifying recovery from fatal errors

The most important rule of error handling is that all transactions must be commited in the master database. Any DBMS error is a fatal error and ,by default, causes the execution of the synchronization message to halt. These errors are system-level errors. For instance, if a write operation fails in the master database during synchronization because of a unique constraint violation, then the execution of the message is halted and an error code is returned to the replica database.

If a fatal error is detected by the business logic of a transaction, the transaction can be aborted and the further execution of the synchronization message halted by putting a rollback request to the bulletin board of the transaction. Remember that the COMMIT WORK and ROLLBACK WORK statements are not allowed in propagated transactions. However, the rollback request can be executed with the following system bulletin board parameters:

SYS_ROLLBACK = 'YES'
SYS_ERROR_CODE = user_defined_error_code
SYS_ERROR_TEXT = user_defined_error_text

The SYS_ROLLBACK parameter is a system-recognized parameter. If the transaction sets the value to YES, then the server automatically rolls back the transaction. The previously committed transactions of the same synchronization message remain committed. What happens to the rest of the synchronization message depends on the mode of the PROPAGATE TRANSACTIONS operation.

Here is a sample scenario:

Let us assume that a transaction detects a violation of the referential integrity of the database; for example, the customer of an order does not exist in the master database. The transaction can put the following parameters on the bulletin board in order to request rollback and return application-specific error codes:

Put_param('SYS_ROLLBACK', 'YES');
Put_param('SYS_ERROR_CODE', '90001');
Put_param('SYS_ERROR_TEXT', 'Referential integrity violation detected');

Note that because the transaction management of transactions is done outside the procedure, directly issuing the ROLLBACK WORK or COMMIT WORK statement inside the procedure is never allowed.

If an error occurs during transaction propagation, the default behavior is that the server stops processing the message and aborts the current transaction. Any previous transactions in the message remain in effect. This means that you might end up with just part of a message executed.

solidDB supports three error-handling modes for propagated messages.

▪ IGNORE_ERRORS - This option means that if an error occurs, the transaction is aborted. Execution continues with the next transaction. In other words, an error does not abort the entire message.

▪ LOG_ERRORS - Like IGNORE_ERRORS, this option means that if an error occurs, the transaction is aborted, and execution continues with the next transaction. In addition, failed transaction statements are saved in SYS_SYNC_RECEIVED_STMTS system table for later execution or investigation.

▪ FAIL_ERRORS - This option means that if a statement fails, the current transaction is rolled back, and the server does not continue on to process subsequent transactions in the same message. However, any transactions that are already committed are not undone. This is the default error-handling mode for propagation.

There are three ways to specify which of these error-handling modes you want to apply to a particular message or transaction.

If you specify the error-handling mode in the SAVE statement then the specified mode applies only to that saved statement (not the entire transaction or the entire message).

▪ Use a transaction bulletin board parameter to specify the error-handling mode. In this case, the specified mode applies to the current transaction.

▪ The parameter name used for autosave in the master database is SYNC_DEFAULT_PROPAGATE_SAVEMODE

Autosave is used in hierarchies that have more than two levels. If a replica needs to propagate a transaction not to its direct master database, but to a master database above that in the hierarchy, then the replica database can use AUTOSAVE. For example:

▪ Use a MESSAGE APPEND PROPAGATE TRANSACTIONS statement to specify the error behavior, see MESSAGE APPEND. In this case, the specified mode applies to the entire message.

Note Note that an autosave option is not available with this statement. For an autosave option, you can use the SAVE statement, see SAVE.

If error-handling options are specified in both the SAVE and the PROPAGATE TRANSACTIONS statement, then the error-handling options specified in the PROPAGATE TRANSACTIONS statement take precedence.

The failed messages can be examined using the SYNC_FAILED_MESSAGES system view and they can be re-executed from there by using the MESSAGE FROM REPLICA RESTART statement, see MESSAGE FROM REPLICA RESTART.

When a master database is propagating a message, the autosave setting is ignored if the server is not also a replica database. In other words, the autosave setting is ignored for the topmost master in the hierarchy. The setting affects one server only; all servers must set it separately if needed.

The table SYS_SYNC_MASTER_MSGINFO has a new column FAILED_MSG_ID which is part of the primary key. The value is zero for normal messages. The value is MSG_ID if LOG_ERRORS option is ON and any errors exists. Also the SYS_SYNC_RECEIVED_STMTS table has ERRCODE and ERR_STR columns where actual errors are logged.

The autosave option takes effect if defined in the SAVE statement or the master bulletin board.

Note If a replica registration or a publication registration message fails, the hung messages are automatically deleted from both the master and the replica, and the status is reset back to what it was before the message execution. The IGNORE_ERRORS, FAIL_ERRORS, and SAVE_ERRORS flags do not apply to these two types of messages.

If transactions were propagated with the LOG_ERRORS option, the server saves the statements that were in the transaction that failed. These statements can be examined the cause of the problem fixed. The failed statements can then be re-executed by using the MESSAGE FROM REPLICA RESTART statement, see MESSAGE FROM REPLICA RESTART.

If the problem cannot be corrected, or you do not re-execute the transactions that failed, you can delete the entire message by using the MESSAGE FROM REPLICA DELETE statement, see MESSAGE FROM REPLICA DELETE.

Alternatively, you can delete one or more transactions within the message with the MESSAGE DELETE CURRENT TRANSACTION statement, see MESSAGE FROM REPLICA DELETE CURRENT TRANSACTION.

You can create and send a synchronization message from a propagated transaction. Although, in most situations, explicit commits are not allowed in a propagated transaction, they are allowed when creating and sending a synchronization message from inside a propagated transaction. You can execute an explicit COMMIT in the middle of a propagated transaction if the transaction has not executed any DML statements and if the previous statement was one of the following statements: