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 involve 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;
EXEC SQL SELECT OrderNumber,
VendorNumber,
OrderDate
INTO :OrderNumber,
:VendorNumber :VendorNumberInd,
:OrderDate :OrderDateInd
FROM PurchDB.Orders
WHERE OrderNumber = :OrderNumber;
Error checking is done here.
EXEC SQL COMMIT WORK;
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:
If 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 as follows:
EXEC SQL BEGIN WORK;
The SELECT command is executed and the query result displayed.
EXEC SQL COMMIT WORK;
The program user requests that the row be deleted.
EXEC SQL BEGIN WORK;
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;
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;
The DELETE command is executed.
If the DELETE command fails, the transaction can be terminated as
follows:
EXEC SQL COMMIT WORK;
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;
If the INSERT command succeeds, the transaction is
terminated as follows:
EXEC SQL COMMIT WORK;
|
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;
if (sqlca.sqlcode != OK) {
EXEC SQL ROLLBACK WORK;
}
|
