Release Savepoint Command

The release savepoint command issues a java.sql.Connection.releaseSavepoint request, which releases a savepoint within the current transaction. Once a savepoint has been released, attempting to reference it in a rollback operation will cause an SQLException to be thrown.

When you commit a transaction, any savepoints created in that transaction are automatically released and invalidated when the transaction is committed or the entire transaction is rolled back.

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

Syntax

release savepoint identifier;

identifier

The name of the savepoint to release.

Examples

Example

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 release our original savepoint, insert a few more values, and create a new savepoint, savept2.

splice> RELEASE SAVEPOINT savept1;
0 rows inserted/updated/deletedsplice> INSERT INTO myTbl VALUES 6,7;
2 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3
4
5
6
7

7 rows selected
splice> SAVEPOINT savept2;
0 rows inserted/updated/deleted

We again insert data into the table, display its contents, and then do a rollback:

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

9 rows selected
splice> ROLLBACK TO SAVEPOINT savept1;
ERROR 3B001: Savepoint SAVEPT1 does not  exist or is not active in the current transaction.
splice> ROLLBACK TO SAVEPOINT savept2;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3
4
5
6
7

7 rows selected

And finally, we commit the transaction:

COMMIT;

See Also