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.

Optimistic locking protects against potential database integrity problems brought on by lowering the isolation level. The HCL Commerce implementation utilizes an optimistic predicate column for each table in the HCL Commerce database OPTCOUNTER. This ensures 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

The MemberLock class prevents optimistic lock exceptions due to concurrent updates of the same data by multiple threads, which may be running in different application server JVMs. It does so by allowing only one request thread at a time to execute requests that can potentially modify user related data for a particular user ID. The MemberLock class first obtains a unique Java lock for the user ID, thus synchronizing threads executing in the same JVM, and then obtains a cross JVM lock, usually a database update lock on a row of the MEMBER table, thus synchronizing threads executing in different JVMs.

Locks are never obtained for the generic user ID (-1002).

HCL Commerce Version 9.1.10.0 or laterA remote cache based lock can be used instead of a database update lock.

The following related configuration settings can be added to the InstanceProperties tag in the wc-server.xml file:
<com.ibm.commerce.user.beans.MemberLock maxWaitSeconds="90" 
     cacheBasedLockingEnabled="false" noLockMemberIds="" spiUserSessionTimeoutOverrideSeconds="1800" />
<OptimisticLockingSelectForUpdate MEMBER.com.ibm.commerce.user.beans.MemberLock="enabled" 
     com.ibm.commerce.user.beans.MemberLock.HitAndMiss="enabled" com.ibm.commerce.user.beans.MemberLock.Miss="enabled" />
Where:
maxWaitSeconds
This setting specifies the maximum number of seconds a thread waits to obtain the Java lock. The default value is 90 seconds. If a thread has to wait more than the specified maximum, the thread resumes execution as if it had obtained the lock, but it does not attempt to obtain a cross JVM lock.
HCL Commerce Version 9.1.10.0 or latercacheBasedLockingEnabled
HCL Commerce Version 9.1.10.0 or laterSpecify cacheBasedLockingEnabled="true" to use a remote cache based lock for cross JVM locking. The default value is false.
noLockMemberIds
Use this setting to specify a list of white-space separated numeric user IDs for which member locks should never be obtained. The default value is an empty list.
spiUserSessionTimeoutOverrideSeconds
The SPI user ID -1020 is a special system user ID which does not normally require synchronization, except when its session expires. If it expires it requires an update to its rows in the CTXDATA and CTXMGMT database tables.

The spiUserSessionTimeoutOverrideSeconds setting specifies the number of seconds before the session of the SPI user ID expires. By specifying a larger number, the frequency of these updates is significantly reduced. Specify 0 to do normal locking and session expiry, as would be done for a normal user ID (a value of 0 is not normally recommended, but may sometimes be useful for problem determination). The default value is 1800 seconds (30 minutes).

MEMBER.com.ibm.commerce.user.beans.MemberLock
This setting has no effect when cacheBasedLockingEnabled is true. Specify never to disable cross JVM locking using database locking. The default value is enabled.
com.ibm.commerce.user.beans.MemberLock.HitAndMiss
When this setting is enabled, a member lock is obtained when the UserAccessBean is used. The default value is enabled. Specify a value of never to avoid this member locking.
com.ibm.commerce.user.beans.MemberLock.Miss
When this setting is enabled, a member lock is obtained when the UserAccessBean experiences a cache miss. The default value is enabled. Specify a value of never to avoid this member locking.