Specifying column names in the for update clause

Adaptive Server acquires update locks on the pages or rows of all tables that have columns listed in the for update clause of the cursor select statement. If the for update clause is not included in the cursor declaration, all tables referenced in the from clause acquire update locks.

The following query includes the name of the column in the for update clause, but acquires update locks only on the titles table, since price is mentioned in the for update clause. The table uses allpages locking. The locks on authors and titleauthor are shared page locks:

declare curs3 cursor
for
select au_lname, au_fname, price
    from titles t, authors a,
        titleauthor ta
where advance <= $1000 
    and t.title_id = ta.title_id
    and a.au_id = ta.au_id
for update of price

Table 14-4 shows the effects of:

Omitting the for update clause entirely—no shared clause
Omitting the column name from the for update clause
Including the name of the column to be updated in the for update clause
Adding shared after the name of the titles table while using for update of price

In this table, the additional locks, or more restrictive locks for the two versions of the for update clause are emphasized.

**Table 14-4: Effects of for update clause and shared on cursor locking**
Clause	titles	authors	titleauthor
None	sh_page on data	sh_page on index sh_page on data	sh_page on data
for update	updpage on index updpage on data	updpage on index updpage on data	updpage on data
for update of price	updpage on data	sh_page on index sh_page on data	sh_page on data
for update of price + shared	sh_page on data	sh_page on index sh_page on data	sh_page on data