Recover from transaction failures due to optimistic locking
There are three main strategies that you can use to recover from failing transactions if there are collisions in the database due to optimistic locking: Serialization, Retry, Do nothing. These strategies are contingent on the relative importance or criticality of the transaction in question.
Procedure
- Serialization
This first strategy is to serialize the transaction that is failing. Serialization is the specific enforcement of the order of operations on the database to ensure that no other transactions update the same result set between the time a transaction reads and writes a result set. In other words, serialization over a certain set of database operations offers the temporary reversal of the freedom that optimistic locking offers.
For example, if many different users perform a certain transaction often, the likelihood of having collisions during the same set of operations is high. Serialization will help by funneling the users through that section of operations one-at-a-time by temporarily switching to a more pessimistic strategy over that critical section of code.
Serialization is of benefit when:
- The transaction is critical in nature, and you do not want to fail on critical transactions.
- The update to the database is likely to have a high frequency of collisions.
By default, the INVENTORY table is serialized using select for update.
Serialization can potentially increase throughput for that operation when the error rate is high without it (that is, frequent collisions occur) by avoiding to retry the operation.
The appearance of one of the following exceptions in the WebSphere Commerce log indicates that an optimistic update failure may have occurred:
- com.ibm.ejs.persistence.OptimisticUpdateFailureException: executeUpdate returned zero rows updated
- This is the exception that occurs when the failure occurs when the EJB container attempts to update the entity bean.
- com.ibm.commerce.base.helpers.ECJDBCOptimisticUpdateFailureException
- This is the exception that occurs when the failure occurs when a session bean attempts to update a row in a database table using a JDBC database connection.
- To force (or serialize) the SELECT and UPDATE statements to occur one after the other, change the isolation level of the current operation to ReadStability by changing the problematic SELECT statement to SELECT . . . FOR UPDATE WITH RS.
- The SELECT statement should be changed to SELECT . . .FOR UPDATE.
- Retry
The second strategy is to retry the command that fails. There is a mechanism in the WebSphere Commerce Server engine that allows you to rerun a command on failure.
See the discussion of the isRetriable method.
Retrying a command that is failing is beneficial when there is a low probability of collision. Retrying a command is more expensive relative to serialization, but for infrequently colliding operations, it is preferable.
- Do nothing Of course, there is a third alternative that is left to the discretion. If the transactions that are failing are insignificant and the only consequence is for the end-users to have to refresh their browser, you can decide not to do anything further.