简体中文

SAVEPOINT

Define a new save point in the current transaction.

Synopsis

SAVEPOINT savepoint_name

Description

SAVEPOINT creates a new save point in the current transaction.

A savepoint is a special mark inside a transaction, which allows rollback of all commands executed after a transaction is established, thus restoring the transaction state to the state at the time of the savepoint.

Parameters

savepoint_name

The name of the new savepoint.

Note

Use ROLLBACK TO SAVEPOINT to roll back to the save point. Use RELEASE SAVEPOINT to destroy a savepoint and keep the command execution effective after the savepoint is created.

Savepoints can only be created inside transaction blocks. Multiple save points can be defined in a transaction.

Example

To create a savepoint, and then undo the effect of all commands executed after the savepoint is created:

BEGIN;
                INSERT INTO table1 VALUES (1);
                SAVEPOINT my_savepoint;
                INSERT INTO table1 VALUES (2);
                ROLLBACK TO SAVEPOINT my_savepoint;
                INSERT INTO table1 VALUES (3);
COMMIT;

The above transaction will insert values 1 and 3, but not 2.

To create and then destroy a savepoint:

BEGIN;
                INSERT INTO table1 VALUES (3);
                SAVEPOINT my_savepoint;
                INSERT INTO table1 VALUES (4);
                RELEASE SAVEPOINT my_savepoint;
COMMIT;

The above transactions will be inserted 3 and 4 at the same time.

Compatibility

When creating another savepoint with the same name, SQL requires that a savepoint be deleted automatically. In the YMatrix database, old save points are preserved, although only newer save points are used when rolled back or released. (Releasing newer save points will make the older save points available again for ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) Otherwise, SAVEPOINT is fully SQL-compliant.