Rollback to Savepoint Command

The rollback to savepoint command issues a java.sql.Connection.rollback request, which has been overloaded to work with a savepoint within the current transaction.

When you rollback a transaction to a savepoint, that savepoint and any others created after it within the transaction are automatically released.

Syntax

rollback to savepoint identifier;

identifier

The name of the savepoint to which the transaction should be rolled back: all savepoints up to and including this one are rolled back.

Usage Notes

In contrast to the  Rollback command, the Rollback to Savepoint command rolls back but of your work, but does not start a new transaction.

Examples

First we’ll create a table, turn autocommit off, and insert some data into the table. We then create a savepoint, and verify the contents of our table:

splice> CREATE TABLE myTbl(i int);
0 rows inserted/updated/deleted
splice> AUTOCOMMIT OFF;
splice> INSERT INTO myTbl VALUES 1,2,3;
3 rows inserted/updated/deleted
splice> SAVEPOINT savept1;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3

3 rows selected

Next we add new values to the table and again verify its contents:

splice> INSERT INTO myTbl VALUES 4,5;
2 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3
4
5
5 rows selected

Now we roll back to our savepoint, and verify that the rollback worked:

splice> ROLLBACK TO SAVEPOINT savept1;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3

3 rows selected

And finally, we commit the transaction:

COMMIT;

See Also