Caché SQL Reference
SAVEPOINT
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Marks a point within a transaction.
Synopsis
SAVEPOINT pointname
Arguments
pointname The name of the savepoint, specified as an identifier. For further details see the “Identifiers” chapter of Using Caché 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.
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, Caché 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, Caché 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:
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)
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 Caché 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