Optimistic locking

Optimistic locking allows you to lower the isolation level that you use in an application so that fewer locks are placed on the database assets. It allows more applications to run concurrently against the database, and potentially increase the throughput of your applications.

To protect against potential database integrity problems brought on by lowering the isolation level, an optimistic locking implementation has to ensure that no integrity problems occur. The WebSphere Commerce implementation utilizes an optimistic predicate column for each table in the WebSphere Commerce database - OPTCOUNTER. This helps ensure that every time there is a change to the database, the optimistic predicate can be:

1. Updated with a new value
2. Checked for change

In this way, the implementation can guarantee that a row in the database has not changed between the time it was read and the time it was written, to protect the integrity of the database.

With this new freedom that optimistic locking offers, of placing fewer locks on the database, for a shorter period of time, one has to realize that there can also be drawbacks.

A pessimistic locking strategy means the avoidance of optimistic locking issues by placing locks on the database. In other words, pessimistic locking assumes there will be a collision in the database, and takes precautions to avoid these collisions. A pessimistic locking strategy protects the integrity of the database by locking database assets over the entire duration of a transaction, from the beginning to the end. During this period, no other transactions can access the same assets because they are locked. This strategy results in greater wait times for more transactions. It also raises a possibility that these transactions will either time out or potentially become deadlocked.

An optimistic locking strategy can improve the application execution by shortening the window in which these pessimistic consequences can occur. At the same time, it opens itself up to collisions where two or more transactions attempt to update the same row in the database. When this happens in an optimistic locking strategy, rollbacks (or failures) for one or more (or all) of the transactions can occur.

WebSphere Commerce uses an optimistic locking scheme that allows the database transaction isolation level to be lowered from repeatable read to read committed. Using that scheme, database rows not normally accessed concurrently are not locked with an intent to update when they are read. Instead, when the update is eventually made, the row is checked to make sure it has not been updated concurrently since it was read. If it has been updated concurrently, then the transaction is rolled back and the command may be restarted from the beginning in a new transaction, if appropriate. In this scheme, performance is improved when concurrent updates do not normally occur, because the relatively expensive process of obtaining the database locks with intent to update is avoided. On the other hand, for those operations where concurrent updates are more likely to occur, pessimistic locking, whereby intent to update locks are obtained when the row is read, continues to be used, thus avoiding the more expensive process of rolling back and re-starting from the beginning in a new transaction.

For information, see OptCounterInfo.

For optimistic locking to work properly, every query that updates a database table row must increment the OPTCOUNTER column value, or reset it to 1 when the current value is 32767. The WebSphere Commerce server uses this technique. However, if database table rows are updated by other code or manual procedures that do not update the OPTCOUNTER column values, then the database triggers defined in the WC_installdir/schema/db_type/wcs.perf.trigger.sql (where db_type is the database type (DB2, Oracle)) schema files ensure that the OPTCOUNTER column values are incremented properly.

The data service layer supports a mechanism called optimistic concurrency control (OCC) which is similar in function and implementation to the Optimistic Locking used by the EJBs in WebSphere Commerce. OCC is implemented for most WebSphere Commerce tables by default, for performance reasons. Changing these tables' use of OCC is not recommended.

When you add tables to the WebSphere Commerce schema, you can choose to use the OCC functionality as well. You must define a collision column called OPTCOUNTER in the table definition. The OPTCOUNTER column must be of type SMALLINT or INTEGER. The Data Service Layer updates the collision counter column automatically. When there is a collision during saving, an exception is thrown.