Caché SQL Reference
ROLLBACK
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Rolls back a transaction.
Synopsis
ROLLBACK [WORK] 
ROLLBACK TO SAVEPOINT pointname
Arguments
pointname The name of an existing savepoint, specified as an identifier. For further details see the “Identifiers” chapter of Using Caché 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 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:
For non-SQL items rolled back or not rolled back, refer to the Caché ObjectScript TROLLBACK command.
Rollback Logging
Messages indicating that a rollback occurred, and errors encountered during the rollback operation are logged in the cconsole.log file in the MGR directory. You can use the Management Portal System Operation option to view cconsole.log: [Home] > [System Logs] > [View Console Log].
Caché ObjectScript Transaction Commands
Caché 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.
Caché 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
 
See Also