SET 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.