Planning the enablement of optimistic locking

Since the new SQL expressions and attributes for optimistic locking can be used with no DDL changes to the tables involved, you can easily try optimistic locking in your test applications.

Note that without DDL changes, optimistic locking applications might get more false negatives than with DDL changes. An application that does get false negatives might not scale well in a production environment because the false negatives might cause too many retries. Therefore, to avoid false negatives, optimistic locking target table(s) should be either:
  • Created with a ROW CHANGE TIMESTAMP column
  • Altered to contain the ROW CHANGE TIMESTAMP column
If the recommended DDL changes are done, false negatives will be a rare occurrence. The only false negatives will occur due to table level operations such as reorg, not concurrent applications operating on different rows.

In general, the database manager allows false negatives (online or offline reorg, for example) and the presence of a row change timestamp column is sufficient to determine whether page or row level granularity is being used. You can also query the SYSCAT.COLUMNS for a table that has rows with a YES in the ROWCHANGETIMESTAMP column.

A thorough analysis of the application and database might indicate that this DDL is not required, for example, if there is one row per page, or if the update and delete operations are very infrequent and rarely, or never, on the same data page. Such analysis is the exception.

For the update timestamp detection usage, you must make changes to the DDL for the table, and possibly reorganize the table to materialize the values. If there is concern that these changes could have a negative impact on the production database, you should first prototype the changes in a test environment. For instance, the extra columns can affect the row size limitations and plan selection.

Conditions to be aware of

  • You should be aware of conditions relating to the system clock and the granularity of the timestamp values. If a table has a ROW CHANGE TIMESTAMP column, after an insert or update, the new row will have a unique ROW CHANGE TIMESTAMP value in that table on that database partition.

  • To ensure uniqueness, the generated timestamp of a row will always increase, regardless if the system clock is adjusted backwards or if the update or insertion of data is happening faster than timestamp granularity. Therefore, the ROW CHANGE TIMESTAMP may be in the future compared with the system time and the CURRENT TIMESTAMP special register. Unless the system clock is gets completely out of sync, or the database manager is inserting or updating at more than one million rows per second, then this should normally be very close to the actual time. In contrast to the CURRENT TIMESTAMP, this value is also generated per row at the time of the update, therefore, it is normally much closer than the CURRENT TIMESTAMP, which is generated once for an entire statement that could take a very long time to complete, depending on the complexity and number of rows affected.