The major objectives of transaction management are
to minimize the contention for locks and to ensure logical data consistency.
Minimizing lock contention implies short transactions and/or locking
small, unique parts of a database. Logical data consistency implies
keeping data manipulations
that should all occur or all not occur within a single transaction.
Defining your transactions
should always be made with these two objectives in mind.
For in depth transaction management information, refer to
the ALLBASE/SQL Reference Manual .
Most simple data manipulation applications are for random
operations on a minimal number of related rows that
satisfy very specific criteria.
To minimize lock contention, you should begin a new
transaction each time these criteria change. For example, if
an application displays order information for random orders,
delimit each new query with a BEGIN WORK and a COMMIT WORK command:
The program accepts an order number from the user.
EXEC SQL BEGIN WORK END-EXEC.
EXEC SQL SELECT ORDERNUMBER,
VENDORNUMBER,
ORDERDATE
INTO :ORDERNUMBER,
:VENDORNUMBER :VENDORNUMBERIND,
:ORDERDATE :ORDERDATEIND
FROM PURCHDB.ORDERS
WHERE ORDERNUMBER = :ORDERNUMBER
END-EXEC.
Error checking is done here.
EXEC SQL COMMIT WORK END-EXEC.
The program displays the row, then prompts for another order number.
|
Because SELECT commands are often executed prior to a related
UPDATE, DELETE, or INSERT command, you must decide
whether to make each command a separate transaction or combine
commands within one transaction. And you must decide which isolation
level to use to attain your desired data consistency and to minimize
possible lock contention.
If, for example, you combine SELECT and DELETE operations within one
transaction, when the DELETE command is executed, the row deleted is
guaranteed to
be the same row retrieved and displayed for the user. However, if the
program user goes to lunch between SELECT
and DELETE commands, and the default isolation level (RR) is in effect,
no other users can modify the page or table locked by the SELECT
command until the transaction terminates.
If you put the SELECT and DELETE operations in separate
transactions, another transaction may change the target row(s) before the
DELETE command is executed. Therefore
the user may delete a row different from that originally intended.
One way to handle this situation is to verify that no changes have
occurred as follows:
EXEC SQL BEGIN WORK END-EXEC.
The SELECT command is executed and the query result displayed.
EXEC SQL COMMIT WORK END-EXEC.
The program user requests that the row be deleted.
EXEC SQL BEGIN WORK END-EXEC.
The SELECT command is re-executed, and the program compares the
original query result with the new one. If the query results match, the
DELETE command is executed.
EXEC SQL COMMIT WORK END-EXEC.
If the new query result does not match the original query result, the
program re-executes the SELECT command to display the query result.
|
In the case of some multi-command transactions, you must execute
multiple data manipulation commands within a single transaction
for the sake of logical data consistency.
In the following example, the DELETE and INSERT commands are
used in place of the UPDATE command to insert null
values into the target table.
EXEC SQL BEGIN WORK END-EXEC.
The DELETE command is executed.
If the DELETE command fails, the transaction can be terminated as follows:
EXEC SQL COMMIT WORK END-EXEC.
If the DELETE command succeeds, the INSERT command is executed.
If the INSERT command fails, the transaction is terminated as follows:
EXEC SQL ROLLBACK WORK END-EXEC.
If the INSERT command succeeds, the transaction is terminated as follows:
EXEC SQL COMMIT WORK END-EXEC.
|
Logical data consistency is also an issue when an UPDATE, INSERT, or
DELETE command may operate on multiple rows. If one of these
commands fails after only some of the target rows have been
operated on, you must use a ROLLBACK WORK command to ensure
that any row changes made before the failure are undone:
EXEC SQL DELETE FROM PURCHDB.ORDERS
WHERE ORDERDATE < :ORDERDATE
END-EXEC.
IF SQLCODE NOT OK
EXEC SQL ROLLBACK WORK END-EXEC.
|
