SAVEPOINT
The SAVEPOINT statement sets a savepoint within a unit of recovery to identify a point in time within the unit of recovery to which relational database changes can be rolled back.
Invocation
This statement can be imbedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
None required.
Syntax
>>-SAVEPOINT--savepoint-name--+--------+------------------------> '-UNIQUE-' (1) >--ON ROLLBACK RETAIN CURSORS-----------------------------------> (1) .-ON ROLLBACK RETAIN LOCKS-----. >--+------------------------------+----------------------------><
- These clauses can be specified in either order.
Description
- savepoint-name
- Names the savepoint. savepoint-name must not begin with 'SYS'.
- UNIQUE
- Specifies
that the application program cannot reuse the savepoint name within
the unit of recovery. An error occurs if a savepoint with the same
name as savepoint-name already exists within
the unit of recovery.
Omitting UNIQUE indicates that the application can reuse the savepoint name within the unit of recovery. If svpt-name identifies a savepoint that already exists within the unit of recovery and the savepoint was not created with the UNIQUE option, the existing savepoint is destroyed and a new savepoint is created. Destroying a savepoint to reuse its name for another savepoint is not the same as releasing the savepoint. Reusing a savepoint name destroys only one savepoint. Releasing a savepoint with the RELEASE SAVEPOINT statement releases the savepoint and all savepoints that have been subsequently set.
- ON ROLLBACK RETAIN CURSORS
- Specifies that any cursors that are opened after the savepoint is set are not tracked, and thus, are not closed upon rollback to the savepoint. Although these cursors remain open after rollback to the savepoint, they might not be usable. For example, if rolling back to the savepoint causes the insertion of a row on which the cursor is positioned to be rolled back, using the cursor to update or delete the row results in an error.
- ON ROLLBACK RETAIN LOCKS
- Specifies that any locks that are acquired after the savepoint is set are not tracked, and thus, are not released on rollback to the savepoint. ON ROLLBACK RETAIN LOCKS is the default behavior.
Example
SAVEPOINT A ON ROLLBACK RETAIN CURSORS;
⋮
SAVEPOINT B UNIQUE ON ROLLBACK RETAIN CURSORS;
⋮
SAVEPOINT A ON ROLLBACK RETAIN CURSORS;