SAVEPOINT

SAVEPOINT — define a new savepoint within the current transaction

Syntax

SAVEPOINT savepoint_name

Description

SAVEPOINT establishes a new savepoint within the current transaction.
A savepoint identifies a point in a transaction that all commands, executed after the savepoint was established, to be rolled back, thereby restoring the transaction state to the original state. There can be multiple savepoints defined within a transaction.
Use COMMIT to release all savepoints and commit all database changes for the current transaction (See COMMIT).
Use ROLLBACK TO SAVEPOINT to rollback to a previously specific savepoint within the current transaction (See ROLLBACK). Use ROLLBACK to rollback all changes for the current transaction, regardless of savepoints in the current transaction.
Use RELEASE SAVEPOINT to destroy a savepoint.

AUTOCOMMIT must be OFF to enable transaction savepoints.

Parameters

savepoint_name: Name given to the new savepoint.

Examples

Example 1: Error using SAVEPOINT and ROLLBACK TO SAVEPOINT

SHOW AUTOCOMMIT
    Autocommit is on
DROP TABLE testtable IF EXISTS;
CREATE TABLE testtable (column1 STRING);
INSERT INTO testtable VALUES ('Insert Before Savepoint first');
SAVEPOINT first;
INSERT INTO testtable VALUES ('Insert After Savepoint first');
SELECT * FROM testtable;
            COLUMN1
 -----------------------------
 Insert Before Savepoint first
 Insert After Savepoint first

ROLLBACK TO SAVEPOINT first;
    savepoint "FIRST" is not defined

Example 2: Defining one or more savepoints in the current transaction.

SET AUTOCOMMIT off;
CREATE TABLE testtable (column1 STRING);
INSERT INTO testtable VALUES ('Insert Before Savepoint first');

SAVEPOINT first;
INSERT INTO testtable VALUES ('Insert After Savepoint first');
SELECT * FROM testtable;
            COLUMN1
 -----------------------------
 Insert Before Savepoint first
 Insert After Savepoint first

ROLLBACK TO SAVEPOINT first;
SELECT * FROM testtable;
            COLUMN1
 -----------------------------
 Insert Before Savepoint first


SAVEPOINT second;
INSERT INTO testtable VALUES('Insert After Savepoint second');
SELECT * FROM testtable;
            COLUMN1
 -----------------------------
 Insert Before Savepoint first
 Insert After Savepoint second

RELEASE SAVEPOINT second;
COMMIT;
SELECT * FROM testtable;
            COLUMN1
 -----------------------------
 Insert Before Savepoint first
 Insert After Savepoint second