ALLBASE/SQL Reference Manual (36216-90216)

86 Chapter2

Using ALLBASE/SQL

Managing Transactions

Using ROLLBACK WORK

The ROLLBACK WORK statement ends the transaction and undoes all data modiﬁcations

made since the BEGIN WORK statement, unless it references a savepoint. (See the

discussion of savepoints in the following section.) The ROLLBACK WORK statement is issued

automatically by ALLBASE/SQL under the following conditions:

• A non-archive log ﬁle becomes full.

•ARELEASE statement is issued before the end of the transaction.

• A system failure occurs. When the system is up again, and a START DBE statement is

issued, incomplete transactions are rolled back.

• ALLBASE/SQL chooses the transaction as the victim when breaking a deadlock.

• The session is terminated by a TERMINATE USER command.

The ROLLBACK WORK statement should be issued explicitly to maintain data integrity. You

may want to issue a ROLLBACK WORK in an application program when any of the following

situations arise:

• The transaction contains more than one SQL statement and one of the statements

generates an error. For example, if your transaction contains three INSERT statements,

and the second INSERT fails, you should rollback the entire transaction.

•AnINSERT, UPDATE, or DELETE statement that affects multiple rows generates an

error after some of the rows have been modiﬁed. You should rollback the transaction if

the partial changes will leave your database in an inconsistent state.

• The end user provides input indicating that he or she does not want to commit the

transaction.

Using SAVEPOINT

The SAVEPOINT statement allows you to rollback part of a transaction. Multiple savepoints

are permitted within a transaction anywhere between the BEGIN WORK and COMMIT WORK

statements. Each SAVEPOINT statement places a unique marker, called a savepoint

number, within the transaction. When a subsequent ROLLBACK references the savepoint

number, only those database changes made after the savepoint are rolled back. Rolling

back to a savepoint does not end the transaction, but it does release locks obtained after

the savepoint was issued.

In the following ISQL example, the number identifying the savepoint marker is 6. The

update performed after the SAVEPOINT statement is undone by the ROLLBACK statement,

but any database changes made before savepoint 6 are unaffected.

isql=> SAVEPOINT;

Savepoint number is 6. Use this number to do ROLLBACK WORK to 6.

isql=> UPDATE PurchDBParts

> SET SalesPrice = 244.00

> WHERE PartNumber = '1243-MU-01';

isql=> ROLLBACK WORK to 6;