Using KEEP CURSOR [ ALLBASE/SQL C Application Programming Guide ] MPE/iX 5.0 Documentation
ALLBASE/SQL C Application Programming Guide
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
locks not associated with the kept cursor and 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.
(Pages held include data and system pages.)
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
"Concurrency Control through Locks and Isolation Levels" in the
ALLBASE/SQL Reference Manual .
OPEN Command Without KEEP CURSOR
Figure 6-1 shows the operation of cursors when you do not select the KEEP
CURSOR option.
Click here to view figure.
Figure 6-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 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 accociated 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 6-2 shows the effect of the KEEP CURSOR WITH LOCKS.
Click here to view figure.
Figure 6-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
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 6-3 shows the effect of KEEP CURSOR WITH NOLOCKS.
Click here to view figure.
Figure 6-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 was saved with a COMMIT
WORK command immediately following 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 was
saved with a COMMIT WORK command immediately following 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
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. See the "Examples" section below.
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.
Figure 6-4 is a skeleton outline of a KEEP CURSOR application showing the
sections outlined above. Specific examples follow in the next section.
___________________________________________________________________
| |
| \ |
| /* First, Declare all your Kept and Non-Kept Cursors */\|
| 1 |
| |
| do { /* Open Kept Cursors */\ |
| EXEC SQL BEGIN WORK [RR/CS/RC];\ |
| /* Open Kept Cursors in a Loop */\ |
| }while (!DeadLockFree); 2 |
| |
| \ |
| EXEC SQL COMMIT WORK; /* COMMIT to Save Cursor Positions */\|
| 3 |
| |
| |
| do {\ |
| /* Open any Non-Kept Cursors */\ |
| \ |
| /* Execute SQL Commands, i.e., FETCH, UPDATE, etc */\ |
| \ |
| if (DeadLockFree) \ |
| {\ |
| EXEC SQL COMMIT WORK; /* Save Cursor Positions */\|
| /* Write any messages or output to file or terminal */\|
| };\ |
| }while (!EndofScan); 4 |
| |
| |
| do {\ |
| \ |
| /* Close all Cursors Opened with KEEP CURSOR Option */\|
| \ |
| }while (!DeadlockFree); 5 |
| |
| \ |
| EXEC SQL COMMIT WORK; /* Final COMMIT to end KEEP state */\|
| 6 |
___________________________________________________________________
Figure 6-4. Keep Cursor Application Program
MPE/iX 5.0 Documentation
HP 3000 Manuals