Cursors and Locking

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.

Note: SAP ASE 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.

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.

Related concepts
select for update