Working with the NO SAVEPOINT Option
Go Up to Ending Transactions
The NO SAVEPOINT clause is an option for the SET TRANSACTION statement. It executes a transaction without starting an implicit savepoint for the transaction or any SQL statements that execute within the context of that transaction. At the InterBase API level, a new transaction parameter block (TPB) option isc_tpb_no_savepoint can be passed to isc_start_transaction() to begin a transaction with no savepoint. For more information about starting a new transaction with isc_start_transaction(), see Calling isc_start_transaction().
It is supported in DSQL, ESQL (GPRE) and ISQL.
Implicit savepoints are automatically started by InterBase to guarantee the atomicity of an SQL statement. However, the execution performance of some SQL statements, stored procedures, and triggers can be adversely affected by the maintenance of the implicit savepoint as the statement continues to run.
Execution performance includes both CPU and memory consumption. In particular, a single transaction that modifies large amounts of data multiple times may experience this performance anomaly. Even transactions that modify large sets of data, but not multiple times, may experience a lesser degree of performance improvement.
The NO SAVEPOINT clause increases user transaction performance by bypassing the maintenance and control of implicit savepoints. In return for this performance benefit, a constraint is placed on a user transaction to maintain the atomicity of SQL statement execution. If any error is return during statement processing such that an atomic execution context must be interrupted before completion, then that user transaction must rollback.
In these scenarios, a major error code, isc_must_rollback (SQLCODE -908), and a minor error code, isc_tran_no_save (SQLCODE -907), are returned to the application. This error return should be treated similar to isc_deadlock or isc_update_conflict, where the normal response is to rollback the transaction and try again or try differently.
Read-only queries will still execute after the isc_must_rollback has been raised but any attempt to modify data will return the isc_must_rollback error again.
Since the transaction does not have any savepoints, rollback completes immediately with no latency due to the undo of savepoints. For the same reason, a statement can be cancelled immediately without enduring a lengthy savepoint undo phase. The transaction is marked as rolled back in the database, which implies that at some future point a database sweep will be required. Therefore, NO SAVEPOINT transactions should be reserved for those periods of database processing when update collision is rare.
The uncommitted rows left in the database will most likely be garbage collected by the garbage collector, sweep, backup, or attempts to update the rows by other transactions.
A NO SAVEPOINT transaction obeys the semantics of an explicit lifecycle of a savepoint as embodied by the SAVEPOINT, RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT statements. This allows existing application and database logic to run unchanged and with the same semantics that were originally intended by the developer. For more information, see Working with Savepoints.
ISQL has a new property SET SAVEPOINT {ON | OFF}, which toggles the use of savepoints with ISQL default transaction. The SET command has been modified to show the current setting for the SAVEPOINT property. The normal default setting at ISQL startup is SET SAVEPOINT ON.