Cursor-locking methods are similar to other locking methods for SAP ASE. 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.
If you run select for update within a cursor context, the cursor open and fetch statements must be within the context of a transaction.
To reduce deadlocks and improve concurrency, SAP ASE often precedes an exclusive lock with an update lock, which indicates that the client intends to change data on the page.
For updatable cursors, SAP ASE 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 semisensitive scrollable cursors, the base table lock is released after the last row of the result set has been fetched once.
For more information on select for update, see the Reference Manual:Commands.
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.