ROLLBACK (SQL)

Rolls back a transaction.

Synopsis

ROLLBACK [WORK]

ROLLBACK TO SAVEPOINT pointname

Arguments

Argument	Description
pointname	The name of an existing savepoint, specified as an identifier. For further details see the “Identifiers” chapter of Using InterSystems SQL.

Description

A ROLLBACK statement rolls back a transaction, undoing work performed but not committed, decrementing the $TLEVEL transaction level counter, and releasing locks. ROLLBACK is used to restore the database to a previous consistent state.

A ROLLBACK rolls back all work completed during the current transaction, resets the $TLEVEL transaction level counter to zero and releases all locks. This restores the database to its state before the beginning of the transaction. ROLLBACK and ROLLBACK WORK are equivalent statements; both versions are supported for compatibility.
A ROLLBACK TO SAVEPOINT pointname rolls back all work done since the specified savepoint and decrements the $TLEVEL transaction level counter by the number of savepoints undone. When all savepoints have been either rolled back or committed and the transaction level counter reset to zero, the transaction is completed. If the specified savepoint does not exist, or has already been rolled back, ROLLBACK issues an SQLCODE -375 error and rolls back the entire current transaction.

A ROLLBACK TO SAVEPOINT must specify a pointname. Failing to do so results in an SQLCODE -301 error.

For details on establishing savepoints, refer to SAVEPOINT.

An SQLCODE -400 error is issued if a transaction operation fails to complete successfully.

Not Rolled Back

The following items are not affected by a ROLLBACK operation:

A roll back does not decrement the IDKey counter for a default class. The IDKey is automatically generated by $INCREMENT (or $SEQUENCE), which maintains a count independent of the SQL transaction.
A roll back does not reverse the creation, modification, or purging of a cached query. These operations are not treated as part of a transaction.
A DDL operation or a Tune Table operation that occur within a transaction may create and run a temporary routine. This temporary routine is treated the same as a Cached Query. That is, the creation, compilation, and deletion of a temporary routine are not treated as part of the transaction. The execution of the temporary routine is considered part of the transaction.

For non-SQL items rolled back or not rolled back, refer to the ObjectScript TROLLBACK command.

Rollback Logging

Messages indicating that a rollback occurred, and errors encountered during the rollback operation are logged in the messages.log file in the MGR directory. You can use the Management Portal System Operation, System Logs, Messages Log option to view messages.log.

Transactions Suspended

The TransactionsSuspended()Opens in a new tab method of the %SYSTEM.ProcessOpens in a new tab class can be used to suspend and resume all current transactions for a process. Suspending transactions suspends journaling of changes. Therefore, if transaction suspension occurred during the current transaction, ROLLBACK cannot roll back any changes made while transactions were suspended; however, ROLLBACK rolls back any changes made during the current transaction that occurred before or after the transaction suspension was in effect.

For further details, refer to Using ObjectScript for Transaction Processing in Using ObjectScript.

ObjectScript Transaction Commands

ObjectScript and SQL transaction commands are fully compatible and interchangeable, with the following exception:

ObjectScript TSTART and SQL START TRANSACTION both start a transaction if no transaction is current. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is preferable to start the transaction with TSTART. If you need compatibility with the SQL standard, use START TRANSACTION.

ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing supplies support for savepoints within transactions.

Examples

The following Embedded SQL example demonstrates how a ROLLBACK restores the transaction level counter ($TLEVEL) to 0, the level immediately prior to the START TRANSACTION:

  &sql(SET TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Set transaction mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION)
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT a)
    WRITE !,"Set Savepoint a, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT b)
    WRITE !,"Set Savepoint b, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
    &sql(SAVEPOINT c)
    WRITE !,"Set Savepoint c, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(ROLLBACK)
    WRITE !,"Rollback transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL

The following Embedded SQL example demonstrates how a ROLLBACK TO SAVEPOINT name restores the transaction level ($TLEVEL) to the level immediately prior to the specified SAVEPOINT:

  &sql(SET TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Set transaction mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION)
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT a)
    WRITE !,"Set Savepoint a, SQLCODE=",SQLCODE
    WRITE !,"Transaction level at a=",$TLEVEL
  &sql(SAVEPOINT b)
    WRITE !,"Set Savepoint b, SQLCODE=",SQLCODE
    WRITE !,"Transaction level at b=",$TLEVEL
  &sql(ROLLBACK TO SAVEPOINT b)
    WRITE !,"Rollback to b, SQLCODE=",SQLCODE
    WRITE !,"Rollback transaction level=",$TLEVEL
  &sql(SAVEPOINT c)
    WRITE !,"Set Savepoint c, SQLCODE=",SQLCODE
    WRITE !,"Transaction level at c=",$TLEVEL
  &sql(SAVEPOINT d)
    WRITE !,"Set Savepoint d, SQLCODE=",SQLCODE
    WRITE !,"Transaction level at d=",$TLEVEL
  &sql(COMMIT)
    WRITE !,"Commit transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL