Cursor locking methods are similar to the other locking methods in Adaptive Server. For cursors declared as read only or declared without the for update clause, Adaptive Server uses a shared page lock on the data page that includes the current cursor position.
When additional rows for the cursor are fetched, Adaptive Server acquires a lock on the next page, the cursor position is moved to that page, and the previous page lock is released (unless you are operating at isolation level 3).
For cursors declared with for update, Adaptive Server uses update page locks by default when scanning tables or views referenced with the for update clause of the cursor.
If the for update list is empty, all tables and views referenced in the from clause of the select statement receive update locks. An update lock is a special type of read lock that indicates that the reader may modify the data soon. An update lock allows other shared locks on the page, but does not allow other update or exclusive locks.
If a row is updated or deleted through a cursor, the data modification transaction acquires an exclusive lock. Any exclusive locks acquired by updates through a cursor in a transaction are held until the end of that transaction and are not affected by closing the cursor.
This is also true of shared or update locks for cursors that use the holdlock keyword or isolation level 3.
The following describes the locking behavior for cursors at each isolation level:
At level 0, Adaptive Server uses no locks on any base table page that contains a row representing a current cursor position. Cursors acquire no read locks for their scans, so they do not block other applications from accessing the same data.
However, cursors operating at this isolation level are not updatable, and they require a unique index on the base table to ensure accuracy.
At level 1, Adaptive Server uses shared or update locks on base table or leaf-level index pages that contain a row representing a current cursor position.
The page remains locked until the current cursor position moves off the page as a result of fetch statements.
At level 2 or 3, Adaptive Server uses shared or update locks on any base table or leaf-level index pages that have been read in a transaction through the cursor.
Adaptive Server holds the locks until the transaction ends; it does not release the locks when the data page is no longer needed or when the cursor is closed.
If you do not set the close on endtran or chained options, a cursor remains open past the end of the transaction, and its current page locks remain in effect. It may also continue to acquire locks as it fetches additional rows.
