After you specify KEEP CURSOR in an OPEN statement, a
COMMIT WORK does not close the cursor, as it normally does.
Instead, COMMIT WORK keeps the cursor open and
begins a new transaction
while maintaining the 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 on the page of data corresponding to the current cursor
position are either held (the default) or released, depending on whether
you specify WITH LOCKS or WITH NOLOCKS.
The KEEP CURSOR option retains the current isolation level (RR,
CS, or RC) that you have
specified in the BEGIN WORK statement. 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 RC and RU isolation levels, no locks are
maintained across transactions because locks are released at the end of
the FETCH. Therefore,
KEEP CURSOR WITH LOCKS has no effect on locks
at the RC or RU isolation levels. Also,
the WITH LOCKS option releases exclusive (X) locks on user tables
at COMMIT WORK time in transactions using RC and RU.
Remember that system catalog tables are locked at the RR isolation
level; exclusive locks on these tables are retained until the CLOSE
cursor statement that is immediately followed by a COMMIT WORK.
For more information and examples, refer to the chapter
"Processing with Cursors"
in the appropriate ALLBASE/SQL Application Programming Guide.
