Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Savepoint and Partial Rollback

Description

A savepoint is established during the transaction so that database changes made by the transaction are rolled back to the specified savepoint. Such operation is called a partial rollback. In a partial rollback, database operations (insert, update, delete, etc.) after the savepoint are rolled back, and transaction operations before it are not rolled back. The transaction can proceed with other operations after the partial rollback is executed. Or the transaction can be terminated with the COMMIT WORK or ROLLBACK WORK statement. Note that the savepoint does not commit the changes made by the transaction.

A savepoint can be created at a certain point of the transaction, and multiple savepoints can be used for a certain point. If a partial rollback is executed to a savepoint before the specified savepoint or the transaction is terminated with the COMMIT WORK or ROLLBACK WORK statement, the specified savepoint is removed. The partial rollback after the specified savepoint can be performed multiple times.

Savepoints are useful because intermediate steps can be created and named to control long and complicated utilities. For example, if you use a savepoint during the update operation, you don't need to perform all statements again when you made a mistake.

Note: In order for savepoints to work, autocommit option must be disabled.

Syntax 1

SAVEPOINT mark

mark:

_ a SQL identifier

_ a host variable (starting with :)

If you make mark all the same value when you specify multiple savepoints in a single transaction, only the latest savepoint appears in the partial rollback. The previous savepoints remain hidden until the rollback to the latest savepoint is performed and then appears when the latest savepoint disappears after being used.

Syntax 2

ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] mark ] [ ]

mark:

_ a SQL identifier

_ a host variable (starting with :)

Previously, the ROLLBACK WORK statement canceled all database changes added since the latest transaction. The ROLLBACK WORK statement is also used for the partial rollback that rolls back the transaction changes after the specified savepoint.

If mark value is not given, the transaction terminates canceling all changes including all savepoints created in the transaction. If mark value is given, changes after the specified savepoint are canceled and the ones before it are remained.

Example

The following example shows how to roll back part of the transaction.

First, set savepoints SP1 and SP2.

CREATE TABLE athlete2 (name VARCHAR(40), gender CHAR(1), nation_code CHAR(3), event VARCHAR(30));

INSERT INTO athlete2(name, gender, nation_code, event)

VALUES ('Lim Kye-Sook', 'W', 'KOR', 'Hockey');

SAVEPOINT SP1;

 

SELECT * from athlete2;

INSERT INTO athlete2(name, gender, nation_code, event)

VALUES ('Lim Jin-Suk', 'M', 'KOR', 'Handball');

 

SELECT * FROM athlete2;

SAVEPOINT SP2;

 

RENAME TABLE athlete2 AS sportsman;

SELECT * FROM sportsman;

ROLLBACK WORK TO SP2;

In the example above, the name change of the athlete2 table is rolled back by the partial rollback. The following example shows how to execute the query with the original name and examining the result.

SELECT * FROM athlete2;

DELETE FROM athlete2 WHERE name = 'Lim Jin-Suk';

SELECT * FROM athlete2;

ROLLBACK WORK TO SP2;

In the example above, deleting 'Lim Jin-Suk' is discarded by rollback work to SP2 command.

The following example shows how to roll back to SP1.

SELECT * FROM athlete2;

ROLLBACK WORK TO SP1;

SELECT * FROM athlete2;

COMMIT WORK;