SQL SAVEPOINT Statements (savepoint/rollback_to_/release_savepoint_statement)

Within a transaction, subtransactions can be defined to make a series of database operations within a transaction appear as a unit with regard to modifications to the database.

The SAVEPOINT statement is used to define an SQL savepoint – and therefore the start of a subtransaction within a transaction – and to assign this SQL savepoint a name. A subsequent ROLLBACK TO statement with the SQL savepoint name reverses any modifications that have been made in the meantime, without affecting the database operations that were executed within the transaction before the start of this subtransaction. The SQL savepoint is also deleted in the process. It is also possible to delete SQL savepoints using the RELEASE SAVEPOINT statement.

Caution Caution

An SQL savepoint defined by a SAVEPOINT statement is not a savepoint. More information about savepoints: Concepts of the Database System, Savepoint

End of the caution.

Structure

Syntax Syntax

<savepoint_statement> ::=
  SAVEPOINT <sql_savepoint_name>

<rollback_to_statement> ::=
  ROLLBACK TO [SAVEPOINT] <sql_savepoint_name>

<release_savepoint_statement> ::=
  RELEASE SAVEPOINT <sql_savepoint_name>

<sql_savepoint_name> ::=
  <identifier>

End of the code.

SAVEPOINT Statement

The SAVEPOINT statement opens a subtransaction. This means the database system records the current position (SQL savepoint) in the transaction and assigns it the name sql_savepoint_name. The SQL savepoint is identified as active. Any sequence of SQL statements can then follow within one transaction. The sequence of SQL statements can contain other SAVEPOINT statements; however, no more than 50 SQL savepoints can be active in one transaction.

Names of SQL savepoints in a transaction must be different. If an SQL savepoint name is assigned twice within a transaction, the SQL savepoint in this transaction defined by the first SAVEPOINT statement becomes inactive.

ROLLBACK TO Statement

A ROLLBACK TO statement reverses all database modifications made in the active transaction following the SAVEPOINT statement.

The SQL savepoint specified in the ROLLBACK TO statement must be an active SQL savepoint in the transaction. All SQL savepoints created after this SQL savepoint are inactive. All SQL savepoints created before this SQL savepoint retain the same state.

The specified SQL savepoint also remains active after the ROLLBACK TO statement has been executed. This means the ROLLBACK TO statement can be executed in the same transaction more than once by specifying the same SQL savepoint name.

RELEASE SAVEPOINT Statement

An SQL savepoint can be deactivated by a RELEASE SAVEPOINT. The SQL savepoint specified in the RELEASE SAVEPOINT statement must be an active SQL savepoint in the transaction.

All SQL savepoints activated after this SQL savepoint are also deactivated. All SQL savepoints created before this SQL savepoint retain the same state.

Further Explanations

The SQL SAVEPOINT statements do not affect locks assigned to the transaction. In particular, these SQL statements do not release any locks. Locks are only released by COMMIT or ROLLBACK.

Subtransactions are particularly useful for keeping the effects of database procedures atomic in the sense that they either fulfill all their tasks or else have no effect. To this end, a SAVEPOINT statement is specified initially. If the subroutine could not fulfill its tasks, all the modifications made by the database procedure can be reversed by a ROLLBACK TO statement.

The COMMIT statement and the ROLLBACK statement close any open subtransactions implicitly.

More Information

Transactions

SUBTRANS Statement (subtrans_statement)