The time at which ALLBASE/SQL obtains locks during cursor
processing depends on the type of index ALLBASE/SQL uses
to retrieve the query result.
When a cursor is based on a SELECT command for which ALLBASE/SQL
can use an index scan,
locks are obtained when the FETCH
command is executed. In the following example, an index scan can be
used, because the predicate is optimizable and an index
exists on column OrderNumber:
EXEC SQL DECLARE OrderReview
CURSOR FOR
SELECT OrderNumber,
ItemNumber,
OrderQty,
ReceivedQty
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
|
When the cursor is based on a SELECT command for which ALLBASE/SQL
will use a sequential scan, locks are obtained when
the OPEN command is executed. A sequential scan would be used
in conjunction with the following cursor:
EXEC SQL DECLARE OrderReview
CURSOR FOR
SELECT OrderNumber,
ItemNumber
OrderQty,
ReceivedQty
FROM PurchDB.OrderItems
WHERE OrderNumber > :OrderNumber;
|
The scope and strength of any lock obtained depends in part on the
automatic locking mode of the target table(s). If the lock obtained is a
shared lock, as for PUBLIC or PUBLICREAD tables, ALLBASE/SQL elevates
the lock to an exclusive lock when you update or delete a row
in the active set.
The use of lock types, lock granularities, and isolation levels is
discussed in the ALLBASE/SQL Reference Manual .
As mentioned in the previous section, when a transaction terminates, any
cursors opened during that transaction are either automatically closed, or they remain open if you are using the KEEP CURSOR option of the OPEN command. To avoid possible confusion, it is good programming practice to always use the CLOSE command to explicitly close any open cursors before ending a transaction with the COMMIT WORK or ROLLBACK WORK command.
When the transaction terminates, any changes made to the active set
during the transaction are
either all committed or all rolled back, depending on how you
terminate the transaction.