ROLLBACK TO SAVEPOINT

`ROLLBACK TO SAVEPOINT`

The ROLLBACK TO SAVEPOINT statement specifies the name of a savepoint to which changes are to be rolled back.The effect is to roll back all changes made within the transaction, from the specified savepoint forward until the point when ROLLBACK TO SAVEPOINT is requested.

ROLLBACK TO SAVEPOINT performs the following operations:

Any database mutations performed since the savepoint was created are undone.User variables set with RDB$SET_CONTEXT() remain unchanged.
Any savepoints that were created after the one named are destroyed.Savepoints earlier than the one named are preserved, along with the named savepoint itself.Repeated rollbacks to the same savepoint are thus allowed.
All implicit and explicit record locks that were acquired since the savepoint are released.Other transactions that have requested access to rows locked after the savepoint are not notified and will 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.

`SAVEPOINT`

Creates a savepoint

Syntax

SAVEPOINT sp_name

Table 1. SAVEPOINT Statement Parameter
Parameter	Description
sp_name	Savepoint name.Available only in DSQL

The SAVEPOINT statement creates an SQL-compliant savepoint that acts as a marker in the “stack” of data activities within a transaction.Subsequently, the tasks performed in the “stack” can be undone back to this savepoint, leaving the earlier work and older savepoints untouched.Savepoints are sometimes called “nested transactions”.

If a savepoint already exists with the same name as the name supplied for the new one, the existing savepoint is released, and a new one is created using the supplied name.

To roll changes back to the savepoint, the statement ROLLBACK TO SAVEPOINT is used.

Note

Memory Considerations

The internal mechanism beneath savepoints can consume large amounts of memory, especially if the same rows receive multiple updates in one transaction.When a savepoint is no longer needed, but the transaction still has work to do, a [fblangref50-transacs-releasesp] statement will erase it and thus free the resources.

Sample DSQL session with savepoints

CREATE TABLE TEST (ID INTEGER);
COMMIT;
INSERT INTO TEST VALUES (1);
COMMIT;
INSERT INTO TEST VALUES (2);
SAVEPOINT Y;
DELETE FROM TEST;
SELECT * FROM TEST; -- returns no rows
ROLLBACK TO Y;
SELECT * FROM TEST; -- returns two rows
ROLLBACK;
SELECT * FROM TEST; -- returns one row