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;
|