HP 3000 Manuals HP 3000 Manuals
Transaction Management for Simple Operations
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 chapter, Programming for Performance.
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 (SQLCODE .LT. 0) THEN
EXEC SQL ROLLBACK WORK;