The time at which ALLBASE/SQL obtains locks during cursor
processing depends on whether ALLBASE/SQL uses an index scan or a
sequential scan 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
END-EXEC.
|
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
END-EXEC.
|
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 .
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.