SAVEPOINT

Description: Creates an SQL-99 compliant savepoint, to which you can later rollback your work
without rolling back the entire transaction. Savepoint mechanisms are also known as
“nested transactions”.

Syntax:

SAVEPOINT <name><name> ::= a user-chosen identifier, unique within the transaction

If the supplied name exists already within the same transaction, the existing savepoint
is deleted and a new one is created with the same name.

If you later want to rollback your work to the point where the savepoint was created,
use:

ROLLBACK [WORK] TO [SAVEPOINT] name

ROLLBACK TO SAVEPOINT performs the following operations:

All the database mutations performed within the transaction since the savepoint was
created are undone. User variables set with RDB$SET_CONTEXT()
remain unchanged.

All savepoints created after the one named are destroyed. All earlier savepoints are
preserved, as is the savepoint itself. This means that you can rollback to the same
savepoint several times.

All implicit and explicit record locks acquired since the savepoint are released.
Other transactions that have requested access to rows locked after the savepoint must
continue to wait until the transaction is committed or rolled back. Other transactions
that have not already requested the rows can request and access the unlocked rows
immediately.

The internal savepoint bookkeeping can consume huge amounts of memory, especially if you
update the same records multiple times in one transaction. If you don't need a savepoint
anymore but you're not yet ready to end the transaction, you can delete the savepoint and free
the resources it uses with:

RELEASE SAVEPOINT name [ONLY]

With ONLY, the named savepoint is the only one that gets released.
Without it, all savepoints created after it are released as well.

Internal savepoints

By default, the engine uses an automatic transaction-level system savepoint to perform
transaction rollback. When you issue a ROLLBACK statement, all changes
performed in this transaction are backed out via a transaction-level savepoint and the
transaction is then committed. This logic reduces the amount of garbage collection caused by
rolled back transactions.

When the volume of changes performed under a transaction-level savepoint is getting
large (104–106 records affected), the
engine releases the transaction-level savepoint and uses the TIP mechanism to roll back the
transaction if needed.

Tip

If you expect the volume of changes in your transaction to be large, you can specify
the NO AUTO UNDO option in your SET TRANSACTION
statement, or – if you use the API – set the TPB flag isc_tpb_no_auto_undo. Both prevent the creation of the
transaction-level savepoint.

Savepoints and PSQL

Transaction control statements are not allowed in PSQL, as that would break the
atomicity of the statement that calls the procedure. But Firebird does support the raising
and handling of exceptions in PSQL, so that actions performed in stored procedures and
triggers can be selectively undone without the entire procedure failing. Internally,
automatic savepoints are used to:

undo all actions in a BEGIN...END block where an exception
occurs;

undo all actions performed by the SP/trigger (or, in the case of a selectable SP,
all actions performed since the last SUSPEND) when it terminates
prematurely due to an uncaught error or exception.

Each PSQL exception handling block is also bounded by automatic system
savepoints.