Savepoints

 

A savepoint is a named entity that represents the state of data and schemas at a particular point within a unit of work. You can create savepoints within a transaction. If the transaction rolls back, changes are undone to the specified savepoint, rather than to the beginning of the transaction.

You can set a savepoint using the SAVEPOINT SQL statement. For example, create a savepoint called STOP_HERE:

SAVEPOINT STOP_HERE ON ROLLBACK RETAIN CURSORS

Program logic in the application dictates whether the savepoint name is reused as the application progresses, or if the savepoint name denotes a unique milestone in the application that should not be reused.

If the savepoint represents a unique milestone that should not be moved with another SAVEPOINT statement, specify the UNIQUE keyword. This prevents the accidental reuse of the name that can occur by invoking a stored procedure that uses the identical savepoint name in a SAVEPOINT statement. However, if the SAVEPOINT statement is used in a loop, then the UNIQUE keyword should not be used. The following SQL statement sets a unique savepoint named START_OVER.

SAVEPOINT START_OVER UNIQUE
       ON ROLLBACK RETAIN CURSORS

To rollback to a savepoint, use the ROLLBACK statement with the TO SAVEPOINT clause. The following example illustrates using the SAVEPOINT and ROLLBACK TO SAVEPOINT statements:

This application logic books airline reservations on a preferred date, then books hotel reservations. If the hotel is unavailable, it rolls back the airline reservations and then repeats the process for another date. Up to 3 dates are tried.

got_reservations =0;
EXEC SQL SAVEPOINT START_OVER UNIQUE ON ROLLBACK RETAIN CURSORS;

if (SQLCODE != 0) return;

for (i=0; i<3 & got_reservations == 0; ++i)
{
  Book_Air(dates(i), ok);
  if (ok)
  {
    Book_Hotel(dates(i), ok);
    if (ok) got_reservations = 1;
    else     {
      EXEC SQL ROLLBACK TO SAVEPOINT START_OVER;
      if (SQLCODE != 0) return;
    }
  }
}

EXEC SQL RELEASE SAVEPOINT START_OVER;

Savepoints are released using the RELEASE SAVEPOINT statement. If a RELEASE SAVEPOINT statement is not used to explicitly release a savepoint, it is released at the end of the current savepoint level or at the end of the transaction. The following statement releases savepoint START_OVER.

RELEASE SAVEPOINT START_OVER

Savepoints are released when the transaction is committed or rolled back. Once the savepoint name is released, a rollback to the savepoint name is no longer possible. The COMMIT or ROLLBACK statement releases all savepoint names established within a transactions. Since all savepoint names are released within the transaction, all savepoint names can be reused following a commit or rollback.

Savepoints are scoped to a single connection only. Once a savepoint is established, it is not distributed to all remote databases that the application connects to. The savepoint only applies to the current database that the application is connected to when the savepoint is established.

A single statement can implicitly or explicitly invoke a user-defined function, trigger, or stored procedure. This is known as nesting. In some cases when a new nesting level is initiated, a new savepoint level is also initiated. A new savepoint level isolates the invoking application from any savepoint activity by the lower level routine or trigger.

Savepoints can only be referenced within the same savepoint level (or scope) in which they are defined. A ROLLBACK TO SAVEPOINT statement cannot be used to rollback to a savepoint established outside the current savepoint level. Likewise, a RELEASE SAVEPOINT statement cannot be used to release a savepoint established outside the current savepoint level. The following table summarizes when savepoint levels are initiated and terminated:

A new savepoint level is initiated when:	That savepoint level ends when:
A new unit of work is started	COMMIT or ROLLBACK is issued
A trigger is invoked	The trigger completes
A user-defined function is invoked	The user-defined function returns to the invoker
A stored procedure is invoked, and that stored procedure was created with the NEW SAVEPOINT LEVEL clause	The stored procedure returns to the caller
There is a BEGIN for an ATOMIC compound SQL statement	There is an END for an ATOMIC compound statement

A savepoint that is established in a savepoint level is implicitly released when that savepoint level is terminated.

 

Parent topic:

Data integrity