SAVEPOINT statement

Use the SAVEPOINT statement to declare the name of a new savepoint within the current SQL transaction, and to set the position of the new savepoint within the lexical order of SQL statements within the transaction. The SAVEPOINT statement is compliant with the ANSI/ISO standard for SQL.

Syntax

^{(explicit id savept202)
savept202} SAVEPOINTsavepoint [ ^[]UNIQUE ]


Element	Description	Restrictions	Syntax
`savepoint`	Name declared here for the new savepoint	Cannot be the name of an existing unique savepoint in the same savepoint level	Identifier

Usage

You can use the SAVEPOINT statement in SQL transactions to support error handling with DB-Access and in SPL, C, and Java™ routines. You can define savepoint to partition a single complex transaction into smaller logical subsets of its component SQL statements. Within that transaction, the subset of statements that follow each savepoint can be rolled back more efficiently than if you had used multiple COMMIT WORK and ROLLBACK WORK statements in multiple transactions.

The SAVEPOINT statement sets the specified savepoint at the current position in the lexical order of statements within the current transaction. After the SAVEPOINT statement executes successfully, subsequent ROLLBACK TO SAVEPOINT statements that reference this savepoint can cancel any uncommitted changes to the database from logged DML or DDL operations in the current transaction that follow the new savepoint but precede the ROLLBACK TO SAVEPOINT statement.

If an existing savepoint in the same transaction has the same name that the SAVEPOINT statement specifies, the existing savepoint is destroyed, unless one of the following conditions is true:

The existing savepoint was set in a different savepoint level.
The existing savepoint name was declared with the UNIQUE keyword option. In this case, the SAVEPOINT statement fails with an error, unless the existing UNIQUE savepoint was set in a different savepoint level.

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 specified savepoint and all savepoints that have been subsequently set.

The UNIQUE option

This optional keyword specifies that the application does not intend to reuse the name of this savepoint in another SAVEPOINT statement while this savepoint is active within the current savepoint level.

If a savepoint already exists that was set with the same name and with the UNIQUE keyword within the current savepoint level, the SAVEPOINT statement fails with an error, and the existing savepoint is not destroyed.

Savepoint levels

Informix® supports the construct of nested savepoint levels. A single SQL transaction can have multiple savepoint levels. New savepoint levels are automatically created for the duration of execution of an SPL routine or an external UDR. Recursive calls to the same SPL routine or UDR also increment the savepoint level of the current transaction.

A savepoint level ends when the UDR in which it was created finishes execution. When a savepoint level ends, all savepoints within it are automatically released. Any DDL or DML modifications are inherited by the parent savepoint level (that is, by the savepoint level within which the one that just ended was created), and are subject to any savepoint-related statements that are 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.
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.

Savepoints in distributed SQL transactions

Savepoints are valid in cross-database distributed SQL transactions of a single Informix® instance that supports transactions if all of the participating databases support transaction logging. Savepoints are also supported in cross-server SQL transactions, including operations in high-availability clusters, if all of the participating Informix® instances support savepoints, and all of the databases that are accessed in the transaction use logging.

If any of the participating database servers in a cross-server transaction does not support savepoints, however, and a connection is established between a coordinator that can support savepoints and a subordinate server that cannot, any ROLLBACK TO SAVEPOINT statement within the distributed transaction fails with an error.

Persistence of savepoints

Savepoints are position markers within SQL transactions, not database objects. An existing savepoint S is destroyed by any of the following events within the same transaction:

A COMMIT WORK or ROLLBACK WORK (without the TO SAVEPOINT clause) statement is executed.
A RELEASE SAVEPOINT statement is executed that specifies S in the same savepoint level.
A ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT statement is executed that specifies a savepoint that was established earlier than S in the same savepoint level.
A SAVEPOINT statement specifies the same name as S in the same savepoint level, and S was not created with the UNIQUE keyword.

Restrictions on savepoints

Savepoints and savepoint levels are not supported in the following contexts:

in databases that do not support transaction logging
in triggered actions
in XA global transactions
in applications or UDRs where the AUTOCOMMIT connection attribute is enabled.

In addition, the SAVEPOINT statement (like the RELEASE SAVEPOINT and ROLLBACK WORK TO SAVEPOINT statements) is not valid in UDRs that are invoked within DML statements, as in the following example:

SELECT first_1 foo() FROM systables;

Here the foo( ) routine cannot set a savepoint.

Related Statements

¹ Informix® extension