HP 3000 Manuals

ALLBASE/SQL COBOL Application Programming Guide

Transaction Management for Cursor Operations 

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

MPE/iX 5.0 Documentation