SQL ROLLBACK TRANSACTION Statement

< > Query Statements Advanced Statements Example Flow Chart Table of Contents

The SQL ROLLBACK TRANSACTION statement ends a transaction by rolling back a transaction that has been started.

ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection on which to roll back the existing active transaction. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

A transaction is started with the SQL START TRANSACTION statement. Until a transaction is started, all query statements are executed in auto-commit mode. After a transaction is committed or rolled back, another SQL START TRANSACTION statement must be executed to start a new transaction; otherwise, InstantSQL will revert to auto-commit mode when an SQL statement is executed with the SQL START QUERY statement outside of a transaction for the connection.

An SQL ROLLBACK TRANSACTION statement has no effect if a transaction has not been started with the SQL START TRANSACTION statement for the same connection.

Transactions are a connection level feature. Transactions may be used on one connection and not used on another connection, even when the two connections are to the same data source. However, a logical transaction, from the point of view of the application, should not be spread across multiple connections because there is no way to guarantee commit or rollback of the entire transaction.

After a successful SQL ROLLBACK TRANSACTION statement, the data source has been rolled back to its state at the time the transaction started, assuming no other concurrent transactions are executing. That is, it is as if all the query statements executed within the transaction had never been executed. Also, any locks being held to maintain the transaction isolation level are released.

Drivers or data sources that do not support transactions are effectively always in auto-commit mode, and therefore, always return success for SQL ROLLBACK TRANSACTION. Such drivers and data sources do not actually roll back transactions when requested to do so.

The driver may close any open cursors on the connection when the transaction is rolled back. The SQL DESCRIBE CONNECTION statement returns the data item sql-ConCursorRollbackBehavior, which provides information regarding cursor rollback behavior for the connection.

SQL ROLLBACK TRANSACTION Statement Example:

SQL ROLLBACK TRANSACTION
sql-ConnectionHandle.