ROLLBACK

ROLLBACK returns the database to the state it was in before the current transaction began. This statement releases the locks acquired since the last SAVEPOINT or START TRANSACTION.

The ROLLBACK TO SAVEPOINT statement rolls back the transaction to the specified savepoint.

Syntax

ROLLBACK [ WORK ] [ TO SAVEPOINT savepoint-name ]

savepoint-name ::= user-defined-name

Remarks

ROLLBACK, SAVEPOINT, and RELEASE are supported at the session level (outside of stored procedures) only if AUTOCOMMIT is off. Otherwise, ROLLBACK, SAVEPOINT, and RELEASE must be used within a stored procedure.

Any committed statements within a stored procedure are controlled by the outermost transaction of the calling ODBC application. This means that, depending on the AUTOCOMMIT mode specified on SQLSetConnectOption, calling the stored procedure externally from an ODBC application performs one of two actions. It either commits automatically (AUTOCOMMIT on, the default) or waits for you to call SQLTransact with SQL_COMMIT or SQL_ROLLBACK (when AUTOCOMMIT is set to off).

In the case of nested transactions, ROLLBACK rolls back to the nearest START TRANSACTION. For example, if transactions are nested five levels, then five ROLLBACK statements are needed to undo all of the transactions. A transaction is either committed or rolled back, but not both. That is, you cannot roll back a committed transaction.

Examples

The following statement undoes the changes made to the database since the beginning of a transaction.

ROLLBACK WORK;

The following statement undoes the changes made to the database since the last savepoint, named Svpt1.

ROLLBACK TO SAVEPOINT Svpt1;