Rollback to Savepoint Command
The rollback to savepoint command issues
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.
rollback to savepoint 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.
In contrast to the
Rollback command, the
Rollback to Savepoint command rolls back but of your work, but does
not start a new transaction.
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: