Cursors and locking

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:

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.