ROLLBACK TO SAVEPOINT

Roll back the current transaction to the savepoint.

Summary

ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT]
                savepoint_name

describe

This command will roll back all commands executed after the savepoint is established. This save point remains valid and can be rolled back later if needed.

ROLLBACK TO SAVEPOINT implicitly destroys all save points created after the specified save point.

Parameters

WORK

TRANSACTION

Optional keywords. They have no effect.

savepoint_name

The name of the savepoint to be rolled back.

Note

Use RELEASE SAVEPOINT to destroy a savepoint without discarding the effect of the commands executed after the savepoint was created.

It is wrong to specify a savepoint name that has not been created yet.

A cursor has some non-transactional behavior relative to a save point. When the save point is rolled back, all cursors opened inside the save point are closed. If the previously opened cursor is affected by the FETCH command within the save point, the save point is then rolled back, the cursor will remain at the position pointed by the FETCH (i.e., the cursor movement caused by the FETCH will not roll back). Close the cursor will not be undoed by rolling back. However, if other side effects caused by cursor's query (such as side effects of the volatile function called by the query) occur during the save point and then rolled back, these side effects are also rolled back. Since the cursor that has its execution caused the transaction to be aborted is in an unexecution state, although the transaction can be restored using ROLLBACK TO SAVEPOINT, the cursor cannot be used anymore.

Example

To undo the effect of commands executed after establishing my_savepoint:

ROLLBACK TO SAVEPOINT my_savepoint;

Cursor location is not affected by save point rollback:

BEGIN;
DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
SAVEPOINT foo;
FETCH 1 FROM foo;
column
----------
        1
ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;
column
----------
        2
COMMIT;

compatibility

The SQL standard specifies the keyword SAVEPOINT is required, but the YMatrix database (and Oracle) allows it to be omitted. SQL only allows WORK after ROLLBACK, and does not allow TRANSACTION as an interfering word. Additionally, SQL has an optional clause AND [NO] CHAIN, which is not currently supported by the YMatrix database. In other aspects, this command complies with SQL standards.