JR55126: DATABASE LOCK CONTENTION OCCURS ON THE LSW_USR_XREF TABLE WHEN YOU UPDATE GROUP MEMBERSHIPS

8.5.5.0-WS-BPM-IFJR55126
bpm.8570.cf2016.06.delta.repository.1of2
bpm.8570.cf2016.06.delta.repository.2of2
8.5.6.2-WS-BPM-IFJR55126

APAR status

• Closed as program error.

Error description

• A database lock contention occurs on the LSW_USR_XREF table when
  you update group memberships.
  
  The authorization concept of IBM Business Process Manager (BPM)
  uses user and group concepts, which require you to update
  corresponding group memberships based on business required
  changes. Updating the group memberships includes validating the
  users, but this step is not efficient enough to enable multiple
  group membership updates when the groups themselves are large.
  
  As a result, you might observe lock timeouts as well as
  deadlocks for the  following SQL statement:
  
  select user_name from lsw_usr_xref where user_id = ? for update
  
  This problem affects all database systems.
  

Local fix

Problem summary

• You see the following exception in the SystemOut.log file when
  the user validation leads to a lock timeout for an Oracle
  database:
  [1/18/16 11:39:28:054 AST] 00001c9a wle_security  E
  CWLLG1082E: Member <UserName> was not added to team <GroupName>
  and <GroupName>.
  Error: PreparedStatementCallback; bad SQL grammar [select
  user_name from lsw_usr_xref where user_id = ? for update];
  nested exception is java.sql.SQLSyntaxErrorException: ORA-02049:
  timeout: distributed transaction waiting for lock
  com.lombardisoftware.client.delegate.BusinessDelegateException:
  PreparedStatementCallback;
    bad SQL grammar [select user_name from lsw_usr_xref where
      user_id = ? for update];
    nested exception is java.sql.SQLSyntaxErrorException:
      ORA-02049: timeout: distributed transaction waiting for lock
  at com.lombardisoftware.client.delegate.
    BusinessDelegateException.asBusinessDelegateException
    (BusinessDelegateException.java:46)
  at com.lombardisoftware.client.delegate.
    InternalGroupsDelegateDefault.addUserToInternalGroup
    (InternalGroupsDelegateDefault.java:267)
  at com.lombardisoftware.server.ejb.security.
    ParticipantGroupsCore.updateMembershipSorted
    (ParticipantGroupsCore.java:1427)
  at com.lombardisoftware.server.ejb.security.
    ParticipantGroupsCore.updateMembership
    (ParticipantGroupsCore.java:1317)
  at com.lombardisoftware.server.ejb.security.
    ParticipantGroupsCore.resolveAddressForRetrievalService
    (ParticipantGroupsCore.java:573)
  at com.lombardisoftware.server.ejb.security.
    ParticipantGroupsCore.resolveAddressInternal
    (ParticipantGroupsCore.java:477)
  at com.lombardisoftware.server.ejb.security.
    ParticipantGroupsCore.resolveAddress
    (ParticipantGroupsCore.java:386)
  

Problem conclusion

• A fix is/will be available for IBM BPM V8.5.5.0 that improves
  how users are validated and prevents the high-access rate to the
  LSW_USR_XREF table.
  
  On Fix Central (http://www.ibm.com/support/fixcentral), search
  for JR55126:
  
  1. Select IBM Business Process Manager with your edition from
    the product selector, the installed version to the fix pack
    level, and your platform, and then click Continue.
  
  2. Select APAR or SPR, enter JR55126, and click Continue.
  
  When you download fix packages, ensure that you also download
  the readme file for each fix. Review each readme file for
  additional installation instructions and information about the
  fix.
  

Temporary fix

• Not applicable
  

Comments

APAR Information

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

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

Fix information

• Fixed component name

  BPM STANDARD

• Fixed component ID

  5725C9500

Applicable component levels