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.

Important: By default most HCL Commerce code runs database queries at specified isolation levels that are within safe tolerances for those individual queries. You can ensure optimal performance of your site by setting the default isolation level for WebSphere Application Server to Cursor Stability. For more information about how to set the default level of isolation for WebSphere Application Server, see Changing the default isolation level for non-CMP applications and describing how to do so using a new custom property webSphereDefaultIsolationLevel.

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 HCL Commerce implementation utilizes an optimistic predicate column for each table in the HCL 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.

HCL 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 HCL 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 utilities_root/schema/9.0.0.0/db_type/wcs.perf.trigger.sql 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 HCL Commerce. OCC is implemented for most HCL Commerce tables by default, for performance reasons. Changing these tables' use of OCC is not recommended.

When you add tables to the HCL 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.

Note: If custom code uses the access bean to update order-related tables and receives an optimistic locking exception, it may be possible that this is because the same record was altered by an out-of-the-box stored procedure in the same transaction. To fix the problem, the custom code must first call the entity manager to refresh the access bean's entity before updating the access bean.
Note: It is possible that the entity bean being detached from the entity manager is causing the custom code to use the access bean to update some database table, but this is not persisted in the database. You can call entity manager to refresh the entity to confirm that this is the problem. If the access bean is detached, the entity manager will throw an exception stating that the entity is detached when calling refresh. The custom code must call the entity manager merge method to reattach the access bean to the entity manager in order to resolve this issue.

MemberLock configuration options

Due to database deadlocks, clients may see error messages when attempting to log in. You should modify your custom code to prevent the use of the UserAccessBean. Instead, use the UserDataBean or the beans delivered by the WCDataCache.newUserAccessBean method (if available). You should also enable serialisation by using HitAndMiss settings, which is explained below. Lastly, when enabling serialisation in operation, you should execute performance and functional test validations using realistic high volume task loads.

All requests for a specific user session are sent to the same server when session affinity is enabled. Deadlocks may sometimes be prevented by serialising all threads that read a specific User EJB (except the Generic User, with id -1002).

By acquiring Java locks the HCL Commerce Data Cache is used to get entry to User EJB objects, irrespective of whether or not the Data Cache is enabled. To take advantage of this feature, custom code, such as a custom Logon command, you should avoid using the UserAccessBean class directly and instead use the UserDataBean class, or use WCDataCache.newUserAccessBean if available.

This serialisation feature may be turned on or off, and it is disabled by default. The functionality may be activated by adding the following option to the wc-server.xml instance configuration file's <InstanceProperties> tag:

<OptimisticLockingSelectForUpdate
com.ibm.commerce.user.beans.MemberLock.HitAndMiss="enabled"
/>

The above configuration causes the Java lock to be obtained by the Data Cache on both the caches, cache hit and on cache miss. An alternate setting can be given for improved concurrency but with much less safety from probable deadlocks:

<OptimisticLockingSelectForUpdate
com.ibm.commerce.user.beans.MemberLock.Miss="enabled"
/>

The Java lock is only obtained if there is a cache miss with the above configuration.

Serialization of threads can cause threads to hang if the threads involved are holding resources, such as database locks. The Java locking offered in this patch includes protections.

The first security feature is that throughout a database transaction, every thread will only wait for locks up to 10 seconds. If somehow the 10 second threshold is exceeded, this thread will not be able to attain locks for the remaining transaction, and the original deadlock or optimistic concurrency error may occur.

The second security feature is that Java locks are automatically released after being held for 60 seconds. Due to that 60 second timeout, another thread may be able to obtain the lock and execute concurrently with the thread that held the lock before its expiration, and the original deadlock or optimistic concurrency exception may occur.

The functionality may be activated by adding the following option to the wc-server.xml instance configuration file's <InstanceProperties> tag:
<com.ibm.commerce.user.beans.MemberLock threshold="10"
timeout="60" />