Session With On_error
The SET SESSION WITH ON_ERROR option specifies how transaction errors are handled in the current session.
To direct the DBMS Server to rollback the current transaction if an error occurs, specify ROLLBACK TRANSACTION.
To direct the DBMS Server to rollback only the current statement, specify ROLLBACK statement. This is the default.
To determine the current status of transaction error handling, issue the SELECT DBMSINFO('ON_ERROR_STATE') statement.
Specifying ROLLBACK TRANSACTION reduces logging overhead and can help performance. The performance gain is offset by the fact that, if an error occurs, the entire transaction is rolled back. The following errors always roll back the current transaction regardless of the current transaction error-handling setting:
• Deadlock
• Forced abort
• Lock quota exceeded
To determine if a transaction was aborted as the result of a database statement error, issue the SELECT DBMSINFO('TRANSACTION_STATE') statement. If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.
The SET SESSION WITH ON_ERROR statement cannot be issued from within a database procedure or multi-statement transaction.
Note: SQL syntax errors (including most messages beginning with E_US) do not cause a rollback. Only errors that occur during execution of the SQL statement cause a rollback.