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;

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.