HP 3000 Manuals

ALLBASE/SQL FORTRAN Application Programming Guide

Transaction Management for Cursor Operations 

The time at which ALLBASE/SQL obtains locks during cursor processing
depends on whether an index scan or a sequential scan is used to retrieve
the query result.

When a cursor is based on a SELECT command for which an index scan is
used, 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 a sequential scan
is used, 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, the lock is elevated 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 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.

Using KEEP CURSOR 

Cursor operations in an application program let you manipulate data in an
active set associated with a SELECT command.  The cursor is a pointer to
a row in the active set.  The KEEP CURSOR option of the OPEN command lets
you maintain the cursor position in an active set beyond transaction
boundaries.  This means you can scan and update a large table without
holding locks for the duration of the entire scan.  You can also design
transactions that avoid holding any locks around terminal reads.  In
general, use the KEEP CURSOR option when you wish to release locks
periodically in long or complicated transactions.

Using KEEP CURSOR 

After you specify KEEP CURSOR in an OPEN command, a COMMIT WORK does not
close the cursor, as it normally does.  Instead, COMMIT WORK releases
locks held before the cursor position and immediately begins a new
transaction without changing the current cursor position.  This makes it
possible to update tuples in a large active set, releasing locks as the
cursor moves from page to page, instead of requiring you to reopen and
manually reposition the cursor before the next FETCH. Locks held on the
page of data corresponding to the current cursor position are either held
until the transaction ends (the default) or released depending on whether
you specify WITH LOCKS or WITH NOLOCKS.

If you use the KEEP CURSOR WITH NOLOCKS option for a cursor that involves
sorting, whether through the use of a DISTINCT, GROUP BY, or ORDER BY
clause, or as the result of a union or a join operation, ALLBASE/SQL does
not ensure data integrity.

It is your responsibility to ensure data integrity by verifying the
continued existence of a row before updating it or using it as the basis
for updating some other table.  For an updatable cursor, you can use
either the REFETCH or SELECT command to verify the continued existence of
a row.  For a cursor that is non-updatable, you must use the SELECT
command.

A warning (DBWARN 2056) regarding the kept cursor on a sort with no locks
is generated.  You must check for this warning if you want to detect the
execution of this type of cursor operation.

Figure 8-1 shows the operation of cursors when you do not select the KEEP
CURSOR option.


          Figure 8-1.  Cursor Operation without the KEEP CURSOR Feature 

After the cursor is opened, successive FETCH commands advance the cursor
position.  Any exclusive locks acquired along the way are retained until
the transaction ends.  If you have selected the Cursor Stability option
in the BEGIN WORK command, shared locks on pages that have not been
updated are released when the cursor moves to a tuple on a new data page.
Exclusive locks are not released until a COMMIT WORK, which also closes
the cursor.

OPEN Command Using KEEP CURSOR WITH LOCKS 

The feature has the following effects:

   *   A COMMIT WORK command does not close the cursor.  Instead, it ends
       the current transaction and immediately starts another one.

   *   When you issue a COMMIT WORK, locks on the page that contains the
       current cursor position are not released.

   *   Successive FETCHES advance the cursor position, which is retained
       in between transactions until the cursor is explicitly closed with
       the CLOSE command.

   *   After the CLOSE command, you use an additional COMMIT WORK
       command.  This step is essential.  The final COMMIT after the
       CLOSE is necessary to end the KEEP state and prevent a new
       implicit BEGIN WORK.

Figure 8-2 shows the effect of the KEEP CURSOR WITH LOCKS.


          Figure 8-2.  Cursor Operation Using KEEP CURSOR WITH LOCKS 

OPEN Command Using KEEP CURSOR WITH NOLOCKS 

The feature has the following effects:

   *   A COMMIT WORK command does not close the cursor.  Instead, it ends
       the current transaction and immediately starts another one.

   *   When you issue a COMMIT WORK, all locks on the page that contains
       the current cursor position are released.  This means that another
       transaction may delete or modify the next tuple in the active set
       before you have the chance to FETCH it.

   *   Successive FETCHES advance the cursor position, which is retained
       in between transactions until the cursor is explicitly closed with
       the CLOSE command.

   *   After the CLOSE command, you use an additional COMMIT WORK
       command.  This step is essential.  The final COMMIT after the
       CLOSE is necessary to end the KEEP state and prevent a new
       implicit BEGIN WORK.

   *   You cannot use the KEEP CURSOR option WITH NOLOCKS for a cursor
       declared as a SELECT with a DISTINCT or ORDER BY clause.

   *   When using KEEP CURSOR WITH NOLOCKS, be aware that data at the
       cursor position may be lost before the next FETCH:

          *   If another transaction deletes the current row, ALLBASE/SQL
              will return the next row.  No error message is displayed.

          *   If another transaction deletes the table being accessed,
              the user will see the message TABLE NOT FOUND   (DBERR 137)

Figure 8-3 shows the effect of KEEP CURSOR WITH NOLOCKS.


          Figure 8-3.  Cursor Operation Using KEEP CURSOR WITH NOLOCKS 

KEEP CURSOR and Isolation Levels 

The KEEP CURSOR option retains the current isolation level (RR, CS, or
RC) that you have specified in the BEGIN WORK command.  Moreover, the
exact pattern of lock retention and release for cursors opened using KEEP
CURSOR WITH LOCKS depends on the current isolation level.  With the READ
COMMITTED isolation level, no locks are maintained across transactions
because locks are released at the end of the FETCH. Therefore, KEEP
CURSOR WITH LOCKS does not make sense at a RC isolation level.

For additional information on isolation levels, refer to the chapter
"Controlling Performance" in the ALLBASE/SQL Database Administration 
Guide.

KEEP CURSOR and BEGIN WORK 

   *   ALLBASE/SQL automatically begins a transaction whenever you issue
       a command if a transaction is not already in progress.  Thus,
       although you can code an explicit BEGIN WORK to start
       transactions, it is not necessary to do so unless you wish to
       specify an isolation level other than RR.

   *   With KEEP CURSOR, an implicit BEGIN WORK follows immediately after
       you perform a COMMIT WORK, so if you do an explicit BEGIN WORK,
       ALLBASE/SQL returns an error message stating that a transaction is
       already in progress.  If this problem should arise, re-code to
       eliminate the BEGIN WORK from the loop.

KEEP CURSOR and COMMIT WORK 

   *   When the KEEP CURSOR option of the OPEN command is activated for a
       cursor, COMMIT WORK may or may not release locks associated with
       the cursor depending on the setting of the WITH LOCKS/WITH NOLOCKS
       option.

   *   COMMIT WORK does not close cursors opened with the KEEP CURSOR
       option.  COMMIT WORK does end the previous implicit transaction
       and starts an implicit transaction with the same isolation level
       as that specified with the BEGIN WORK command.

   *   Remember that COMMIT WORK will still close all cursors opened
       without the KEEP CURSOR option.

KEEP CURSOR and ROLLBACK WORK 

   *   When the KEEP CURSOR option is activated for an opened cursor, all
       locks are released when you ROLLBACK WORK, whether or not you have
       specified WITH LOCKS or WITH NOLOCKS. The position of the cursor
       is restored to what it was at the beginning of the transaction
       being rolled back.  The current transaction is ended and a new
       transaction is implicitly started with the same isolation level as
       specified in the BEGIN WORK command.

   *   Remember that ROLLBACK WORK closes all cursors that you opened
       during the current transaction, whether opened with or without the
       KEEP CURSOR option.  Thus it is important to do a COMMIT WORK
       after opening a cursor with the KEEP CURSOR option.

   *   When a cursor is opened with the KEEP CURSOR option, ROLLBACK WORK
       TO SavePoint is not allowed.

KEEP CURSOR and Aborted Transactions 

   *   When a transaction is aborted by ALLBASE/SQL, the cursor position
       is retained, and a new transaction begins, as with ROLLBACK WORK.

   *   Remember that when a transaction aborts all cursors that you
       opened during the current transaction are closed, whether opened
       with or without the KEEP CURSOR option.  Thus it is important to
       do a COMMIT WORK after opening a cursor with the KEEP CURSOR
       option.

   *   The use of multiple cursors may require frequent examination of
       several system catalog tables.  This means acquiring exclusive
       locks, which creates the potential for deadlock.  However, the
       behavior of aborted transactions with KEEP CURSOR lets you create
       automatic deadlock handling routines.  Simply repeat the operation
       until deadlock does not occur.

Writing Keep Cursor Applications 

Because of the potential for deadlock, you must be careful to test for
that condition frequently in applications using KEEP CURSOR. Use the
following steps to create your code:

   1.  Declare all cursors to be used in the application.

   2.  Use a loop to test for a deadlock condition as you open all
       cursors that will use the KEEP CURSOR option.  Start the loop with
       a BEGIN WORK statement that specifies the isolation level, then
       include a separate test for non-deadlock errors for each OPEN
       statement.  Create an SQLStatusCheck routine to display all error
       messages and RELEASE the DBEnvironment in the event of fatal
       errors.

   3.  Use the COMMIT WORK command.  If you do not COMMIT at this point,
       an aborted transaction will roll back all the OPEN statements, and
       you will lose the cursor positions.  The COMMIT starts a new
       transaction and keeps the cursor positions.

   4.  Use a loop to scan your data until all rows have been processed.

          *   First, open any non-kept cursors.  Do not include a COMMIT
              WORK after opening the non-kept cursors.  If a deadlock is
              detected at this point, the transaction will automatically
              be reapplied.

          *   Next, execute any FETCH, UPDATE WHERE CURRENT, or DELETE
              WHERE CURRENT commands.  Be sure to test for unexpected
              errors and branch to SQLStatusCheck to display messages and
              RELEASE in the event of a non-deadlock error.  In the event
              of deadlock, the transaction will automatically be
              reapplied.

          *   At the end of the loop, include a COMMIT WORK. This will
              commit your data to the database, and it will close any
              non-kept cursors opened so far in the program.  It will
              also start a new transaction and maintain the cursor
              position of all kept cursors.

          *   Place any terminal or file I/O after this COMMIT, in order
              to prevent duplicate messages from appearing in the event
              of a rollback because of deadlock.

   5.  Once the program is finished scanning the tables, you should close
       all kept cursors within a final loop which tests for a deadlock
       condition.  Once again, test for unexpected errors and branch to
       SQLStatusCheck if necessary.

   6.  Execute a final COMMIT WORK to release the KEEP state.

MPE/iX 5.0 Documentation