Savepoint Command

The savepoint command issues a java.sql.Connection.setSavepoint request, which sets a savepoint within the current transaction.

Savepoints are only useful when autocommit is off.

You can define multiple savepoints within a transaction.

Syntax

savepoint identifier;

identifier

An identifier name for the string.

Example

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 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