Enabling optimistic locking in applications

There are a number of steps that you must perform in order to enable optimistic locking support in your applications.

Procedure

  1. In the initial query, SELECT the row identifier (using the RID_BIT() and RID() built-in function) and ROW CHANGE TOKEN for each row that you need to process.
  2. Release the row locks so that other applications can SELECT, INSERT, UPDATE and DELETE from the table.
  3. Perform a searched UPDATE or DELETE on the target rows, using the row identifier and ROW CHANGE TOKEN in the search condition, optimistically assuming that the unlocked row has not changed since the original SELECT statement
  4. If the row has changed, the UPDATE operation will fail and the application logic must handle the failure. For instance, the application retries the SELECT and UPDATE operations.

What to do next

After running the preceding steps:
  • If the number of retries performed by your application seems higher than expected or is desired, then adding a row change timestamp column to your table to ensure that only changes to the row identified by the RID_BIT function will invalidate only the ROW CHANGE TOKEN, and not other activity on the same data page.
  • To see rows which have been inserted or updated in a given time range, create or alter the table to contain a row change timestamp column. This column will be maintained by the database manager automatically and can be queried using either the column name or the ROW CHANGE TIMESTAMP expression.
  • For row change timestamp columns only, if the column is defined with the IMPLICITLY HIDDEN attribute, then it is not externalized when there is an implicit reference to the columns of the table. However, an implicitly hidden column can always be referenced explicitly in SQL statements. This can be useful when adding a column to a table can cause existing applications using implicit column lists to fail.