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:

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