Enabling optimistic concurrency control for custom tables

Most WebSphere Commerce tables use optimistic locking for database performance reasons. A similar functionality is provided for the data service layer, called optimistic concurrency control. Optimistic concurrency control can slightly decrease performance but allows for much greater concurrency during heavy database load, because it allows for a reduction in the transaction isolation level, reducing locking.

About this task

Note: Not implementing optimistic locking on custom tables can result in data corruption when data collision occurs.

To enable optimistic concurrency control for custom tables:

Procedure

  1. Add an OPTCOUNTER column (of type SMALLINT or INTEGER) to your table. The column could be nullable.
  2. Follow the steps in Building metadata and data objects to reflect Data Service Layer schema changes, specifying your custom table.
    The metadata and physical SDO classes are generated with the inclusion of the OPTCOUNTER column, and optimistic concurrency control is enabled.

    That is, when following the steps, you select the custom tables you have added to the schema, and select the columns you want to include in the SDO class. Primary key columns, non-nullable columns, and the optcounter column are automatically selected. Primary key columns are not available, because you must include them in the SDO.