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 'PartsDBE.SomeGrp.SomeAcct';
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.
