Using select for update

If the configuration parameter select for update is set to 1, the rows selected by select for update are exclusively locked provided that the command is executed on a datarows-locked table within a transaction context, or in chained mode. If select for update is run within a cursor context, the cursor open and fetch statements must be within the context of a transaction.

Rows selected with select for update, within or outside of a cursor context, retain an exclusive lock until the transaction is complete.

Limitations of select for update:
  • select for update is not valid in a subquery block.

  • select for update is applicable only when the select returns rows directly from a base table and not from a work table. You cannot use select for update with queries that have aggregates or group by, computed, union, having, or distinct clauses.

  • More rows may qualify for the actual transaction update than with select for update. These rows may appear in the updated set. Prevent such “phantom” rows by using isolation level 3.

  • During select processing, concurrent select for update tasks may attempt to lock the the same set of rows in different order, causing application deadlocks. However, once select for update completes, subsequent updates to the set of rows are not blocked, and encounter no deadlocks.

  • All existing restrictions on updateable cursors apply to select for update both within and outside of the cursor context. The only difference is that with select for update, the order by clause is supported with an updateable cursor. The limitations and restrictions on updateable cursors apply to both language and execute cursors.

  • All tables referenced by select for update must be from the same database.

  • select for update is supported only on tables with datarows locked.