Using the shared keyword

When declaring an updatable cursor using the for update clause, you can 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 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: