Recovering from transaction failures due to optimistic locking

There are three main strategies we use to recover from failing transactions if there are collisions in the database due to optimistic locking: Serialization, Retry, Ignore. 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 enforces 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 helps by funneling 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.

  Note: By default, the INVENTORY table is serialized using select for update.

  Serialization can increase throughput for that operation when the error rate is high without it (that is, frequent collisions occur) by avoiding retrying the operation. The appearance of one of the following exceptions in the WebSphere Commerce log indicates that an optimistic update failure might have occurred:

  com.ibm.ejs.persistence.OptimisticUpdateFailureException: executeUpdate returned zero rows updated

  The exception when the failure occurs when the EJB container attempts to update the entity bean.

  com.ibm.commerce.base.helpers.ECJDBCOptimisticUpdateFailureException

  The exception 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.

  • (DB2) Change the problematic SELECT statement to SELECT . . . FOR UPDATE WITH RS.

  • (Oracle) Change the SELECT statement to SELECT . . .FOR UPDATE.

  Note: For the serialization of EJB beans, do not modify existing beans. Instead, introduce JDBC queries that use SELECT ... FOR UPDATE, as shown in the preceding example.

• Retry

  The second strategy is to retry the command that fails. There is a mechanism in the Transaction Server engine that allows us to rerun a command on failure.

  (Developer) See the Making controller commands retriable.

  The default value for the retriable property is true. A command implementation can override this value, although the normal way to override without changing any code is to explicitly specify it in the CMDREG database table PROPERTIES column value. For example, if the CMDREG.PROPERTIES column value specifies retriable=false, then the command will not be retried when its database transaction rolls back.

  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.

• Ignore

  If the transactions that are failing are insignificant (end users can refresh their browsers and continue) we can ignore the error.