ROLLBACK

ROLLBACK is used to mark the end of a transaction and back out of all changes made by the transaction at the current location. The XDB Server does not rollback data definition language (DDL) commands such as CREATE, DROP, ALTER; data control language (DCL) commands such as GRANT, and REVOKE; or utility commands such as SET and COPY. When working in a production environment, it is recommended that you backup your location before and after such commands.

Invocation

ROLLBACK is an executable command that can be issued interactively or embedded in a host language. ROLLBACK can be dynamically prepared.

Authorization

No special authorization is required for ROLLBACK.

Syntax

ROLLBACK [WORK] [TO SAVEPOINT [svpt-name]]

Description

A transaction begins when a previous transaction ends. A transaction is terminated by a COMMIT or ROLLBACK command. Only the results of SQL commands executed within a single, uncommitted transaction are affected by the ROLLBACK command. When the ROLLBACK command is issued, all changes caused by INSERT, UPDATE and DELETE commands of the transaction are undone. All locks acquired during a transaction are released by the ROLLBACK command. If AUTOCOMMIT is on, a user-invoked ROLLBACK has no effect. However, the system will rollback a failed command.

Backward Logging

If you are using XDB Server Version 5.0 or higher, the ROLLBACK command is available. Previous versions of XDB required a feature called Backward Logging to be turned on in order to use the ROLLBACK command. The functionality provided by the old Backward Logging feature has been incorporated into the XDB Server logging system as a permanent default.

TO SAVEPOINT

Specifies that the unit of recovery is not to be ended and that only a partial rollback (to a savepoint) is to be performed. If a savepoint name is not specified, rollback is to the last active savepoint. For example, if in a unit of recovery, savepoints A, B, and C are set in that order and then C is released, ROLLBACK TO SAVEPOINT causes a rollback to savepoint B.

svpt-name A savepoint-identifier that identifies the savepoint to which to roll back. If the named savepoint does not exist, an error occurs.

All database changes (including changes made to a declared temporary tables but excluding changes made to created temporary tables) that were made after the savepoint was set are backed out. Changes that are made to created temporary tables are not logged and are not backed out; a warning is issued instead. (A warning is also issued when a created temporary table is changed and there is an active savepoint.)

In addition, none of the following items are backed out:

  • The opening or closing of cursors
  • Changes in cursor positioning
  • The acquisition and release of locks
  • The caching of the rolled back statements

Any savepoints that are set after the one to which rollback is performed are released. The savepoint to which rollback is performed is not released.

ROLLBACK with or without the TO SAVEPOINT clause has no effect on connections.