HP 3000 Manuals HP 3000 Manuals
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 '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.