When declaring an updatable cursor using the for update clause, you can tell Adaptive Server to use shared page locks (instead of update page locks) in the declare cursor statement:
declare cursor_name cursor for select select_list from {table_name | view_name} shared for update [of column_name_list]
This allows other users to obtain an update lock on the table or an underlying table of the view.
You can use the holdlock keyword in conjunction with shared after each table or view name. holdlock must precede shared in the select statement. For example:
declare authors_crsr cursor for select au_id, au_lname, au_fname from authors holdlock shared where state != ’CA’ for update of au_lname, au_fname
These are the effects of specifying the holdlock or shared options when defining an updatable cursor:
If you do not specify either option, the cursor holds an update lock on the row or on the page containing the current row.
Other users cannot update, through a cursor or otherwise, the row at the cursor position (for datarows-locked tables) or any row on this page (for allpages and datapages-locked tables).
Other users can declare a cursor on the same tables you use for your cursor, and can read data, but they cannot get an update or exclusive lock on your current row or page.
If you specify the shared option, the cursor holds a shared lock on the current row or on the page containing the currently fetched row.
Other users cannot update, through a cursor or otherwise, the current row, or the rows on this page. They can, however, read the row or rows on the page.
If you specify the holdlock option, you hold update locks on all the rows or pages that have been fetched (if transactions are not being used) or only the pages fetched since the last commit or rollback (if in a transaction).
Other users cannot update, through a cursor or otherwise, currently fetched rows or pages.
Other users can declare a cursor on the same tables you use for your cursor, but they cannot get an update lock on currently fetched rows or pages.
If you specify both options, the cursor holds shared locks on all the rows or pages fetched (if not using transactions) or on the rows or pages fetched since the last commit or rollback.
Other users cannot update, through a cursor or otherwise, currently fetched rows or pages.