Managing Transactions

A transaction consists of one or more SQL statements that are grouped together to form a unit of work. For example, if you wanted to transfer money from a savings to a checking account, the withdrawal and the deposit would both occur within the same transaction. A transaction begins with a BEGIN WORK statement and ends with either a COMMIT WORK or a ROLLBACK WORK statement. Either all the statements or none of the statements are executed.

How ISQL Manages Transactions

ISQL automatically processes a BEGIN WORK statement whenever you successfully submit most SQL statements and a transaction is not already in progress:

   isql=> CONNECT TO '../sampledb/PartsDBE';

   isql=> UPDATE STATISTICS FOR TABLE PurchDB.Parts;

   isql=> BEGIN WORK;

   Transaction already started.  (DBERR 2103)

   isql=>

In this example, the UPDATE STATISTICS statement automatically does an implicit BEGIN WORK. Thus the explicit BEGIN WORK creates an error condition.

The following SQL statements do not cause ISQL to process a BEGIN WORK statement:

   BEGIN ARCHIVE

   BEGIN WORK

   CHECKPOINT

   COMMIT ARCHIVE

   CONNECT

   RELEASE

   START DBE

   STOP DBE

   TERMINATE USER

ISQL also automatically processes a BEGIN WORK statement whenever you successfully submit the following ISQL commands and a transaction is not already in progress:

   INFO

   INPUT

   INSTALL

   LOAD

   UNLOAD

Using SQL SAVEPOINT and ROLLBACK WORK Statements

Within a transaction, you can set savepoints. Work accomplished after a savepoint can be undone at any time prior to the end of the transaction. When you issue the SAVEPOINT statement in ISQL, you are assigned a savepoint number, starting at 1 with each new transaction. You reference the savepoint number in a ROLLBACK WORK statement to undo work done since the referenced savepoint was established. For example:

   isql=> SAVEPOINT;

   Savepoint number is 1

   Use this number to do ROLLBACK WORK TO 1.

   isql=> Command;Command...;

   isql=> ROLLBACK WORK TO 1;

   isql=>

ISQL automatically terminates transactions for you in several instances:

ISQL issues a COMMIT WORK statement when the SET AUTOCOMMIT option is ON and you are using the ISQL command INPUT, INSTALL, or LOAD.
ISQL issues a COMMIT WORK or ROLLBACK WORK statement when you respond to the exit prompt described earlier in this chapter under "Leaving ISQL."

When accessing multiuser DBEnvironments from ISQL, you may need to submit the COMMIT WORK or ROLLBACK WORK statement frequently to improve concurrency.

More information on managing SQL transactions is provided in the ALLBASE/SQL Database Administration Guide.

Managing Transactions

Technical documentation

» Table of Contents

» Index

How ISQL Manages Transactions

Using SQL SAVEPOINT and ROLLBACK WORK Statements