ALLBASE/SQL Reference Manual (36216-90216)

Chapter 2 87

Using ALLBASE/SQL

Managing Transactions

After a rollback to a savepoint has been executed, use the COMMIT WORK statement to make

the changes that were not rolled back permanent. If you want to rollback the entire

transaction, issue the ROLLBACK statement without a savepoint.

Savepoints are suitable for transactions that perform several operations, any of which may

need to be rolled back. In the following example, a travel agency is booking tour

reservations for 15 people. When the ﬁrst attempt to make a hotel reservation fails, only

that part of the transaction is rolled back. The car reservations are unaffected by the roll

back because they were made prior to the savepoint.

BEGIN WORK

Make 15 car reservations.

SAVEPOINT

Savepoint number is 1. An attempt to make 15 hotel reservations fails because the

designated hotel is full.

ROLLBACK WORK TO 1

SAVEPOINT

Savepoint number is 2. Make 15 hotel reservations at another hotel.

COMMIT WORK

Scoping of Transaction and Session Attributes

A set of attributes is associated with each transaction and user session. This section

discusses the statements used to specify the following transaction and session attributes:

• priority

• isolation level

• label

• ﬁll option

• constraint checking mode

• DML atomicity level

Each attribute can be speciﬁed in one or more of the statements listed in Table 2-1. You

can issue such statements at any point in an application or ISQL session (with the

exception of BEGIN WORK which cannot be issued within a transaction). However they may

not take effect immediately, and the duration of their effect differs as described in the

following paragraphs. Chapter 10 , “SQL Statements A - D,” and Chapters 11 and 12

contains complete syntax for each statement.

When beginning a transaction, attributes speciﬁed in a BEGIN WORK statement take effect

immediately and remain in effect until the transaction ends, unless reset by a SET

TRANSACTION, SET CONSTRAINTS, or SET DML ATOMICITY statement within the

transaction.

Within a transaction, the attributes speciﬁed in a SET TRANSACTION, SET CONSTRAINTS,

or SET DML ATOMICITY statement take effect immediately and remain in effect until the

transaction ends, unless subsequently reset by such a statement. A SET SESSION