The
ROLLBACK statement is used to back out of the database changes that
were made within a unit of work or a savepoint.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
Authorization
None required.
Syntax
.-WORK-.
>>-ROLLBACK--+------+--+----------------------------------+----><
'-TO SAVEPOINT--+----------------+-'
'-savepoint-name-'
Description
The unit of work in which the
ROLLBACK statement is executed is terminated and a new unit of work
is initiated. All changes made to the database during the unit of
work are backed out.
The following statements, however, are
not under transaction control, and changes made by them are independent
of the ROLLBACK statement:
- SET CONNECTION
- SET ENCRYPTION PASSWORD
- SET EVENT MONITOR STATE
- SET
PASSTHRU (Although the SET PASSTHRU statement is not under transaction
control, the passthru session initiated by the statement is under
transaction control.)
- SET SERVER OPTION
- A
SET statement that sets an updatable special register
The generation of sequence and identity values is not under
transaction control. Values generated and consumed by the nextval-expression or
by inserting rows into a table that has an identity column are independent
of issuing the ROLLBACK statement. Also, issuing the ROLLBACK statement
does not affect the value returned by the prevval-expression,
nor the IDENTITY_VAL_LOCAL function.
Modification of the values
of global variables is not under transaction control. ROLLBACK statements
do not affect the values assigned to global variables.
- TO SAVEPOINT
- Specifies that a partial rollback (ROLLBACK TO SAVEPOINT) is to
be performed. If no savepoint is active in the current savepoint level
(see the "Rules" section in the description of the SAVEPOINT
statement), an error is returned (SQLSTATE 3B502). After a successful
rollback, the savepoint continues to exist, but any nested savepoints
are released and no longer exist. The nested savepoints, if any, are
considered to have been rolled back and then released as part of the
rollback to the current savepoint. If a savepoint-name is
not provided, rollback occurs to the most recently set savepoint
within the current savepoint level.
If this clause is omitted,
the ROLLBACK statement rolls back the entire transaction. Furthermore,
savepoints within the transaction are released.
- savepoint-name
- Specifies the savepoint that is to be used in the rollback
operation. The specified savepoint-name cannot
begin with 'SYS' (SQLSTATE 42939). After a successful rollback
operation, the named savepoint continues to exist. If the savepoint
name does not exist, an error (SQLSTATE 3B001) is returned. Data and
schema changes made since the savepoint was set are undone.
Notes
- All locks held are released on a ROLLBACK of the unit of work.
All open cursors are closed. All LOB locators are freed.
- Executing a ROLLBACK statement does not affect either the SET
statements that change special register values or the RELEASE statement.
- If the program terminates abnormally, the unit of work is implicitly
rolled back.
- Statement caching is affected by the rollback operation.
- The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends
on the statements within the savepoint
- If the savepoint contains DDL on which a cursor is dependent,
the cursor is marked invalid. Attempts to use such a cursor results
in an error (SQLSTATE 57007).
- Otherwise:
- If the cursor is referenced in the savepoint, the cursor remains
open and is positioned before the next logical row of the result table.
(A FETCH must be performed before a positioned UPDATE or DELETE statement
is issued.)
- Otherwise, the cursor is not affected by the ROLLBACK TO SAVEPOINT
(it remains open and positioned).
- Dynamic SQL statements prepared in a package
bound with the KEEPDYNAMIC YES option are kept in
the SQL context after a ROLLBACK statement. The statement might be
implicitly prepared again, as a result of DDL operations that are
rolled back within the unit of work.
- Inactive dynamic SQL statements prepared in a
package bound with KEEPDYNAMIC NO are removed from
the SQL context after a rollback operation. The statement must be
prepared again before it can be executed in a new transaction.
- The following dynamic SQL statements may be active
during ROLLBACK:
- ROLLBACK statement
- CALL statements under which the ROLLBACK statement was executed
- A ROLLBACK TO SAVEPOINT operation will drop
any created temporary tables created within the savepoint. If a created
temporary table is modified within the savepoint and that table
has been defined as not logged, then all rows in the table are
deleted.
- A ROLLBACK TO SAVEPOINT operation will drop any declared temporary
tables declared within the savepoint. If
a declared temporary table is modified within the savepoint and
that table has been defined as not logged, then all rows in the
table are deleted.
- All locks are retained after a ROLLBACK TO SAVEPOINT statement.
- All LOB locators are preserved following a ROLLBACK TO SAVEPOINT
operation.
Example
Delete the alterations made since
the last commit point or rollback.
ROLLBACK WORK