HP 3000 Manuals HP 3000 Manuals
Using KEEP CURSOR 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.