Select a stringent level of commitment control

Do not use commitment control unnecessarily. The overhead that is associated with locking not only increases utilization, but also reduces concurrency. However, if your application is not read-only, commitment control might be required.

A common alternative is to use optimistic locking. Optimistic locking involves issuing explicit UPDATEs by using a WHERE clause that uniquely determines a particular record. Optimistic locking ensures that the record does not change after it is retrieved.

Many third-party tools use this approach, which is why they often require a unique index to be defined for updatable tables. This allows the record update to be made by fully qualifying the entire record contents. Consider the following example:

   UPDATE table SET C1=new_val1, C2=new_val2, C2=new_val3
      WHERE C1=old_val1 AND C2=old_val2 AND C3=old_val3

In the 6.1 release, Db2® for i added 'ROW CHANGE TIMESTAMP' support. This allows an application to create a table with a ROW CHANGE TIMESTAMP column, which can be used together with a ROW CHANGE expression and the RID function to guarantee row uniqueness without the need to cache all the columns for each of the rows that might be subsequently changed. This is a better solution for optimistic locking, in that it does not require row locks to be maintained and it does not require caching of every column value in the row to be updated. See the following example:

 /*Add a row change timestamp column (called 'RCT' in this example)*/
 /*  to the table when it is created                               */
  CREATE TABLE TABLEX (col1 int,..., RCT GENERATED ALWAYS FOR
   EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);
                  : 
  /*Add the ROW CHANGE TOKEN expression and the RID function to the */
  /* select list of the query (note : a ROW CHANGE expression which */
  /*specifies the TIMESTAMP or the column itself can also be used   */
  /*in the query. See the SQL Reference for more details).          */
  /* Note that locks on the rows read by the query do not need to be*/
  /* held.                                                          */
 SELECT ROW CHANGE TOKEN FOR tablex,RID(tablex),col1,....,
     FROM TABLEX WHERE ...  
                  : 
  /* For each row, cache away just the value from the ROW CHANGE    */
  /* TOKEN and the value for the result of the RID function.        */
  /* When a row qualifies to be updated, just the ROW CHANGE        */
  /* TOKEN value and the RID()function value need to be specified in*/
  /*  the criteria for the UPDATE.                                  */
UPDATE table SET Col1=new_val1, Col2=new_val2,... WHERE ROW CHANGE
 TOKEN for tablex = <saved value> and RID(tablex) = <saved RID value>
     : 

If the UPDATE statement returns a 'row not found' error, this indicates that the row you attempted to update has been updated or deleted since the time it was read. See the SQL Reference for more details on ROW CHANGE expression and the RID() function.

If commitment control is required, use the lowest level of record locking possible. For example, use *CHG: over *CS when possible, and never use *ALL when *CS provides what you require.