EnSet Transaction No Savepoint
InterBase 7.5 SP1 includes a new option for the SET TRANSACTION statement. The NO SAVEPOINT clause executes a transaction without starting an implicit savepoint for the transaction or any SQL statements that execute within the context of that transaction. It is supported in DSQL, ESQL (GPRE) and ISQL. 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.
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 under different savepoints 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 returned 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_savepoint (SQLCODE -907), is 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 isc_must_rollback has been raised but any attempt to modify data will return the isc_must_rollback error again. It is also not possible to commit a transaction in such a condition.
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 time 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 savepoint's lifecycle 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.
ISQL has a new property SET SAVEPOINT {ON | OFF}, which toggles the use of savepoints with ISQL's 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.
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 under different savepoints 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 returned 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_savepoint (SQLCODE -907), is 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 isc_must_rollback has been raised but any attempt to modify data will return the isc_must_rollback error again. It is also not possible to commit a transaction in such a condition.
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 time 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 savepoint's lifecycle 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.
ISQL has a new property SET SAVEPOINT {ON | OFF}, which toggles the use of savepoints with ISQL's 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.