Skip to main content
Previous sectionNext section

SAVEPOINT

Marks a point within a transaction.

Synopsis

SAVEPOINT pointname

Arguments

Argument Description
pointname The name of the savepoint, specified as an identifier. For further details see the “Identifiers” chapter of Using InterSystems SQL.

Description

A SAVEPOINT statement marks a point within a transaction. Establishing a savepoint enables you to perform transaction roll back to the savepoint, undoing all work done and releasing all locks acquired during that period. In a long-running transaction, or a transaction with internal control structure, it is often desirable to be able to roll back part of the transaction without undoing all work submitted during the transaction.

The establishment of a savepoint increments the $TLEVEL transaction level counter. Rolling back to a savepoint decrements the $TLEVEL transaction level counter to its value immediately prior to the savepoint. You can establish up to 255 savepoints within a transaction. Exceeding this number of savepoints results in an SQLCODE -400 fatal error, a <TRANSACTION LEVEL> exception caught during SQL execution. The Terminal prompt displays the current transaction level as a TLn: prefix to the prompt, where n is an integer between 1 and 255 representing the current $TLEVEL count.

Each savepoint is associated with an savepoint name, a unique identifier. Savepoint names are not case-sensitive. A savepoint name can be a delimited identifier.

  • If you specify a SAVEPOINT with no pointname, or with a pointname that is not a valid identifier or is an SQL Reserved Word, a runtime SQLCODE -301 error is issued.

  • If you specify a SAVEPOINT with a pointname that begins with “SYS”, a runtime SQLCODE -302 error is issued. These savepoint names are reserved.

Savepoint names are not case-sensitive; therefore resetpt, ResetPt and "RESETPT" are the same pointname. This duplication is detected during ROLLBACK TO SAVEPOINT, not during SAVEPOINT. When you specify a SAVEPOINT statement with a duplicate pointname, InterSystems IRIS increments the transaction level counter, just as if the pointname was unique. However, the most recent pointname overwrites all prior duplicate values in the table of savepoint names. Therefore, when you specify a ROLLBACK TO SAVEPOINT pointname, InterSystems IRIS rolls back to the most recently established SAVEPOINT with that pointname, and decrements the transaction level counter appropriately. However, if you again specify a ROLLBACK TO SAVEPOINT pointname with the same name, an SQLCODE -375 error is generated, with the %msg: Cannot ROLLBACK to unestablished savepoint 'name', the full transaction is rolled back and the $TLEVEL count reverts to 0.

Using Savepoints

The SAVEPOINT statement is supported for Embedded SQL, Dynamic SQL, ODBC, and JDBC. In JDBC, connection.setSavepoint(pointname) sets a savepoint, and connection.rollback(pointname) rolls back to the named savepoint.

If savepoints have been established:

  • A ROLLBACK TO SAVEPOINT pointname rolls back work done since the specified savepoint, deletes that savepoint and all intermediate savepoints, and decrements the $TLEVEL transaction level counter by the number of savepoints deleted. If pointname does not exist, or has already been rolled back, this command rolls back the entire transaction, resets $TLEVEL to 0, and releases all locks.

  • A ROLLBACK rolls back all work done during the current transaction, rolling back the work done since START TRANSACTION. It resets the $TLEVEL transaction level counter to zero and releases all locks. Note that a generic ROLLBACK ignores savepoints.

  • A COMMIT commits all work done during the current transaction. It resets the $TLEVEL transaction level counter to zero and releases all locks. Note that a COMMIT ignores savepoints.

Issuing a second START TRANSACTION within a transaction has no effect on savepoints or the $TLEVEL transaction level counter.

An SQLCODE -400 error is issued if a transaction operation fails to complete successfully.

Examples

The following embedded SQL example creates a transaction with two savepoints:

  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(START TRANSACTION)
  &sql(DELETE FROM Sample.Person WHERE Name=NULL)
  IF SQLCODE=100 { WRITE !,"No null name records to delete" }
  ELSEIF SQLCODE'=0 {&sql(ROLLBACK)}
  ELSE {WRITE !,%ROWCOUNT," null name records deleted"}
    &sql(SAVEPOINT svpt_age1)
    &sql(DELETE FROM Sample.Person WHERE Age=NULL)
    IF SQLCODE=100 { WRITE !,"No null age records to delete" }
    ELSEIF SQLCODE'=0 {&sql(ROLLBACK TO SAVEPOINT svpt_age1)}
    ELSE {WRITE !,%ROWCOUNT," null age records deleted"}
      &sql(SAVEPOINT svpt_age2)
      &sql(DELETE FROM Sample.Person WHERE Age>65)
      IF SQLCODE=0 { &sql(COMMIT)}
      ELSEIF SQLCODE=100 { &sql(COMMIT)}
      ELSE {
       &sql(ROLLBACK TO SAVEPOINT svpt_age2)
       WRITE !,"retirement age deletes failed" 
      }
    &sql(COMMIT)
  &sql(COMMIT)
Copy code to clipboard

ObjectScript and SQL Transactions

ObjectScript transaction processing, using TSTART and TCOMMIT, differs from, and is incompatible with, SQL transaction processing using the SQL statements START TRANSACTION, SAVEPOINT, and COMMIT. Both ObjectScript and InterSystems SQL provides limited support for nested transactions. ObjectScript transaction processing does not interact with SQL lock control variables; of particular concern is the SQL lock escalation variable. An application should not attempt to mix the two types of transaction processing.

If a transaction involves SQL update statements, then the transaction should be started by the SQL START TRANSACTION statement and committed with the SQL COMMIT statement. Methods that use TSTART/TCOMMIT nesting can be included in the transaction, as long as they don't initiate the transaction. Methods and stored procedures should not normally use SQL transaction control statements, unless, by design, they are the main controller of the transaction.

See Also