TO SAVEPOINT Clause

The optional TO SAVEPOINT clause specifies a partial rollback. This clause can restrict the scope of the rollback to the operations of the current savepoint level between the ROLLBACK statement and the specified or default savepoint. If no savepoint is specified after the SAVEPOINT keyword, the rollback ends at the most recently set savepoint within the current savepoint level.

When the ROLLBACK WORK TO SAVEPOINT statement executes successfully, any effects of DDL and DML statements that preceded the savepoint persist, but changes to the schema of the database or to its data values by statements that follow the savepoint are cancelled. Any locks acquired by these cancelled statements persist, but are released at the end of the transaction. Any savepoints between the specified savepoint and the ROLLBACK statement are destroyed, but the savepoint referenced by the ROLLBACK statement (and any savepoints that precede the referenced savepoint) continue to exist. Program control passes to the statement that immediately follows the ROLLBACK statement.

If the TO SAVEPOINT clause is omitted, the ROLLBACK statement rolls back the entire transaction, and all savepoints within the transaction are released.

If the specified savepoint does not exist in the current transaction, the database server issues an exception.

The TO SAVEPOINT clause is not valid in a ROLLBACK statement that immediately follows the TRUNCATE statement. In this case, the attempted partial rollback fails with an error. To cancel uncommitted changes that the TRUNCATE statement has made to a table, issue ROLLBACK WORK as the next statement, but with no TO SAVEPOINT clause.

The following program fragment rolls back part of the current transaction to a savepoint called pt109:
BEGIN WORK;
DROP TABLE tab03;
CREATE TABLE tab03 (col1 CHAR(24), col2 DATE);
SAVEPOINT pt108;
...
INSERT INTO  tab03 VALUES ('First day of autumn', '09/23/2012');
SAVEPOINT pt109;
...
DELETE FROM tab03 WHERE col2 < '12/09/2009';
SAVEPOINT pt110;
...
ROLLBACK TO SAVEPOINT pt109;
The ROLLBACK statement in this example has these effects:
  • Cancels the DML operation that deleted any rows with col2 date values earlier than December 9, 2009.
  • Releases savepoint pt110, and any other savepoints between pt109 and the ROLLBACK statement.
  • Cancels any other changes to the database by operations that follow savepoint pt109 in the lexical order of SQL statements within the current transaction.
Savepoint pt108, however, is not released, because it was set earlier than pt109 in the transaction. Not cancelled by this partial rollback are the effects of any uncommitted DDL or DML operations of the transaction before savepoint pt109 was set, including the creation of table tab03 and the INSERT operation that added a row to that table. These persist after the partial rollback, pending the possibility of another partial rollback to a savepoint, and the eventual commitment or rollback of the entire transaction.

Related Statements

Related statements: BEGIN WORK statement, COMMIT WORK statement, RELEASE SAVEPOINT statement, and SAVEPOINT statement.

For a discussion of transactions and ROLLBACK WORK, see the HCL OneDB™ Guide to SQL: Tutorial.