8.2 Working with Savepoints

X DevAPI supports savepoints, which enable you to set a named point within a transaction that you can revert to. By setting savepoints within a transaction, you can later use the rollback functionality to undo any statements issued after setting the savepoint. Savepoints can be released if you no longer require them. This section documents how to work with savepoints in X DevAPI. See SAVEPOINT for background information.

Setting a Savepoint

Savepoints are identified by a string name. The string can contain any character allowed for an identifier. To create a savepoint, use the session.setSavepoint() operation, which maps to the SQL statement SAVEPOINT name;. If you do not specify a name, one is automatically generated. For example by issuing:

session.setSavepoint()

a transaction savepoint is created with an automatically generated name and a string is returned with the name of the savepoint. This name can be used with the session.rollbackTo() or session.releaseSavepoint() operations. The session.setSavepoint() operation can be called multiple times within a session and each time a unique savepoint name is generated.

It is also possible to manually define the name of the savepoint by passing in a string name. For example issuing:

session.setSavepoint('name')

results in a transaction savepoint with the specified name, which is returned by the operation as a string. The session.setSavepoint('name') operation can be called multiple times in this way, and if the name has already been used for a savepoint then the previous savepoint is is deleted and a new one is set.

Rolling Back to a Savepoint

When a session has transaction savepoints, you can undo any subsequent transactions using the session.rollbackTo() operation, which maps to the ROLLBACK TO name statement. For example, issuing:

session.rollbackTo('name')

rolls back to the transaction savepoint name. This operation succeeds as long as the given savepoint has not been released. Rolling back to a savepoint which was created prior to other savepoints results in the subsequent savepoints being either released or rolled back. For example:

session.startTransaction()
(some data modifications occur...)

session.setSavepoint('point1')     <---- succeeds
(some data modifications occur...)

session.setSavepoint('point2')     <---- succeeds
(some data modifications occur...)

session.rollbackTo('point1')       <---- succeeds
session.rollbackTo('point1')       <---- still succeeds, but position stays the same
session.rollbackTo('point2')       <---- generates an error because lines above already cleared point2
session.rollbackTo('point1')       <---- still succeeds

Releasing a Savepoint

To cancel a savepoint, for example when it is no longer needed, use releaseSavepoint() and pass in the name of the savepoint you want to release. For example, issuing:

session.releaseSavepoint('name')

releases the savepoint name.

Savepoints and Implicit Transaction Behavior

The exact behavior of savepoints is defined by the server, and specifically how autocommit is configured. See autocommit, Commit, and Rollback.

For example, consider the following statements with no explicit BEGIN, session.startTransaction() or similar call:

session.setSavepoint('testsavepoint');
session.releaseSavepoint('testsavepoint');

If autocommit mode is enabled on the server, these statements result in an error because the savepoint named testsavepoint does not exist. This is because the call to session.setSavepoint() creates a transaction, then the savepoint and directly commits it. The result is that savepoint does not exist by the time the call to releaseSavepoint() is issued, which is instead in its own transaction. In this case, for the savepoint to survive you need to start an explicit transaction block first.