Fix Pack 7 introduced changes to DBClean to enhance the performance of the DELETE executions. One of the changes is executing the DELETE using fullselect DELETE instead of using a primary-key JOIN. Although this change performs better, it could lead to a LOCK ESCALATION and disrupt your site. We'll first review the difference between the two DELETE statements, how we get to a LOCK ESCALATION, and the options available to prevent or resolve it.

Primary-Key JOIN vs fullselect DELETE

In versions prior to Fix Pack 7, DBClean alters the statement from the CLEANCONF table and includes the FETCH FIRST n ROWS ONLY clause to support committing after an n number of records. The following is an example of DBClean's executed DELETE statement to purge guest users.

DELETE FROM member WHERE member_id
            IN (SELECT users_id FROM users T1
          WHERE registertype=''G'' AND (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ?
            AND NOT EXISTS (SELECT 1 FROM orders WHERE orders.member_id=T1.users_id AND status != ''Q'')
            AND (users_id > 0))
          FETCH FIRST 5000 ROWS ONLY

Fix Pack 7 changed the DELETE statements to use a fullselect DELETE to enhance performance. The following is an example of a fullselect DELETE.

              FETCH FIRST 5000 ROWS ONLY)

Fullselect and LOCK ESCALATION

Imagine DBClean attempting to clean up thousands or even million of records. Database locks can exist on row or tables. If the clean up process holds several row locks from the same table, lock escalation consolidates them into a higher lock level (such as a whole table lock). Consequently frees resources held by large number of fine-grained locks.

Although fullselect DELETE performs better it does requires additional locks which may lead to table locks. The problem rises when you have a huge table locked. All queries against this table may be locked out for a long time; or you can exhaust the LOCKLIST leading to a LOCK ESCALATION. Not only it will prevent other connections from using the table(s) but it may lock up the whole WebSphere Commerce site.

To prevent or resolve such scenario, DBClean has an optional parameter called sqlmode that allows site administrators choose which DELETE form to use.

Resolving LOCK ESCALATION

Pre-Fix Pack 7

The default or -sqlmode 0, uses the 'DELETE FROM <table> WHERE...' form. If you are running into a LOCK ESCALATION, you're probably running a customized version of the DELETE statement with fullselect form.

Fix Pack 7

Starting Fix Pack 7, the default sqlmode was modified to enhance performance. The default or -sqlmode 0, uses 'DELETE FROM (SELECT u.users_id FROM users u...' form (fullselect DELETE).

As mentioned earlier, fullselect DELETE performs better but depending on certain factors it may induce a LOCK ESCALATION. To address this type of scenario, apply APAR JR51847 to revert the default behavior of DBClean to use a primary-key JOIN to perform the DELETE. 

APAR JR51847 is applicable for Fix Pack 7. For FixPack 8 and Fix Pack 9 apply JR52306.fp8 and JR52306.fp9.

Post-Fix Pack 7 + APAR JR51847

With JR51847 (or JR52306 for Fix Pack 8 and 9), the default or -sqlmode 0, uses the 'DELETE FROM <table> WHERE...' form. The option -sqlmode 1 runs the out-of-the-box or your own customized version of the DELETE statement without any DBClean modification.

A new option, -sqlmode 2, is added to run the DELETE statement in fullselect form. This option is for those who are not affected by a LOCK ESCALATION and want the performance benefits of the fullselect DELETE.

If sqlmode is set to 1, the DELETE statement retrieved from the CLEANCONF table is run directly against the database without any DBClean modification. Consequently, the commit counter and the result set from the SQL is committed to the database until the max counter parameter is reached.

In summary, if you're running into LOCK ESCALATION when running DBClean, review the options based on the Fix Pack level. It is likely that the DELETE statement is using a fullselect form and needs to be modified to primary-key JOIN form.

Note: if you are planning to run DBClean using offline mode, you cannot specify this parameter. Default behavior will be used.

For more information, please refer to the following links: