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:
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).
- Dynamically prepared statement names are still valid, although
the statement may be implicitly prepared again, as a result of DDL
operations that are rolled back within the savepoint.
- 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