Defect HC-2710

Long Running SQL on CMMETADATA table causing locks on production database

Software

Customer case Applies to Corrected in
CS0109914 V7 FEP 8 8.0.4.25

Observed behavior

When CSRs assist with orders, the production database can slow down due to database contention.

Expected behavior

CSR activity should not negatively affect performance.

Resolution

The order locking feature exists to prevent other CSRs (and customers) from making order modifications while the CSR is editing the order.

There are two approaches to locking the order in WebSphere Commerce: API and EDITOR_ID. The preset (default) is the API method. This method relies on inserting and deleting rows in the CMMETADATA table. Each time an order is put into edit by a CSR, a record is inserted into CMMETADATA and when they finish editing, the record is deleted. The EDITOR_ID method updates the ORDER table instead, eliminating the CMMETADATA table as a bottleneck. This fix enables the use of the EDITOR_ID method to eliminate the performance problem.

To change to the EDITOR_ID method, insert this line into the wc-server.xml file:

<OrderLockFeature enabled=\"true\" lockingSystem=\"EDITOR_ID\"/>

To revert to the default, remove the line or change it to:

<OrderLockFeature enabled=\"true\" lockingSystem=\"API\"/>