Deadlock and log file full errors cause the system to roll back the entire transaction. By default, other errors only roll back the statement that caused the error. If you want to roll back an entire transaction whenever any database error occurs, you must explicitly issue the rollback statement.

If you want the entire transaction rolled back in case of error, instead of writing code that handles each statement separately, you can set transaction management for the entire session using the SQL set session with on_error statement. In addition to requiring less code, this statement requires fewer writes to the log file.

For example, to ensure that every transaction in the Videos application is rolled back in the event of any database error, the application's top frame (the main_control frame) includes the following statement:

To turn off this option, causing rollback of only the current database statement, issue the following statement:

For more information about determining the status of the current transaction, see How You Can Use the Inquire_sql Statement.