 |
» |
|
|
|
|
|
5.48 How does a cursor opened with keep cursor behave?
Key points
- If you open a cursor using the
KEEP CURSOR clause and you issue a COMMIT
WORK or a ROLLBACK WORK statement without explicitly closing the cursor,
ALLBASE/SQL does not normally close the cursor:
- If you issue a
COMMIT WORK statement while the cursor is open, ALLBASE/SQL
does not close the cursor. Instead, a new transaction is automatically
started (a BEGIN WORK statement is implicitly issued). From the
user's standpoint, it appears that the old transaction has not terminated: the
new transaction has the same isolation level and priority as the transaction
that was just terminated, and the cursor's position in the active set is
maintained in the new transaction. For example, if a cursor's active set
covers four rows, and a COMMIT WORK is issued after the first and second row
had been fetched and modified, the cursor would still be pointing to the
second row at the beginning of the new transaction. When the next FETCH is
issued (in the new transaction), the cursor will move to the third row.
- If you issue a
ROLLBACK WORK statement while the cursor is open, one of
the following occurs:
- If you have not issued a
COMMIT WORK since you issued the OPEN statement,
the ROLLBACK WORK statement closes the cursor and undoes any changes made
through it. The KEEP CURSOR option only takes effect if the
the OPEN statement is committed.
- If you have issued a
COMMIT WORK since you issued the OPEN statement,
the ROLLBACK WORK statement only rolls back the last transaction that was
implicitly started, and then starts another transaction. The new transaction
has the same isolation level and priority as the terminated transaction, and
the cursor's position is reset to the same position that it had at the
beginning of the terminated transaction (this position also is the position
held at the end of the last committed transaction).
- If you attempt to issue a
ROLLBACK WORK TO SAVEPOINT statement while the
cursor is open, an error will be returned. ROLLBACK WORK TO SAVEPOINT is not
allowed with cursors opened with KEEP CURSOR.
- When KEEP CURSOR is used, the logical end of the transaction occurs during
the
COMMIT WORK statement that immediately follows the CLOSE cursor statement.
- An explicit
CLOSE statement is required if you have committed an OPEN
statement that includes the KEEP CURSOR clause. If KEEP CURSOR is in effect,
the cursor remains open when you issue a COMMIT WORK or a ROLLBACK WORK
statement. The cursor will only be closed when a CLOSE statement is
explicitly issued and a COMMIT WORK is issued.
In ALLBASE/SQL, X locks are still retained until the end of any
transaction (until a COMMIT WORK or ROLLBACK WORK is issued). The
KEEP CURSOR feature actually generates many small ALLBASE/SQL transactions to
create the illusion of a single, logically continuous user transaction in
which X locks can be released before the end of the transaction.
Page last updated on November 29, 1995
|
|
|
Printable version
