Was this helpful?
Session with on_error Option
The set session with on_error statement enables you to specify how transaction errors are handled in the current session. To direct the DBMS Server to roll back the effects of the entire current transaction if an error occurs, specify rollback transaction. To rollback only the current statement (the default), specify rollback statement. To determine the current status of transaction error handling, issue the retrieve (x=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, not the single statement that caused the error.
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 retrieve (x=dbmsinfo("transaction_state")) statement. If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.
You cannot issue the set session with on_error statement from within a database procedure or multi-query transaction.
Last modified date: 08/28/2024