Nested Transactions - Firebird

In Firebird, transactions are always started and finished by a client. Some other DBMSs can start and commit transactions from within stored procedures because two-phase locks and transaction logging are used to control transactions. Instead, Firebird provides other mechanisms that can operate on the flow of work within a transaction without breaking atomicity. Two of these mechanisms, exception handler blocks and executable strings, are restricted for use within PSQL modules. The other, not available in PSQL, is user savepoints.

User Savepoints

User savepoint statements, also known as nested transactions, enable you to “batch” groups of operations inside a transaction and signpost them when they post successfully. Should an exception occur later in the task sequence, the transaction can be rolled back to the last savepoint. Operations that were posted between the savepoint and the exception are rolled back and the application can correct, branch, commit, perform a full rollback, resume, or whatever is required.

Setting a Savepoint

User savepoints are a client-side operation, available only as DSQL statements. The statement for setting a savepoint is

SAVEPOINT <identifier>;

The identifier can be any valid Firebird SQL identifier (31 characters maximum, U.S. ASCII alphanumeric characters, and distinct within the transaction). You can reuse the same identifier in the same transaction and it will overwrite any existing savepoint associated with the name.

Rolling Back to a Savepoint

A rollback to a savepoint begins by rolling back all the work posted after the savepoint was set. The named savepoint and any that preceded it are retained. Any savepoints that were set after the named savepoint are lost.

Any locks (explicit or implicit) that were acquired since the named savepoint was set are released. However, transactions that have been locked out, waiting for access to the released rows, still do not get access to them until the transaction completes. This ensures that currently waiting contenders will not interfere with the established work-flow if it resumes. It does not block a transaction that was not waiting at the time of the nested rollback.

This is the syntax pattern for rolling back to a savepoint:


If the transaction is allowed to resume after rolling back to a savepoint, the work-flow can roll back to the same savepoint again, as many times as necessary. Record versions that get rolled back will not be eligible for garbage collection, because the transaction is still active.

Releasing Savepoints

On the server, the savepoint mechanism—a memory-based log—can have a significant impact on resources, especially if the same rows receive updates many times during the course of retracking through the task. The resources from unwanted savepoints can be released using a RELEASE SAVEPOINT statement:


Without the keyword ONLY, the named savepoint and all savepoints that were set after it will be released and lost. Use ONLY to release just the named savepoint.

The following example illustrates how savepoints work:

SELECT * FROM SAVEPOINT_TEST; /* returns nothing */
SELECT * FROM SAVEPOINT_TEST; /* returns 2 rows */
SELECT * FROM SAVEPOINT_TEST; /* returns the one committed row */

Internal Savepoints

When the server engine executes rollbacks, its default point of reference for backing out a transaction is to internal savepoints stored in the memory-based auto-undo log. At the end of the rollback, it commits the transaction. The purpose of this strategy is to reduce the amount of garbage generated by rollbacks.

When the volume of changes performed under a transaction-level savepoint is getting large—into the range of 10,000 to 1 million rows affected —the engine abandons the auto-undo log and takes its references directly from the global TSB. If you have a transaction that you expect to command a large number of changes, disabling auto-undo logging will forestall the wastage of resource consumption that would occur if the server decided to abandon the logging. For more information, see “The Auto-Undo Log” earlier in this chapter.


Exception Handling Extensions

The equivalent of savepoints in PSQL modules is exception handling. Each PSQL exception-handling block is also bounded by automatic system savepoints. The PSQL extensions provide language wrappers implementing the same kind of nesting that user savepoints provide in DSQL.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics