JR46424: EMAIL NOTIFICATIONS ARE CAUSING LOCK TIMEOUTS ON TABLE LSW_BPD_INSTANCE

Download Version 8.0.1 Fix Pack 2 for the IBM Business Process Manager products
Version 8.5.0 Fix Pack 1 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• If email notification is enabled in portal for a lot of
  users, this can cause lock timeouts on table LSW_BPD_INSTANCE
  because emails are sent in a loop for each subscribed user.
     If the LSW_BPD_INSTANCE table is on an Oracle DB Server, the
  following exception is logged:
  PreparedStatementCallback; bad SQL grammar [SELECT
  BPD_INSTANCE_ID FROM
  LSW_BPD_INSTANCE WHERE BPD_INSTANCE_ID = ? FOR UPDATE]; nested
  exception is java.sql.SQLSyntaxErrorException:
  ORA-02049: timeout:
  distributed transaction waiting for lock
     Here is the same Oracle error with the BPM message that is
  also logged
  [4/5/13 9:46:55:027 CDT] 00001ad5 wle_ucaexcept E   CWLLG0181E:
  An exception occurred during execution of task 37,447.  Error:
  PreparedStatementCallback; bad SQL grammar
  [SELECT BPD_INSTANCE_ID FROM
  LSW_BPD_INSTANCE WHERE BPD_INSTANCE_ID = ? FOR UPDATE]; nested
  exception is java.sql.SQLSyntaxErrorException: ORA-02049:
  timeout:
  distributed transaction waiting for lock
  com.lombardisoftware.core.TeamWorksException:
  PreparedStatementCallback; bad SQL grammar [SELECT
  BPD_INSTANCE_ID FROM
  LSW_BPD_INSTANCE WHERE BPD_INSTANCE_ID = ? FOR UPDATE]; nested
  exception is java.sql.SQLSyntaxErrorException: ORA-02049:
  timeout:
  distributed transaction waiting for lock
          at
  com.lombardisoftware.core.TeamWorksException.asTeamWorksExceptio
  n(TeamWorksException.java:136)
          at
  com.lombardisoftware.bpd.runtime.engine.quartz.AbstractBpdTask.e
  xecute(AbstractBpdTask.java:130)
          at
  com.lombardisoftware.bpd.runtime.engine.quartz.AbstractBpdTask.e
  xecute(AbstractBpdTask.java:59)
          at
  com.lombardisoftware.server.scheduler.Engine.execute(Engine.java
  :746)
          at
  com.lombardisoftware.server.scheduler.Engine.access$300(Engine.j
  ava:72)
          at
  com.lombardisoftware.server.scheduler.Engine$1.run(Engine.java:4
  68)
  .------------------------------------------
     This is a possible scenario for the problem
  1. you use the "Send email notification" in the portal (Open
  portal-> click on Administrator->User profile->"Send me an
  email when I have a new task assignment").
  2. If each email notification takes about 5 seconds and 13
  notifications are sent, this results in a total time of 65
  seconds during which time the lock is hold.  If the lock
  timeout is 60 seconds, then the lock timeout occurs.
  

Local fix

• This can be improved by sending one email notification to a
  list of users.
  

Problem summary

• If email notification is enabled and a Task is created, BPM
  sends an email to each user that is assigned to the task and is
  registered for email notification. If many users apply and the
  mail server is slow, this can take a significant amount of time.
  Since the sending of the email happens synchronously within a
  process navigation transaction, this can cause lock
  timeouts on table LSW_BPD_INSTANCE.
  

Problem conclusion

• The fix of the problem introduces 2 new properties in the
  <server> <email> configuration. The properties are
  <server>
      <email>
          <send-email-notifications-to-list>[true |
           false]</send-email-notifications-to-list>
          <send-email-notifications-async>[true |
           false]</send-email-notifications-async>
      <email>
  <server>
  
  They have the following effects:
  
  If <send-email-notifications-to-list> is set to false, BPM sends
  a separate email to each user that is assigned to the task
  and is registered for email notifications. This is identical to
  the behavior before the IFix.
  
  If <send-email-notifications-to-list> is set to true, the users
  that will receive a notification email are grouped by locale.
  For each locale, an email is sent to the list of all users with
  that locale. In order to keep privacy as previously, these
  emails are not sent to the "to:" destination but rather to
  "bcc:". This has the effect that no user can see the other
  users in the list.
  
  If <send-email-notifications-async> is set to false, sending of
  the emails happens synchronously within the navigation
  transaction of the BPD.  This is identical to the behavior
  before the IFix.
  
  If <send-email-notifications-async> is set to true, sending of
  the emails happens asynchronously in a separate thread.
  Depending on the overall load, the navigation transaction that
  created and sent the task may finish before an email
  notification has been sent.
  
  Please note, that this modification introduces new failure
  possibilities.  For example, if sending of an email to one user
  failed, this was reported in the past, an exception was thrown
  and the creation of the task failed.
  
  If <send-email-notifications-to-list> is set to true, BPM can no
  longer detect, which user caused the problem. It can only detect
  that the email could not be sent to the list.
  
  Similarly, if <send-email-notifications-async> is set to true, a
  task may be created even if sending of the emails may fail
  afterwards.
  

Temporary fix

• ...
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  JR46755

Fix information

• Fixed component name

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R801 PSY

     UP