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.

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 all locks not associated with the kept cursor and begins a new transaction while maintaining the current (kept) 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 pages corresponding to the current kept cursor are either held until after the transaction ends (the default) or released depending on whether you specify WITH LOCKS or WITH NOLOCKS. (Pages held include data and system pages.)

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.

KEEP CURSOR and Isolation Levels

The KEEP CURSOR option retains the current isolation level 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. For example:

With the CS isolation level, KEEP CURSOR maintains locks until the next FETCH is completed. See Figure 8-2.
With the RC isolation level, KEEP CURSOR maintains locks only until the current FETCH is completed; no locks are maintained across transactions. Therefore, KEEP CURSOR WITH LOCKS does not retain locks at the RC isolation level.

For additional information on isolation levels, refer to the .

KEEP CURSOR and Declaring for Update

When you DECLARE a cursor for UPDATE, SIX locks are obtained at the page level rather than share locks. There is less concurrency and less chance of deadlock because lock promotion is unnecessary. Although concurrency is reduced, throughput is often improved due to the reduction in deadlock recovery overhead.

OPEN Command Without KEEP CURSOR

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, 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 and CS Isolation Level

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 associated with the cursor 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, release all locks associated with the cursor, 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 associated with the cursor 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.
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 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, unless the cursor was opened with the KEEP CURSOR option and its position saved with a COMMIT WORK immediately following the the OPEN command.
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 unless the cursor was opened with the KEEP CURSOR option and its position saved with a COMMIT WORK immediately following the the OPEN command.
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. The technique is shown under "Examples," below.

Writing Keep Cursor Applications

A skeleton outline of a KEEP CURSOR application showing the sections and specific code examples follow appear below.

Because of the potential for deadlock, you must be careful to test for that condition frequently in applications using KEEP CURSOR. An aborted transaction results when a deadlock is encountered. (There is no need to test for deadlock following a COMMIT WORK or a BEGIN WORK command.) Use the following steps to create your code:

Declare all cursors to be used in the application.
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 S100-SQL-STATUS-CHECK routine to display all error messages and RELEASE the DBEnvironment in the event of fatal errors. See the "Examples" section below.
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.
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 and the transaction aborted, the program reapplies the transaction.
- Next, execute any FETCH, UPDATE WHERE CURRENT, or DELETE WHERE CURRENT commands. Be sure to test for unexpected errors and branch to S100-SQL-STATUS-CHECK to display messages and RELEASE in the event of a non-deadlock error. Again, if a deadlock is detected and the transaction aborted, the program reapplies the transaction.
- 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.
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 S100-SQL-STATUS-CHECK if necessary.
Execute a final COMMIT WORK to release the KEEP state.