IBM Support

JR46424: EMAIL NOTIFICATIONS ARE CAUSING LOCK TIMEOUTS ON TABLE LSW_BPD_INSTANCE

Subscribe

You can track all active APARs for this component.

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 number

    JR46424

  • Reported component name

    BPM ADVANCED

  • Reported component ID

    5725C9400

  • Reported release

    801

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-22

  • Closed date

    2013-09-30

  • Last modified date

    2014-02-11

  • 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



Document information

More support for: IBM Business Process Manager Advanced

Software version: 8.0.1

Reference #: JR46424

Modified date: 11 February 2014