Creating a savepoint

    Available

    DSQL

    The SAVEPOINT statement creates an SQL:99-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. Savepoint mechanisms are sometimes characterised as “nested transactions”.

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

    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 statement will erase it and thus free the resources.

    Sample DSQL session with savepoints

    1. CREATE TABLE TEST (ID INTEGER);
    3. INSERT INTO TEST VALUES (1);
    4. INSERT INTO TEST VALUES (2);
    5. SAVEPOINT Y;
    6. DELETE FROM TEST;
    7. ROLLBACK TO Y;
    8. SELECT * FROM TEST; -- returns two rows
    9. ROLLBACK;

    ROLLBACK TO SAVEPOINT,