Optimistic locking restrictions and considerations

This topic lists optimistic locking restrictions that you must be aware of.

  • ROW CHANGE TIMESTAMP columns are not supported in the following keys, columns, and names (sqlstate 429BV is returned if used):
    • Primary keys
    • Foreign keys
    • Multidimensional clustering (MDC) columns
    • Table partitioning columns
    • Database hashed partitioning keys
    • DETERMINED BY constraint columns
    • Nicknames
  • The RID() function is not supported in partitioned database configurations.
  • Online or offline table reorg performed between the fetch and update operations in an optimistic locking scenario can cause the update to fail, but this should be handled by normal application re-try logic.
  • The IMPLICITLY HIDDEN attribute is restricted to only ROW CHANGE TIMESTAMP columns for optimistic locking.
  • Inplace reorg is restricted for tables where a ROW CHANGE TIMESTAMP column was added to an existing table until all rows are guaranteed to have been materialized (SQL2219, reason code 13, is returned for this error). This can be accomplished with a LOAD REPLACE command or with a classic table reorg. This will prevent false positives. Tables created with the ROW CHANGE TIMESTAMP column have no restrictions.

Considerations for implicitly hidden columns

A column defined as IMPLICITLY HIDDEN is not part of the result table of a query that specifies * in a SELECT list. However, an implicitly hidden column can be explicitly referenced in a query.

If a column list is not specified on the insert, then the VALUES clause or the SELECT LIST for the insert should not include this column (in general, it must be a generated, defaultable, or nullable column).

For example, an implicitly hidden column can be referenced in the SELECT list, or in a predicate in a query. Additionally, an implicitly hidden column can be explicitly referenced in a CREATE INDEX statement, ALTER TABLE statement, INSERT statement, MERGE statement, or UPDATE statement. An implicitly hidden column can be referenced in a referential constraint. A REFERENCES clause that does not contain a column list refers implicitly to the primary key of the parent table. It is possible that the primary key of the parent table includes a column defined as implicitly hidden. Such a referential constraint is allowed.

  • If the SELECT list of the fullselect of the materialized query definition explicitly refers to an implicitly hidden column, that column will be part of the materialized query table. Otherwise, an implicitly hidden column is not part of a materialized query table that refers to a table containing an implicitly hidden column.

  • If the SELECT list of the fullselect of a view definition (CREATE VIEW statement) explicitly refers to an implicitly hidden column, that column will be part of the view, (however the view column is not considered to be hidden). Otherwise, an implicitly hidden column is not part of a view that refers to a table containing an implicitly hidden column.

Considerations for Label Based Access Control (LBAC)

When a column is protected under LBAC, access by a user to that column is determined by the LBAC policies and the security label of the user. This protection, if applied to a row change timestamp column, extends to the reference to that column via both the ROW CHANGE TIMESTAMP and ROW CHANGE TOKEN expressions which are derived from that column.

Therefore when determining the security policies for a table, ensure that the access to the row change timestamp column is available for all users which need to use optimistic locking or time based update detection as appropriate. Note that if there is no row change timestamp column then the ROW CHANGE TOKEN expression cannot be blocked by LBAC. However, if the table is altered to add a row change timestamp column then any LBAC considerations will then apply.