Cursor-locking methods are similar to other locking methods for Adaptive Server. In general, statements that read data (such as select or readtext) use shared locks on each data page to avoid reading changed data from an uncommitted transaction. Update statements use exclusive locks on each page they change. To reduce deadlocks and improve concurrency, Adaptive Server often precedes an exclusive lock with an update lock, which indicates that the client intends to change data on the page.
For updatable cursors, Adaptive Server uses update locks by default when scanning tables or views referenced with the for update clause of declare cursor. If the for update clause is included, but the list is empty, all tables and views referenced in the from clause of the select_statement receive update locks by default. If the for update clause is not included, the referenced tables and views receive shared locks. You can use shared locks instead of update locks by adding the shared keyword to the from clause after each table name for which you prefer a shared lock.
In insensitive cursors, the base table lock is released after the worktable is fully populated. In semi-sensitive scrollable cursors, the base table lock is released after the last row of the result set has been fetched once.
Adaptive Server releases an update lock when the cursor
position moves off the data page. Since an application buffers rows
for client cursors, the corresponding server cursor may be positioned
on a different data row and page than the client cursor. In this
case, a second client could update the row that represents the current
cursor position of the first client, even if the first client used
the for update option.
Any exclusive locks acquired by a cursor in a transaction are held until the end of that transaction. This also applies to shared or update locks when you use the holdlock keyword or the set isolation level 3 option. However, if you do not set the close on endtran option, the cursor remains open past the end of the transaction, and its current page lock remains in effect. It can also continue to acquire locks as it fetches additional rows.
See the Performance and Tuning Series: Locking and Concurrency Control.
