SAVEPOINT statement

Use the SAVEPOINT statement to set a savepoint within a transaction.

Invocation

This statement can be imbedded in an application program (including a procedure) or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

Specifies the name of a savepoint. The specified savepoint-name cannot begin with 'SYS' (SQLSTATE 42939). If a savepoint by this name has already been defined as UNIQUE within this savepoint level, an error is returned (SQLSTATE 3B501).
UNIQUE: Specifies that the application does not intend to reuse this savepoint name while the savepoint is active within the current savepoint level. If savepoint-name already exists within this savepoint level, an error is returned (SQLSTATE 3B501).
ON ROLLBACK RETAIN CURSORS: Specifies system behavior upon rollback to this savepoint with respect to open cursor statements processed after the SAVEPOINT statement. This clause indicates that, whenever possible, the cursors are unaffected by a rollback to savepoint operation. For situations where the cursors are affected by the rollback to savepoint, see ROLLBACK.
ON ROLLBACK RETAIN LOCKS: Specifies system behavior upon rollback to this savepoint with respect to locks acquired after the setting of the savepoint. Locks acquired since the savepoint are not tracked, and are not rolled back (released) upon rollback to the savepoint.

Rules

Savepoint-related statements must not be used within trigger definitions (SQLSTATE 42987).
A new savepoint level starts when one of the following events occurs:
- A new unit of work (UOW) starts.
- A procedure defined with the NEW SAVEPOINT LEVEL clause is called.
- An atomic compound SQL statement starts.
A savepoint level ends when the event that caused its creation is finished or removed. When a savepoint level ends, all savepoints contained within it are released. Any open cursors, DDL actions, or data modifications are inherited by the parent savepoint level (that is, the savepoint level within which the one that just ended was created), and are subject to any savepoint-related statements issued against the parent savepoint level.
The following rules apply to actions within a savepoint level:
- Savepoints can only be referenced within the savepoint level in which they are established. You cannot release, destroy, or roll back to a savepoint established outside of the current savepoint level.
- All active savepoints established within the current savepoint level are automatically released when the savepoint level ends.
- The uniqueness of savepoint names is only enforced within the current savepoint level. The names of savepoints that are active in other savepoint levels can be reused in the current savepoint level without affecting those savepoints in other savepoint levels.

Notes

Once a SAVEPOINT statement has been issued, insert, update, or delete operations on nicknames are not allowed.
Omitting the UNIQUE clause specifies that savepoint-name can be reused within the savepoint level by another savepoint. If a savepoint of the same name already exists within the savepoint level, the existing savepoint is destroyed and a new savepoint with the same name is created at the current point in processing. The new savepoint is considered to be the last savepoint established by the application. Note that the destruction of a savepoint through the reuse of its name by another savepoint simply destroys that one savepoint and does not release any savepoints established after the destroyed savepoint. These subsequent savepoints can only be released by means of the RELEASE SAVEPOINT statement, which releases the named savepoint and all savepoints established after the named savepoint.
If the UNIQUE clause is specified, savepoint-name can only be reused after an existing savepoint with the same name has been released.
Within a savepoint, if a utility, SQL statement, or database command performs intermittent commits during processing, the savepoint will be implicitly released.
If the SET INTEGRITY statement is rolled back within the savepoint, dynamically prepared statement names are still valid, although the statement might be implicitly prepared again.
If inserts are buffered (that is, the application was precompiled with the INSERT BUF option), the buffer will be flushed when SAVEPOINT, ROLLBACK, or RELEASE TO SAVEPOINT statements are issued.

Example

Perform a rollback operation for nested savepoints. First, create a table named DEPARTMENT. Insert a row before starting SAVEPOINT1; insert another row and start SAVEPOINT2; then, insert a third row and start SAVEPOINT3.

   CREATE TABLE DEPARTMENT (
     DEPTNO   CHAR(6),
     DEPTNAME VARCHAR(20),
     MGRNO    INTEGER)

     INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301)

     SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520)

     SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430)

     SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150)

At this point, the DEPARTMENT table exists with rows A20, B30, C40, and R50. If you now issue:

   ROLLBACK TO SAVEPOINT SAVEPOINT3

row R50 is no longer in the DEPARTMENT table. If you then issue:

   ROLLBACK TO SAVEPOINT SAVEPOINT1

the DEPARTMENT table still exists, but the rows inserted since SAVEPOINT1 was established (B30 and C40) are no longer in the table.